Backing Up and Restoring Databases

Knopper

I am currently studying for 70-431. As I am reading the topic about backing up and restoring databases and logs, I am trying to think of an example so that I can better understand the topic.

Let's say I make a full backup of the MYDB database at 00:00. Differential backups run at 4:00 and 8:00am. Database log backups are created every 5 minutes. At 8:18am the hard disk holding the database crashes catastrophically. I need to restore the data to its latest possible state.

Given this information, I suppose I can restore the database to the state it was at 8:00am with no problem by just using the midnight full backup and applying the differential backup at 8:00. Now, to restore the data to the state at 8:15, I need to roll forward the transaction logs taken at 8.05, 8.10 and 8.15, is that correct? Finally, if best practices have been followed designing the server and the transaction log is on a different hard disk, I should be able to roll the log forward to the state the DB was when the server crashed.

Is this correct or I am not grasping the concept right? Thanks in advance for helping me in this exam.

bertieb

Pretty much yes! You'd generally want to back up the tail of the transaction log first before commencing the restore procedure though, and make sure this is the last backup to be restored.

Knopper

Thanks, I am more confident now in the backup concept of SQL Server.