You are a database administrator for your company. You implement the following backup strategy for your Prod_details database:
? Take a full backup at 10:00 P.M. every day.
? Take a differential level 1 backup at 6:00 A.M. every day.
? Take a transaction log backup every four hours starting at 12 midnight.
? Take a database snapshot at 2:00 P.M.
A user in your database inadvertently deletes some important data from the Tbl_product table at 8:30 A.M. You are required to recover the maximum possible data in the table.
Which activities should you perform to recover the database in the least amount of time?
A.
Restore the last full backup and the last differential backup of the database, apply the transaction log backuptaken at 8:00 A.M., and perform the recovery.
B.
Restore the differential backup of the database, apply the transaction log backup taken at 8:00 A.M., andperform the recovery.
C.
Restore the full database backup, apply all the transaction log backups taken since 8:00 P.M. on theprevious day, and perform the recovery.
D.
Restore the data in the Tbl_product table by performing a bulk copy from the database snapshot created at 2:00 P.M.
Explanation:
You should restore the last full backup and the last differential backup of the database, apply the transaction logbackup taken at 8:00 A.M., and perform the recovery. The full database backup taken at 10:00 P.M. on theprevious day contains all the data in the database until 10:00 P.M. The differential backup of the database takenat 6:00 A.M. contains the backup of the data that has changed since the full backup at 10:00 P.M. on the previousday. Therefore, when you restore the full backup and differential backup of the database, the data until 6:00 A.M.can be recovered. Additionally, you have taken a backup of the transaction log at 8:00 A.M., which contains anychanges made to the database after the differential database backup was taken at 6:00 A.M. Therefore, you canrecover the data up to 8:00 A.M. by restoring the full backup taken at 10:00 P.M., differential backup taken at 6:00A.M., and then applying the transaction log backup taken at 8:00 A.M. This method will require the shortestamount of time than any of the other options. You should not restore the differential backup of the database, apply the transaction log backup taken at 8:00A.M., and perform the recovery. To recover a database from a failure, you must first restore the full backup andthen restore the differential backup of the database. Restoring the differential backup before the full backup willgenerate an error in the restore and recovery process. You should not restore the full database backup, apply all the transaction log backups since 8:00 P.M. on theearlier day, and perform the recovery. This would take a longer time to recover the database. You should restorethe differential backup taken at 6:00 A.M. after restoring the full database backup. Then, you should apply thetransaction logs. This process will take less time because you will not be required to apply all the changes sincethe full backup. You should not restore the data in the Tbl_product table by performing a bulk copy from the database snapshotcreated at 2:00 P.M. A snapshot of the database taken at 2:00 P.M. will not contain the changes made after 2:00P.M. In this scenario, you should recover the maximum possible data in the table. Therefore, you should not usethe snapshot to recover the database. When restoring a database, you can restore to a particular point in time. You would execute the RESTOREDATABASE statement with the NO RECOVERY clause for the database backups. When you restore thetransaction log backups, you execute the RESTORE LOG statement. The RESTORE LOG statement includesthe RECOVERY and STOPAT
clauses, which ensure that the database is restored to full functionality and isrestored to a certain point in time, respectively. Administrators should also consider performing a tail-log backup to back up log records. This prevents work lossand keeps the log chain intact.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Restore databases.References:
TechNet>TechNet Library>Server Products and Technologies>SQL Server>SQL Server 2008>ProductDocumentation>SQL Server 2008 Books Online>Database Engine>Operations>Administration>Backing Up andRestoring Database in SQL Server>Understanding Recovery Performance in SQL Server>Reducing RecoveryTime When Restoring a Database TechNet>TechNet Library>Server Products and Technologies>SQL Server>SQL Server 2008>ProductDocumentation>SQL Server 2008 Books Online>Database Engine>Operations>Administration>Administration:How-to Topics>Backing Up and Restoring How-to Topics>How to: Restore to a Point in Time (Transact-SQL)