Administering Microsoft SQL Server 2012/2014 Databases — Question 29

You administer a Microsoft SQL Server instance.
The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. The database is configured to use the FULL recovery model.
You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
✑ No more than 5 minutes worth of transactions are lost.
✑ Data can be recovered by using the minimum amount of administrative effort.
What should you do? Each correct answer presents part of the solution.

Answer options

• A. Create a LOG backup every 5 minutes.
• B. Configure the database to use the SIMPLE recovery model.
• C. Create a DIFFERENTIAL database backup every 4 hours.
• D. Create a DIFFERENTIAL database backup every 24 hours.
• E. Create a FULL database backup every 24 hours.

Correct answer: A, E

Explanation

Answer A is correct because creating a LOG backup every 5 minutes ensures that no more than 5 minutes of transactions are lost, which meets one requirement. Answer E is also correct as a FULL backup every 24 hours is necessary to allow for a complete restoration of the database. Options B, C, and D do not fulfill the requirements, as changing to the SIMPLE recovery model would result in potential data loss, and the differential backups alone would not provide the needed recovery point objectives.