Administering a SQL Database Infrastructure — Question 138

You administer a Microsoft SQL Server 2016 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. 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? Choose all that apply.

Answer options

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

Correct answer: B, C, D, E

Explanation

The correct answers are B, C, D, and E because using the FULL recovery model allows for point-in-time recovery and ensures minimal data loss with frequent LOG backups (every 5 minutes). Creating a DIFFERENTIAL backup every 4 hours and a FULL backup every 24 hours provides a comprehensive backup strategy. Option A is incorrect as it does not meet the transaction loss requirement, and options F does not provide sufficient frequency for backups.