You are a database administrator managing all the SQL Server 2008 databases of your company. All thecustomer-related data is stored in the Prod database that is operating in the Full Recovery model. You adhere tothe following backup strategy for the Prod database:
At 4:00 P.M. on Wednesday, the hard disk on which your database files are stored fails. You lose all the datastored on the disk and are required to recover the database from the existing database backups.
To most efficiently recover the database, which action should you take?
A.
Restore the full backup taken on Sunday, the differential backup taken on Tuesday, and the transaction logbackups taken at 10:00 A.M. and 2:00 P.M. on Wednesday.
B.
Restore the full backup taken on Sunday, the differential backup taken on Wednesday, and the transactionlog backup taken at 2:00 P.M. on Wednesday.
C.
Restore the full backup taken on Sunday, the differential backups taken on Monday and Tuesday, thetransaction log backups taken on Tuesday, and the transaction log backups taken on Wednesday at 10:00A.M. and 2:00 P.M.
D.
Restore the full backup taken on Sunday, the differential backup taken on Monday, and all the transactionlog backups taken on Tuesday and Wednesday.
Explanation:
In this scenario, you should recover the database up to 2:00 P.M. on Wednesday by restoring the full backuptaken on Sunday, the differential backup taken on Tuesday, and the transaction log backups taken at 10:00 A.M.and 2:00 P.M. on Wednesday. To recover a database from a set of full and differential backups, you shouldrestore the most recent full backup and the most recent differential backup created since the last full backup. Thisdifferential backup will contain all the data that has been modified in the database since the last full backup. Youcan also use the transaction log backups since the most recent differential backup. In this scenario, the mostrecent full backup was taken on Sunday, and the most recent differential backup was taken on Tuesday. Afterrestoring these two backups, you can also restore the two transaction log backups that were taken after thedifferential backup. Therefore, after recovery, the database will contain all the data up to 2:00 P.M. onWednesday. You should not restore the full backup taken on Sunday, the differential backup taken on Wednesday, and thetransaction log backups taken at 2:00 P.M. on Wednesday. There is no differential backup of the database onWednesday because the differential backups are taken on weekdays at 8:00 P.M., and the database wascorrupted at 4:00 P.M. Therefore, you can only use the differential backup created on Tuesday. You should not restore the full backup taken on Sunday, the differential backups taken on Monday and Tuesday,the transaction log backups taken on Tuesday, and the transaction log backups taken on Wednesday at 10:00A.M. and 2:00 P.M. To recover a database by restoring the backups, you should only restore the most recentdifferential backup since the most recent full backup. You should not recover the differential backup created onMonday. You should not restore the full backup taken on Sunday, the differential backup taken on Monday, and all thetransaction log backups created on Tuesday and Wednesday. To recover a database from backups, you shouldrestore the most recent backup. This enables you to recover the database in less time by applying only thosetransactions logs that are generated after the last differential backup. If you restore the differential backup takenon Monday, you will be required to apply all the transaction logs generated on Tuesday and on Wednesday. Thisprocess will require additional time and is not the most efficient method to recover the database. The backup and restore process will vary based on the design of the database layout as well as the backuprequirements. For example, if you have a database that resides on drive G, a transaction log that resides on driveH, and an archive that resides on drive I, you would design your backup and restore process based on therequirements. In such a situation, it would be best to implement a separate backup strategy for each drive. Youcould use a full backup/differential backup strategy to backup the database and use transaction log backups tobackup the transaction log. A separate backup strategy could be implemented for the archive. If the databaseneeds to be recovered, you would use a combination of the full backup, differential backups, and transaction logbackups. However if the drive on which the archive or transaction log resides is the only drive to fail, the databasewould still be operational. You would simply need to replace the failed drive.
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 > BackingUp and Restoring Databases in SQL Server > Understanding Recovery Performance in SQL Server > ReducingRecovery Time 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)