Time given lies in all three backups, full, diff ,log:

  • full - 12 midnight
  • diff - 4 am ( every 4 hours)
  • log - 15 minutes interval


  1. time restore is 5:17
  2. time restore is 5:14

How should I answer that?


I'd answer that from the backupmediafamily and backupset tables. Build the restore T-SQL dynamically from that using STOPAT for the time you want to restore.


Use PowerShell + dbatools pipe get-backuphistory to restore-database using the -restoretime parameter.


Your question is bit unclear; you have not specified whether there was a failure and at which time the failure happened.

In general, restore steps are:

  1. Restore the latest full backup with no recovery
  2. Restore the latest diff backup with no recovery
  3. Restore transaction log backups with no recovery, except the last transaction log backup which falls withing the recovery time and specify the stop at time.

i.e. in above scenario:

for 5:17

  1. restore full backup that was taken at midnight with no recovery

  2. the diff backup taken at 4am with norecovery

  3. restore all t-log backups until 5.15 with no recovery

  4. restore t-log backup taken at 5.30 with stop at 5.17 and with recovery

For 5:14, do step 1, do step 2, step 3 all t-log backups till 5am with no recovery. Then restore t-log backup taken at 5.15 with stop at 5.14 with recovery.

  • I see no difference there between 5:17 and 5:14 , only to be stopped early then restart time. – Stas Rozen Mar 1 '17 at 6:41
  • @Stas Rozen you have specified that transaction log backups happen every 15 mins, hence 5.14 falls inside the t-log backup which would be taken at 5.15, where as 5.17 falls inside the t-log backup that would be taken at 5.30 , i.e. all changes that happened between 5.15 and 5.30 only would be available in the 5.30 T-log backup – entDba Mar 1 '17 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.