Setting Up a Database Maintenance Plan

Setting up a database maintenance plan forces a database server to perform database backups at regular intervals. The maintenance plan consists of full database and transaction log backups performed at regular intervals and the procedure of updating statistics for query optimization.

IMPORTANT! Backing up the computer hard drives will NOT back up Microsoft SQL Server databases correctly. In order to preserve your company’s information, the databases must be backed up using Microsoft SQL Server procedures.

To set up a database maintenance plan, use the Automatic Database Backup Plan group of settings located of the Management tab of the Database Monitoring and Maintenance snap-in.

Note: Alternatively, you may choose to set up the maintenance plan for your DSM database using the Database Maintenance Plan Wizard form the Microsoft SQL Server Enterprise Manager (available only for the Standard/Enterprise editions of MSSQL).

To enable/disable the maintenance plan:

1. Click Change Settings. The Change Automatic Database Backup Plan window will open.
2. Clear the Enable check box.

Backup files and transaction logs will be saved in a subfolder of the folder specified in the Backup Directory field of the window. The subfolder has the same name as the database. The backup file names are self explanatory and contain the database name, for example: “ProductionDatabase_db_200311281135.BAK” - the full backup file for the “ProductionDatabase” database and “ProductionDatabase_tlog_200311281200.TRN” - a transaction log file for the “ProductionDatabase” database. The report files may be found in the “reports_txt” (text format) and “reports_html” (HTML format) subfolders of the main backup folder. You may also specify a policy for removing old full backup, transaction log, and report files in the corresponding fields of the window.

Important! To prevent loss of data, you should always save database backup files to a hard disk other than the one used to store data related files. This strategy provides a possibility to restore databases in case of a hard disk failure.

Important! To ensure proper operation of a backup plan, you should routinely inspect backup folders and test your backup by performing occasional restores.

The operations performed by the maintenance plan are implemented as SQL Server Agent jobs and may be managed using the SQL Server Agent Jobs listing of the SQL Jobs tab. By default, the full backup is made every day, at midnight, and transaction logs are saved every four hours. To modify the default backup plan, specify a different schedule for each of the plan components as shown in the section below.

Important! The database maintenance procedures utilize the SQL Server Agent provided with all editions of Microsoft SQL Server (Standard, Enterprise, or MSDE). Before preparing a plan, make sure that the SQL Server Agent service is installed, and running. It is recommended that the SQL Server Agent be configured to run automatically on startup.

Important! To ensure proper operation of the currently enabled backup plan, check the Enabled status of the SQL Server Agent jobs corresponding to the components of the plan. For more information on SQL Server Agent jobs, refer to the next section.