Management Plug-in

If enabled, the Management Plug-in is displayed in the Database Monitoring and Maintenance Snap-in as a Management tab.

The Management tab provides the ability to change the database user password, modify SQL Server Memory Usage settings, Backup and Restore databases, and institute an automatic database backup plan for Microsoft SQL Server 2000 MSDE Edition.

Changing the database user password

1. Click Change User Password and specify a new password in the “Change user password” window.

1. Type the old password in the Old password field.
2. Type the new password in the New password field.
3. Optional: Though not recommended, blank passwords can be used by clicking the Blank password checkbox.
4. Click OK when done. The password will be changed.

Modifying SQL Server Memory Usage settings

1. Click Server Memory Usage to modify the memory usage settings for Microsoft SQL Server in the “Set SQL Server memory usage” window.

1. Select Dynamic memory configuration or a Fixed memory size.

If dynamic usage is set, the database server will dynamically determine how much memory to allocate based on how much memory the operating system and other applications are currently using. The “Minimum” configuration option is used to ensure that the database server does not release memory below this value. The “Maximum” configuration option is used to specify the maximum amount of memory the database server can allocate when it starts and while it runs. Clicking on “Use a fixed memory size” will fix the amount of memory available to the database server.

1. Click OK when done. The SQL Server memory usage settings will be adjusted.

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.
2. 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.

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.

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

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.

Important! The database maintenance procedures utilize the SQL Server Agent provided with all editions of Microsoft SQL Server. 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 “SQL Jobs Plug-in” on page 84.

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.