SQL Database Maintenance Plan and Recommended Backup Procedures for Board Child Care Servers

Oleh: Eky Bangun Mukti (114.07.1179)

This document provides detailed information on:
1. how to set up a SQL maintenance plan on your child care server
2. how to ensure the child care databases are being backed up onto the
server daily.

You must set up a SQL database maintenance plan on your server. This plan
does the following:
1. reorganizes data and indexes
2. checks database integrity
3. creates backups of databases.

The database backup plan within SQL maintenance makes a copy of the
databases directly onto the server. You must also save the child care
databases onto a tape each day and store them off site. Each Board must
have a process for tape backups and storage. If a Board experiences data loss
because of a server crash, a virus that affects the server, or other major issue
you can restore the database from the latest backup file.

Following are detailed steps to setting up the SQL maintenance plan and backing
up critical data onto tapes. Launch SQL Server Enterprise Manager and expand until your server appears; expand management.



Right click in the Database Maintenance Plan window to start the new maintenance Plan Wizard.



It is recommended that you choose All Databases, but you must choose at least CCS, master, model, and msdb. Click Next.



The first screen defines the options for Data Optimization. The default schedule is once a week. Change this to daily. Do this by clicking the Change button.



Do this process after hours, just prior to backing up your tape. The time it takes to complete maintenance depends on the size of your database.



The next screen defines the options for the Database Integrity Check. At a minimum, accept the default values. You may choose the repair option and then index options, and you may change the schedule.



This screen defines the options for the Database Backup Plan. Check the Backup option and the Verify option. Set the location to Disk. Change the Schedule to backup every day before the system tape backup starts.



This screen provides the options for the disk backup location. You may use the default backup directory or choose your location. If you check the ”Create a subdirectory option,” the system backs up each database into separate folders. Regardless of the backup location, be sure you include the backup location in the system tape backup. Select the ”Remove files older than” option, and choose how long to retain previous backup files. Usually, removing files older than three days is adequate.



Select the ”Back up of the transaction” option, and schedule it to run after the database backup. Select the same options as you did for the database backup.



If you choose, you can have the maintenance plan generate reports. These options are site dependent.



This screen defines the options for the Maintenance Plan History. The default options are usually adequate. You can view the Maintenance Plan History by right-clicking the Maintenance Plan and selecting Maintenance Plan History.



Name your plan, click on finish, and the following appears:



Execution of the Maintenance Plan requires that the SQL Server Agent be active. The system displays an error message if the SQL Server Agent is not active. Click OK, then Finish. Activate the SQL Server Agent the same way you activated the SQL Server. The following screen describes this process. To start this service, click on the SQL Service Manager located in your system\ tray by the clock.



Select SQL Server Agent and start it. You will probably want to check the Auto- start box also. After this, your maintenance plan should run as scheduled.
Authors: TWC Child Care (12/03/03)