Administering a SQL Database Infrastructure — Question 147

You have a server that has SQL Server 2014 installed.
The server contains 100 user databases.
You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
✑ Perform a transaction log backup every hour.
✑ Perform a full backup of each database every week.
✑ Perform a differential backup of each database every day.
✑ Ensure that new user databases are added automatically to the backup solution.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

Answer options

• A. A maintenance plan
• B. SQL Server Agent jobs
• C. Policy-Based Management
• D. A Data Definition Language (DDL) trigger

Correct answer: A

Explanation

A maintenance plan is the most suitable choice as it allows for configuring scheduled tasks like transaction log, full, and differential backups while automatically including new databases. SQL Server Agent jobs would require manual setup for each database, Policy-Based Management does not directly handle backups, and a DDL trigger is not intended for managing backup schedules.