| |
|
|
| 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 jas@sqldbatips.com
Related Articles
 Automating Database maintenance in SQL 2005 Express Edition Part II
Getting Started with SMO in SQL 2005 - Restores
Getting Started with SMO in SQL 2005 - Integrity Checks
Getting Started with SMO in SQL 2005 - Backups
Getting Started with SMO in SQL 2005 - Verifying Backups
SM0 2005 - Populating a list of SQL Servers
GettingStarted with SMO in SQL 2005
|
|
articles
