understand Full,Differential,TransactionLog Backups

[saqlainkhan]

I am in a sticky position. I have a backup plan as following:
1)FULL BACKUP - WEEKLY -->FRIDAY
2)DIFFERENTIAL - EVERY 6 HRS
3)TRANSACTIONLOG - EVERY 15 MIN

Assuming my db crashes on Wednesday, what will be the procedure to restore my db just before it crahsed. As i see it my minimum loss would be a minute and max of 14.59 minutes.
Thanks.

[Bru Medishetty]

First, I would not recommend a Differential backup every 6 hours, I would recommend it to be daily (24 hours).

So with that slight change, the recovery process would be something like this.

Since you have not mentioned at what time the crash occured (assuming if the crash occurs at 3:25 PM) or there was not a crash but a user made mistake (a Delete / Drop / Truncate). around 3:25.

Then the procedure would be something like this.

Restore the latest Full backup WITH NORECOVERY Option.

Restore the Most recent Differential backup (in this case it would be Tuesday night's 11 PM (again assuming you have it scheduled at 11 PM)  Diff backup) WITH NORECOVERY Option.

Restore all the Transaction Log backups which were taken right after the Recent most differential backup (from Tuesday night 11:15 ) till 3:15. All these backups need to be used WITH NORECOVERy Option and then finally (the last Transaction Log Backup) needs to be restored WITH RECOVERY Option and you need to mention STOP AT 15:25.

I hope this gives a good picture..

[saqlainkhan]

Thanks... makes perfect sense with additional info regarding DifferentialBackup.
Thanks!