| Automating Database maintenance in SQL 2005 Express Edition Part II |
| Author |
Jasper Smith |
Hits |
31647 |
| Create Date |
18-08-2004 |
Last Updated |
11-12-2008 |
| Versions |
SQL2005, SQL2008 |
|
|
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.
| Switch |
Argument |
Required |
Description |
| -? |
N |
N |
Displays help for the ExpressMaint utility |
| -S |
Y |
Y |
The SQL Server for ExpressMaint to connect
to |
| -U |
Y |
N |
SQL Authentication Login. If not specified a trusted windows connection is used. If specified a non blank password must be supplied |
| -P |
Y |
N |
SQL Authentication Password |
| -D |
Y |
Y |
The target database
for the maintenance operation. Valid values are a single database name,
ALL_USER which will
process all user databases, ALL_SYSTEM which will process all system
databases and ALL which will process all databases |
| -T |
Y |
Y |
The type of maintenance
operation to be performed. Valid values are
- DB - Full Database Backup
- DIF - Differential Database Backup
- LOG - Log Backup
- CHECKDB - Database Integrity Check
- REINDEX - Rebuild all indexes
- REORG - Reorganize all indexes
- STATS - Update all statistics using default sampling
- STATSFULL - update all statics with fullscan
|
| -B |
Y |
N |
The base folder to
write the backups to. Sub folders will be created for each database |
| -V |
N |
N |
Indicates whether to
verify the backup file |
| -BU |
Y |
N |
The unit of measure
for the -BV argument. Valid values are minutes, hours, days, weeks. The
combination of these two arguments determines for how long old backup
files are kept. Required if -B argument is specified. |
| -BV |
Y |
N |
The time period of
old backups to keep. Required if -B argument is specified. |
| -R |
Y |
N |
The folder where maintenance reports are
written to |
| -RU |
Y |
N |
The unit of measure
for the -RV argument. Valid values are minutes, hours, days, weeks. The
combination of these two parameters determines for how long
old reports are kept. Required if -R argument is specified. |
| -RV |
Y |
N |
The time period or
number of copies of old reports to keep. Required if -R argument is specified. |
| -A |
N |
N |
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. |
| -DS |
N |
N |
If this switch is specified then the timestamp for backup and report files is appended to the front of the filename (the default is to append to the end of the filename) |
| -TO |
Y |
N |
This switch allows the setting of a statement timeout specified in minutes (e.g. -TO 10). If not specified, the default is 10 minutes. |
| -C |
N |
N |
This switch allows multi database maintenance operations (ALL,ALL_USER,ALL_SYSTEM) to continue if an error is encountered with one or more databases |
| -BF |
Y |
N |
This switch allows specifying custom backup filename formats. Accepted tokens are $(DB) for database name, $(DATE) for current date, $(TIME) for current time and $(OPTYPE) for backup type |
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
 Automating Database maintenance in SQL 2005 Express Edition Part I
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
