Overview

In this series of articles, I'll demonstrate a couple of different approaches to writing a maintenance utility that mimics some of the behavior of the sqlmaint utility that is included with SQL Server 2000. SQL Server Express Editions do not include such a utility so these articles will show how we can easily create one ourselves using either TSQL or SMO (SQL Management Objects - the successor of SQL-DMO). Rather than dive into the code, these articles will demonstrate how to use these utilities for backing up and maintaining your databases and how to schedule these tasks using the Scheduled Tasks facility in Windows XP and Windows 2003.

In this article we will concentrate on the SMO version of the utility which is in the form of a standalone console application - ExpressMaint. To vew articles on performing maintenance operations using SMO including sample code see the Related Articles section at the bottom of the page. You can grab the sourcecode for the ExpressMaint utility from the related ExpressMaint Project Codeplex site.

ExpressMaint Utility

Click here to Download the ExpressMaint utility for SQL 2005

Click here to Download the ExpressMaint utility for SQL 2008

Click here to Download the ExpressMaint source code from Codeplex

The ExpressMaint utility supports the following operations

• Full Database Backup
• Differential Database Backup
• Log Backup
• Housekeeping of backup files
• Database Integrity Checks
• Database Index Rebuilds
• Database Index Reorganization
• Database Statistics Update
• Report Creation

Please note that double quotes are required around any command line arguments that include spaces. All switches are case sensitive. For switches that take an argument, there should be a space between the switch and the argument. The argument column in the table below indicates whether a switch takes an argument or not.

Example Syntax

Note that the entire command should be on one line even though some examples span multiple lines for formatting purposes

1) Full Database Backup of all user databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 week and continue processing other databases if an error is encountered baking up a database

expressmaint -S (local)\SQLExpress -D ALL_USER -T DB -R c:\reports 
	-RU WEEKS -RV 1 -B c:\backups -BU DAYS -BV 1 -V -C

2) Full Database Backup of all system databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 week and reports for 1 week

expressmaint -S (local)\SQLExpress -D ALL_SYSTEM -T DB -R c:\reports 
	-RU WEEKS -RV 1 -B c:\backups -BU DAYS -BV 1 -V

3) Log Backup of all user databases to c:\backups, don't verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 day

expressmaint -S (local)\SQLExpress -D ALL_USER -T LOG -R c:\reports 
	-RU DAYS -RV 1 -B c:\backups -BU DAYS -BV 1

4) Check the integrity of the AdventureWorks database and report to c:\reports keeping reports for 1 week

expressmaint -S (local)\SQLExpress -D AdventureWorks -T CHECKDB 
	-R c:\reports -RU WEEKS -RV 1

5) Rebuild all indexes in the AdventureWorks database and report to c:\reports keeping reports for 1 day

expressmaint -S (local)\SQLExpress -D AdventureWorks -T REINDEX 
	-R c:\reports -RU DAYS -RV 1

6) Update all statistics with fullscan in the AdventureWorks database and report to c:\reports keeping reports for 1 day

expressmaint -S (local)\SQLExpress -D AdventureWorks -T STATSFULL 
	-R c:\reports -RU DAYS -RV 1

7) A Full Backup of the AdventureWorks database specifiying a custom backup format to achieve a filename like AdventureWorks_foo_20091107.bak

expressmaint -S (local)\SQLExpress -D AdventureWorks -T DB
	-R c:\reports -RU DAYS -RV 1 -B c:\backups -BU DAYS -BV 1
	-BF $(DB)_foo_$(DATE)

Automating backups using ExpressMaint

Since SQL Server 2005 Express Edition does not include SQL Agent, we need to rely on the Windows Task Scheduler to run our maintenance tasks. If you are not familiar with how to set up a scheduled task, it's worth reviewing the Microsoft Knowledge Base article below

How to Schedule Tasks in Windows XP

• Double-click Add Scheduled Task to start the Scheduled Task Wizard, and then click Next in the first dialog box
• Click Browse, browse to Expressmaint.exe, and then click Open.
• Type a name for the task e.g DAILY FULL BACKUP and then choose Daily from the scheduling options
• Click Next, specify the information about the time to run the task e.g. 00:00, and then click Next
• Type the name and password of the account that will execute this task. Make sure that you choose an account that is a syadmin for your instance
• Click Next, select the checkbox to Open the Advanced Properties for this task and then click Finish
• In the Run text box append the arguments you want to pass to the ExpressMaint utility.(You must leave a space after the existing contents)
• Click OK. If prompted, supply the password for the account again

To supply feedback on this article and the ExpressMaint utility or to report bugs/issues email jas@sqldbatips.com

Related Articles