Automating Database maintenance in SQL 2005 Express Edition Part I
Author Jasper Smith Hits 26214
Create Date 31-07-2004 Last Updated 28-07-2008
Versions SQL2005
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 2005 Express Edition does 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 to 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 TSQL version of the utility which is in the form of a stored procedure - expressmaint. To download a command line version built using SMO go to Automating Database maintenance in SQL 2005 Express Edition Part II. To vew articles on performing maintenance operations using SMO including sample code see the Related Articles section at the bottom of the page
Expressmaint Stored Procedure
To view the full code for the expressmaint stored procedure click here
The expressmaint stored procedure 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
Report Creation
Parameter Required Default Description
@database Y NONE The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases and ALL_SYSTEM which will process all system databases
@optype Y NONE The type of maintenance operation to be performed. Valid values are
DB - Full Database Backup
DIFF - Differential Database Backup
LOG - Log Backup
CHECKDB - Database Integrity Check
REINDEX - Rebuild all indexes
REORG - Reorganize all indexes
@backupwith N NULL Specify additional backup options as documented in BOL for the BACKUP WITH command
@backupfldr N NULL The base folder to write the backups to. Sub folders will be created for each database
@verify N 1 Indicates whether to verify the backup file.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@verifywith N NULL Specify additional verify options as documented in BOL for the VERIFY WITH command
@dbretainunit N NULL The unit of measure for the @dbretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old backup files are kept
@dbretainval N 1 The time period or number of copies of old backups to keep
@report N 1 Indicates whether to produce a report of the maintenance carried out.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@reportfldr N NULL The folder where maintenance reports are written to if @report = 1
@rptretainunit N NULL The unit of measure for the @rptretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old reports are kept
@rptretainval N 1 The time period or number of copies of old reports to keep
@checkattrib N 0 Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent deletion of files that have not been backed up onto tape.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@delfirst N 0 Indicates whether to delete old backups prior to doing the current backup. This is not advisable but can be useful if disk space is limited.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@debug N 0 Indicates whether print out debug information such as the commands generated and the contents of the temporary tables used in the procedure.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
Examples
To load the stored procedure into SQL Express using the sqlcmd utility simply download the code from here and save it as c:\expressmaint.sql. Open a command prompt and run the following command (assuming a named instance called SQLExpress)
sqlcmd -S .\SQLExpress -i c:\expressmaint.sql
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
exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'c:\backups',
@reportfldr = 'c:\reports',
@verify = 1,
@dbretainunit = 'days',
@dbretainval = 1,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1
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
exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'DB',
@backupfldr = 'c:\backups',
@reportfldr = 'c:\reports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 1,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1
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
exec expressmaint
@database = 'ALL_USER',
@optype = 'LOG',
@backupfldr = 'c:\backups',
@reportfldr = 'c:\reports',
@verify = 0,
@dbretainunit = 'days',
@dbretainval = 1,
@rptretainunit = 'days',
@rptretainval = 1,
@report = 1
4) Check the integrity of the AdventureWorks database and report to c:\reports keeping reports for 1 week
exec expressmaint
@database = 'AdventureWorks',
@optype = 'CHECKDB',
@reportfldr = 'c:\reports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1
5) Rebuild all indexes in the AdventureWorks database and report to c:\reports keeping reports for 1 day
exec expressmaint
@database = 'AdventureWorks',
@optype = 'REINDEX',
@reportfldr = 'c:\reports',
@rptretainunit = 'days',
@rptretainval = 1,
@report = 1
Automating backups using sqlcmd
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
The simplest way to pass our parameters to sqlcmd is to simply save the call to the stored procedure in a file. For example, we could copy the code from the Full Database Backup of all user databases example above (Example 1) and save it to c:\backup scripts\userfullbackup.sql. The walk through below assumes you have a named instance called SQLExpress.
Double-click Add Scheduled Task to start the Scheduled Task Wizard, and then click Next in the first dialog box
Click Browse, browse to SQLCMD.exe (by default it can be found in C:\Program Files\Microsoft SQL Server\90\Tools\binn), 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 following to the contents : -S .\SQLExpress -i"c:\backup scripts\userfullbackup.sql" (You must leave a space after the existing contents)
Click OK. If prompted, supply the password for the account again
An alternative to the penultimate step above is to remove the entire contents of the Run text box and simply supply the following
sqlcmd -S.\SQLExpress -i"c:\backup scripts\userfullbackup.sql"
Another alternative rather than maintaining an individual script for each task is to parameterize the script and take advantage of the ability to pass parameters to sqlcmd from the command line. If we take the same example script we used in the task above (c:\backup scripts\userfullbackup.sql), we could add parameters to it as shown below
exec expressmaint
@database = '$(DB)',
@optype = 'DB',
@backupfldr = '$(BACKUPFOLDER)',
@reportfldr = 'c:\reports',
@verify = 1,
@dbretainunit = '$(DBRETAINUNIT)',
@dbretainval = '$(DBRETAINVAL)',
@rptretainunit = 'copies',
@rptretainval = 2,
@report = 1
This allows us to pass in the database, backup folder and backup retention parameters from the command line. To simulate the same parameters as example 1, we would supply the following command to the task (note that this entire command should all be on one line)
sqlcmd -S .\SQLExpress -i"c:\backup scripts\userfullbackup.sql" -v DB="ALL_USER"
-v BACKUPFOLDER="c:\backups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"
As this demonstrates, sqlcmd is a lot more flexible than osql/isql and there are numerous options available for scheduling our maintenance tasks. In Part II of this article, I'll be demonstrating how we can build an expressmaint.exe command line utility using SMO to provide the same functionality. In the meantime, to supply feedback on this article and the code or to report bugs/issues email This email address is being protected from spambots. You need JavaScript enabled to view it.