You are the database administrator for your company. You are managing the SQL Server database named Sql_prod1 located on your company’s server.
You adhere to the following backup strategy for the Sql_prod1 database:
? a transaction log backup at noon
? a snapshot of the database at 3:00 P.M.
? a complete backup of the database at 8:00 A.M.
? a differential backup of the database at noon and at 5:00 P.M.
Between 3:00 PM and 3:14 PM there are no changes made. At 3:15 P.M., a programmer issues the followingstatement:
DELETE FROM emp WHERE department_id = 40;
After this statement is issued and the changes have been committed, all the data for the employees in departmentID 40 is lost.
You want to recover the lost data using the least administrative effort. What should you do?
A.
Bulk copy the deleted rows from the snapshot created at 3:00 P.M., and merge the data into the database.
B.
Restore the full database backup that was taken at 8:00 A.M., and recover the database by applying thetransaction log files.
C.
Restore the full database backup followed by the differential backup taken at noon, and recover thedatabase by applying the transaction log files.
D.
Restore the database from the snapshot created at 3:00 P.M.
Explanation:
In this scenario, you should bulk copy the deleted rows from the snapshot that was created at 3:00 P.M., andmerge the data into the database. Snapshots can be used to undo a delete operation from the database withoutinvolving much overhead. The easiest method of recovering the deleted rows in this scenario is to identify thedeleted rows and subsequently transfer them from the snapshot to the source database. Snapshots can be usedto recover the database after an unwanted deletion, after a row has been erroneously updated, or after a tablehas been accidentally dropped. Snapshots can also be used to completely recover a database by reverting to apreviously created snapshot. You should not restore the database from the snapshot created at 3:00 P.M. To restore the database from apreviously created snapshot, you should delete all the other existing snapshots of the database. You cannotrevert to a snapshot of a database if there is more than one snapshot of the database. Reverting the completedatabase is not required in this scenario because you can easily undo the deletion by inserting the rows from thesnapshot created at 3:00 P.M. You should not restore the full backup of the database taken at 8:00 A.M. and recover the database by applyingthe transaction log files. You can recover the lost data by using this method, but it involves more administrativeeffort than using a snapshot. This option will only restore the database to the state that it was in at 12:00 P.M. Ifany changes to the emp table were made after 12:00 P.M., the changes would be lost. Therefore, this option isnot appropriate in this scenario. A database can be recovered by using this technique when one or more data filesor filegroups have been damaged. You should not restore the full backup of the database followed by the differential backup taken at noon and thenrecover the database by applying the transaction log files. You can recover the lost data by this method, but itinvolves more administrative effort than using a snapshot. This option will only restore the database to the statethat it was in at 12:00 P.M. If any changes to the emp table were made after 12:00 P.M., the changes would belost. Therefore, this option is not appropriate in this scenario. A database can be recovered by using thistechnique when one or more data files or filegroups have been damaged.
Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Manage database snapshots.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots > Typical Uses ofDatabase SnapshotsTechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots > Reverting to aDatabase Snapshot