Flexible and extensible task scheduling

A job can perform any of the predefined commands (such as database backup, differential backup, transaction log backup, reorganization, consistency check etc). You can also extend it with your own TSQL commands, all in the same job definition. These TSQL commands do not have to be written in any certain way. Any messages returned from the TSQL command execution will be logged to he Db Maint log.
The ability to execute TSQL commands along with your job definition gives you virtually endless functionality. Some examples:
 

• Correcting identity values. A sample stored procedure is available on the Db Maint web site (www.dbmaint.com).
   
• Performing Bulk Copy (before or after execution).
   
• Other batch processing.

Serial execution of tasks

All SQL Server installations need to maintain several databases (since you want to run maintenance jobs against your system databases). With Db Maint, one task contains several jobs, where one job defines a certain database (or group of databases). All jobs within a task are executed serially. You define the order for which the jobs execute and even the order for the different operations in the task definition.

Consistency checking

A job can include all important consistency checking commands (DBCC CHECKDB, CHECKCATALOG etc.). If any messages are returned from SQL Server, they will be logged to all specified log destinations. The error number is included as well, which makes it easier to search for information regarding each returned message.

Reorganization

Optimum performance can not be achieved if index statistics are out of date and if the tables and indexes are fragmented. A Db Maint job can include index rebuilds or updating index statistics information. For SQL Server 2000, either full index rebuild with locking (DBCC DBREINDEX) or index rebuild through moving pages only with minimal locking (DBCC INDEXDEFRAG) can be selected.

Notification

Your consistency checks are quite useless if you won’t be informed when something has gone wrong in the database.
Db Maint presents its log in the Db Maint application log window, the Windows Eventlog, through an email (MAPI or SMTP), to a HTML file and/or to a printer. The log includes all messages from the commands, including SQL Servers error number so that you (or your support provider) have as much details about the situation as possible. Messages are categorized as INFORMATION, WARNING or ERROR.

Backup

Database, differential and transaction log backup
Db Maint supports database, differential and transaction log backup to tape or disk.

Backup Generations
Db Maint allows you to specify that generations of backup files are to be stored. You can specify how many days you want to keep backup files.

Use of backup devices
Db Maint creates backup devices each time a backup is perfomed. This makes it easier to check that a backup device actually contains the backups that it should.

Advanced backup features

Test-restore and log shipping

You can select to automatically do a test-restore after each backup. If any error messages are returned, they will be logged in the Db Maint log. This is a good way to ensure that a backup is restorable. This can even be done on another SQL Server: log shipping.

Reporting of server and database configuration

The report can be exported to a text file. This is valuable for documentation purposes as well as for troubleshooting. A report can also be generated regularly though a Db Maint task.

Performance logging and presentation

Vital performance counters can be continuously logged. The logging is non-intrusive. Performance information is stored in a table and the format is very compact. This information is valuable for trend analysis and capacity planning. Db Maint client includes a graphical presentation of collected performance data. This information can also be exported to other tools, such as Microsoft Excel. Since the information is stored in a table, other tools can be used for analysis as well. Any performance monitor counters can be logged in Db Maint, since the logging is based on the performance monitor API.

Grouping of databases

You will probably find that you have several databases for which you want to define similar maintenance routines. The Database Group functionality allows you to group several databases and add them as a group to a job.

Support for "All Databases"

Some installations have many databases. It would be a tedious task to define a job for each database. With the "All" support, you simply define one job for all databases and a task for that job. That is all you have to do. This will also include databases that are created after that job was created. You can also exclude certain databases from the "all" group.

When using Database Groups or "all", msdb is run last

This is done automatically and is important since msdb contains information required to restore your databases easily. This information is stored in history tables and SQL Enterprise Manager reads those tables during restore. If msdb were not the last database in your backup schedule, you would risk restoring an unnecessarily old version of your database.

Note that this is per Job. If you have scheduled several Jobs within a Task, a later Job might backup a database after the Job that backed up msdb was run.

Interactive operations

The Db Maint client has two types of menu-driven interactive operations.

Pre-defined, which includes:

• Backup

• Restore

• Consistency check

• Fragmentation analysis

• Defragmentation

• View oldest open transaction

User Defined Commands: