Develop a Database Maintenance Plan, Exercise

This exercise is worth a total of 10 points. To receive full credit, review the scenario below and write your maintenance plan in the text box. Once you have completed your answer, press the Submit button.

As the database administrator for the XYZ Rental Corporation, you need to design a maintenance routine for the SalesDB database, running on SQL Server 2025.

1. A full database backup takes four hours, and you only have a maintenance window to run one once a week.
2. A transaction log backup takes 20 minutes.
3. You expect the data distribution in several tables to change significantly over a three week period.
4. Automatic statistics updating (AUTO_UPDATE_STATISTICS) is turned on for every table.
5. You only want to take a backup if the database passes an integrity check first; a backup should not run against a database that just failed that check.
6. Backups are written to disk.
7. You want a notification emailed to JoeSmith whenever a step in the routine fails.
8. JoeSmith also wants to be able to review the routine's run history remotely, from outside the office network.
9. Connections to the instance use Windows Authentication (integrated security); no SQL Server login or password should appear anywhere in the routine.
10. You need to be able to recover the database to a specific point in time, up to the minute, not just back to the most recent full backup.

Design the maintenance routine described above. Your answer should cover:

• Which recovery model the database needs to be in, and why, given requirement 10.
• The order integrity checking and backup should run in, and how a failed check actually prevents the backup from running rather than merely being logged alongside it.
• How often each kind of backup needs to run to satisfy requirements 1 and 2, and which backup type each schedule applies to.
• Whether anything needs to be done about statistics maintenance, given that autostats is already on, in light of requirement 3.
• How failure notifications reach JoeSmith, and how the routine's history stays reviewable to him remotely.

A solution written as SQL Server Agent job steps with T-SQL commands is expected. If you are already familiar with sqlmaint.exe from this module's earlier lesson on automating maintenance, you may include the equivalent legacy syntax as well, but note in your answer that it is deprecated and not what you would deploy in new work.

Submitting your exercise Enter your answer into the text box below. Click the Submit button to submit your answer.