You are the database administrator of your company. Your server named Server1 contains an instance of SQL Server 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 differential backup to be performed daily on SQL1 at 4:00 A.M. You also configure the transaction log to be backed up every hour. The database becomes corrupt on Wednesday at 3:20 P.M. You want to restore the database to the point of failure.
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 on Wednesday at 4:00 A.M.
D.
Restore the differential backup that was created on Wednesday at 4:00 A.M.
E.
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 backup is created at medium intervals, and the transaction log backup is created at more frequent intervals. The backup strategy that uses full database, differential database, and transaction log backups reduces the amount of time required 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 database backup. To restore a database to the point of failure, you should first perform the backup of the tail-log before restoring the most recent full and differential database backups. The tail-log backup is the backup of the active transaction log that was not included in the most recent transaction log backup. After creating the tail-log backup, you should restore the most recent full database backup. Next, you should restore the most recent differential database backup. Then, you should apply all transaction log backups taken after the last differential backup in sequential order. Finally, you should apply the tail-log backup. If the transaction log is also corrupt or lost, you will not be able to perform the tail-log backup and will not be able to restore the data to the point of failure.Objective: Maintaining a SQL Server Database Sub-Objective: Back up databases.
References: MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Backing Up and Restoring Databases in SQL Server > Understanding Recovery Performance in SQL Server > Reducing Recovery Time When Restoring a Database MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > Backing Up and Restoring Databases in SQL Server > Backup Under the Full Recovery Model MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > 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)