Caution: Use caution when restoring databases. Do not attempt to restore a database over an existing database unless you are sure you want to replace the data in the existing database with the data in the backup files you will restore from.

Maintenance Plan Backups for MS SQL Server 2000 MSDE

In order to facilitate database backups, an automated database backup tool is available in the Management tab of the Database Monitoring and Maintenance Snap-in of the MetaCommunications Management Console (MetaConsole).

This backup tool will allow you to perform on-demand database backups, and implement an automated database backup policy that creates daily full database backups and transaction log backups every four hours for your MS SQL Server 2000 MSDE database(s).

The following procedures are extracted from the MetaConsole guide. For complete information on the MetaConsole, please refer to the MetaConsole Guide.

Backing Up a database

At any chosen time, the Management tab of the Database Monitoring and Maintenance Plug-in allows you to manually perform a full backup of the database.

Note: This tool is recommended for use with Microsoft SQL Server 2000 MSDE Edition only.

1. Click Backup Database. The Backup Database dialog will appear.

1. Specify a destination and file for the database backup. According to existing naming conventions, you normally will use the “.BAK” extension for the backup file. Click OK when finished. A Backup Progress window will appear.

Note: The backup location must be accessible by SQL Server.

1. Wait for the database backup to complete. When finished, a completion window will appear.

1. Click OK.

Restoring a database

At any chosen time, the Management tab of the Database Monitoring and Maintenance plug-in allows you to manually restore a database from a database backup and transaction logs.

Note: This tool is recommended for use with Microsoft SQL Server 2000 MSDE Edition only.

To prevent overwriting a database unintentionally, the restore operation automatically performs a safety check. The restore operation fails if:

The database being restored already exists on the server and,

The name of the database being restored is not the same as the name of the database recorded in the backup set.

1. Click Restore Database. The Restore Database window will appear.

1. Browse to and select a full backup file and (if available) transaction log to restore from. If there are more than one transaction log files saved after the full backup, all of them will automatically be restored one after another until the specified transaction log backup is applied.
2. Optional: To restore a database from a different backup file (for example, when you need to populate an empty database with data), use the “Force restore over existing database” checkbox.
3. Click OK. A restore progress window will appear, indicating that the database restoration is in process.

Setting an automatic database backup plan

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

Use the Automatic Database Backup Plan group of the Management tab in the Database Monitoring and Maintenance Plug-in to set up an automatic database backup plan.

Note: This tool is recommended for use with Microsoft SQL Server 2000 MSDE Edition only.

1. To enable/disable the Automatic Database Backup Plan, click Change Settings and check/uncheck the Enable checkbox in the Change Automatic Database Backup Plan window.

1. Select the database backup plan options according to your needs. The description below provides additional details:

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 automatic backup plan are implemented as SQL Server Agent jobs and may be managed using the SQL Server Agent Jobs listing in the SQL Jobs tab of the Database Monitoring and Maintenance Snap-in. 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. (See the SQL Jobs Plug-in chapter of the MetaConsole Guide for more information.)

Important! The database maintenance procedures utilize the SQL Server Agent provided with all editions of Microsoft SQL Server 2000. 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 SQL Jobs Plug-in chapter of the MetaConsole Guide.

1. Click the Enabled checkbox to enable the backup plan and click OK. The database backup plan will be implemented.

Three components of the database maintenance plan appear in the SQL Server Agent Jobs listing only if the maintenance plan is enabled. The names of the jobs contain the name of the currently attached database. In the SQL Server Agent Jobs listing, the following SQL Jobs will appear:

DB Backup Job for DB Maintenance Plan - the name of the job saving a full database backup.

Optimizations Job for DB Maintenance Plan - the name of the job performing query optimization.

Transaction Log Backup Job for DB Maintenance Plan - the name of the job saving transaction logs.