You are the database administrator for your company. You manage the production database named Prod . The database is configured to use the Simple Recovery model. You perform a full backup of the database at 1:00 A.M.and 1:00 P.M. daily. A differential backup is performed every two hours beginning at midnight.
A user in the database drops the table at 11:30 A.M.
Which restoration method will you use to recover the dropped table to the most recent point in time?
A.
Restore the database from the most recent full backup only.
B.
Restore the database from the most recent full backup, and apply all the differential backups.
C.
Restore the database from the most recent full backup, and apply the most recent differential backup sincethe last full database backup.
D.
Recover the table from the most recent full backup, apply the most recent differential backup, and apply themost recent transaction log backup.
Explanation:
You should restore the database from the most recent full backup and apply the latest differential backup sincethe last full database backup. To recover a table from a series of full and differential database backups, you should restore the database from the most recent full database backup and apply the latest differential backupthat was taken after the most recent full database backup. In this scenario, the database is configured to use thesimple recovery model. Therefore, you cannot use the transaction logs for recovery. In a database that uses thesimple recovery model, backups of the transaction logs are not maintained. You should not restore the database from the most recent full backup. Restoring the most recent full databasebackup will only restore the table to the point when the full database backup was taken. To restore the table to themost recent point in time, you should apply the latest differential backup performed after the most recent fulldatabase backup. You should not restore the database from the most recent full backup and apply all the differential backups. Afterrestoring the table from the most recent full database backup, you are only required to restore the latestdifferential backup since the most recent full database backup. You are not required to restore all the differentialdatabase backups. You should not recover the table from the most recent full backup, followed by the most recent differentialbackups, and finally by the most recent transaction log backup. In this scenario, the database is using the simplerecovery model, and a backup of transaction logs is not maintained in the simple recovery model. 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.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 >Administration: How-to Topics > Backing Up and Restoring How-to Topics > How to: Create a DifferentialDatabase Backup (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > BackingUp and Restoring Database in SQL Server > Creating Full and Differential Backups of a SQL Server database >Using Differential Backups > Differential Database Backups