You are the database administrator of your company. Your server named Server1 contains an instance of SQLServer 2008 named SQL1.
Server1 contains two hard disks named Disk1 and Disk2 . A database named Corpdb contains sensitive information and is stored on Disk1.
The transaction log for Corpdb is stored on Disk2 .Your company policy states that data loss must never exceed one hour’s worth of data.
You configure a full backup to be performed on SQL1 every Sunday at 1:00 A.M.
You configure a differentialbackup to be performed daily on SQL1 at 4:00 A.M.
You also configure the transaction log to be backed up everyhour. The database becomes corrupt on Wednesday at 3:20 P.M.
You want to restore the database to the point offailure. What should you do first?
A.
Perform a tail-log backup.
B.
Apply the tail-log backup.
C.
Apply all transaction log backups in the order they were created after the differential backup created onWednesday at 4:00 A.M.
Restore the differential backup that was created on Wednesday at 4:00 A.M.
D.
Restore the full backup that was created on Sunday.
Explanation:
You should perform a tail-log backup. In a typical backup strategy that uses full database, differential database,and transaction log backups, the full database backup is created at less frequent intervals, the differential backupis created at medium intervals, and the transaction log backup is created at more frequent intervals. The backupstrategy that uses full database, differential database, and transaction log backups reduces the amount of timerequired to restore a database to any point in time after the database backup was created. This strategy also requires less disk space because the differential database backups take much less space than a full databasebackup. To restore a database to the point of failure, you should first perform the backup of the tail-log before restoring themost recent full and differential database backups. The tail-log backup is the backup of the active transaction logthat was not included in the most recent transaction log backup. After creating the tail-log backup, you shouldrestore the most recent full database backup. Next, you should restore the most recent differential databasebackup. Then, you should apply all transaction log backups taken after the last differential backup in sequentialorder. Finally, you should apply the tail-log backup. If the transaction log is also corrupt or lost, you will not be ableto perform the tail-log backup and will not be able to restore the data to the point of failure.
Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Back up databases.References:
MSDN > MSDN Library > Servers and Enterprise Development > 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 MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > BackingUp and Restoring Databases in SQL Server > Backup Under the Full Recovery Model MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > How To > Transact-SQL > Administering SQL Server >Backing Up and Restoring Databases > How to restore to a point in time (Transact-SQL)