SQL Server Maintenance Guide

Owned by Leslie
Last updated: Feb 28, 2011 by John PaliwodaVersion comment
5 min read

Overview

There are two main aspects to SQL Server Maintenance:

  1. A disaster recovery plan.
  2. General maintenance of the database.

Disaster Recover Plan

Typically the disaster recover plan for a StudyTRAX database consists of the following elements:

  1. Periodic database backups (see Maintenance Plan Creation below).
  2. Storage of backups at a secure off-site location.
  3. Test restoring the database from a backup.

General Maintenance of the Database.

General maintenance includes activities such as Rebuilding Indexes and Updating Statistics. These activities can be scheduled through a SQL Server maintenance plan (see Maintenance Plan Creation below).

Database backup strategies

Since a StudyTRAX database is typically not very large by SQL Server standards, the following two strategies are viable options:

  1. Nightly Full Backups: This is also the simplest option. A full backup of the database is scheduled to be run every night. This allows a database to be restored to its state at the time of the backup. With this option, it is important to set the Recover Model for the database to Simple or else the transaction logs will grow indefinitely.
  2. Weekly Full Backups and Daily Transaction Log Backups: Typically a full backup is scheduled to run on each weekend and transaction log backups are scheduled to run daily. This allows a database to be restored to its state at the time of the last transaction log backup or to any prior specific time. This option also requires less storage space for backups since a transaction log backup is significantly smaller than a full database backup.

Maintenance Plan Creation

Nightly Full Backups:

  1. Open SQL Server Management Studio.
  2. Set the database Recovery Model to Simple.
    1. Right-click on the StudyTRAX database and select Properties from the menu.
  3. Start the Maintenance Plan Wizard.
  4. Right-click on Management -> Maintenance Plans and select Maintenance Plan Wizard from the menu.
  5. In the Select Plan Properties Window:
    1. Supply a name.
    2. Select "Single Schedule for the entire plan or no schedule."
    3. Click on the Change button to select a schedule for the plan to run.
  6. On the Select Maintenance Tasks window, select the options as in the following image:
  7. Click Next through the Select Maintenance Task Order page.
  8. Select the StudyTRAX database on the Define Database Check Integrity Task page.
  9. Select the StudyTRAX database on the Define Rebuild Index Task page. The other options can be left at their defaults.
  10. Select the StudyTRAX database on the Define Update Statistics Task page. The other options can be left at their defaults.
  11. Click Next through the Define History Cleanup Task page.
  12. Select the StudyTRAX database on the Define Back Up Database (Full) Task page. Specify where the database backup files will be created. The remaining options can be left at their defaults.
  13. Set options as desired on the Select Report Options page.
  14. Click Finish on the Complete the Wizard page.

Weekly Full Backups and Daily Transaction Log Backups:

Note, this option requires the creation of two maintenance plans: One for the weekly database backup and general maintenance and another for the nightly backups.

Weekly Database Backup and Maintenance Tasks
  1. Manually create the initial database backup.
    1. Open SQL Server Management Studio.
    2. Right-click on the StudyTRAX database and select Task -> Backup from the context menu.
    3. Ensure the Recovery Model is set to Full (the default).
    4. Ensure the Backup type is Full (the default).
    5. Select the location for the backup file.
  2. Start the Maintenance Plan Wizard.
    1. Right-click on Management -> Maintenance Plans and select Maintenance Plan Wizard from the context menu
  3. In the Select Plan Properties Window:
    1. Supply a name.
    2. Select "Single Schedule for the entire plan or no schedule."
    3. Click on the Change button to select a schedule for the plan to run.
  4. On the Select Maintenance Tasks window, select the options as in the following image:
  5. Click Next on the Select Maintenance Task Order page.
  6. Select the StudyTRAX database on the Define Database Check Integrity Task page.
  7. Select the StudyTRAX database on the Define Rebuild Index Task page. The other options can be left at their defaults.
  8. Select the StudyTRAX database on the Define Update Statistics Task page. The other options can be left at their defaults.
  9. Click Next through the Define History Cleanup Task page.
  10. Select the StudyTRAX database on the Define Back Up Database (Full) Task page. Specify where the database backup files will be created. The remaining options can be left at their defaults.
  11. Set options as desired on the Select Report Options page.
  12. Click Finish on the Complete the Wizard page.
Daily Transaction Log Backup
  1. Start the Maintenance Plan Wizard.
    1. Right-click on Management -> Maintenance Plans and select Maintenance Plan Wizard from the context menu
  2. In the Select Plan Properties Window:
    1. Supply a name.
    2. Select "Single Schedule for the entire plan or no schedule."
    3. Click on the Change button to select a schedule for the plan to run. It should be run every day except when the full database backups are run. One option to accomplish this schedule is in the image below.
  3. On the Select Maintenance Task page, select Back Up Database (Transaction Log)
  4. Click Next on the Select Maintenance Task Order page.
  5. Set options as desired on the Select Report Options page.
  6. Click Finish on the Complete the Wizard page