Expressmaint utility is a very handy tool to backup and do different maintenance activities for SQL server 2005/2008 databases.

You can perform various kind of administrative jobs in your SQL server database without having a in depth knowledge of SQL server maintenance facilities available with it. The jobs that can be done using this small but powerful utility are:

  • 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

The Expressmaint Utility can be downloaded from the below mentioned links:

ExpressMaint utility for SQL 2005: http://www.sqldbatips.com/samples/code/ExpressMaint.zip

ExpressMaint utility for SQL 2008: http://www.sqldbatips.com/samples/code/ExpressMaint2008.zip

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) -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) -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) -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) -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) -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) -D AdventureWorks -T STATSFULL -R c:reports -RU DAYS -RV 1

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

If you need to enable MSSQL Server read our Complete SQL Server 2019 Installation Guide with SSMS