You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales and product data. Several data capturers enter data into the CK_Sales database everyday. The Full Recovery Model is implemented for the CK_Sales database. A Full backup of the CK_Sales database is performed every Saturday at
12:00 P.M., a differential backup is performed every week night at 12:00 P.M., and a backup of the transaction log is performed every two hours during the working day.
On Thursday morning you discover that Certkiller -DB01 suffered a hard disk failure sometime before the previous day’s differential backup was performed. You replace the failed hard disk and restore the operating system and application on Certkiller -DB01. You now need to restore the CK_Sales database as quickly as possible.
What should you do?
A.
Restore the last Full backup.
Restore the last differential backup.
Restore all the transaction logs in order.
B.
Restore the last Full backup.
Restore all the transaction logs in order.
C.
Restore the last Full backup.
Restore the differential backup since the last Full backup.
Restore all the transaction logs since the last differential backup.
D.
Restore the last Full backup.
Restore the last differential backup.
Restore all the transaction logs since the last differential backup.
Explanation:
The Full Recovery Model is implemented for the CK_Sales database and you’ve performed a Full backup on Saturdays, daily differential backups and transaction log backups every two hours. In the Full Recovery Model, transactions in the transaction log are not cleared until they are backed up. Therefore you need to restore the last Full backup, restore the last differential backup, and restore all the transaction logs since the last differential backup.
Incorrect Answers:
A: You do not need to restore all the transaction log backups, only the transaction log backups since the last differential backup.
B: You could restore all the transaction log backups since the last Full backup but it would be quicker to restore the last differential backup performed since the last Full backup and then only the transaction log backups since the last differential backup.
C: A differential backup will backup all data that has changed since the last Full backup. Therefore you only need to restore that last differential backup.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: database backups [SQL Server]