TexCopy Sql Server 2005

I found lot of questions on the missing in ability of SQL Server Express to automatically perform database backups/integrity checks/reindexing regularly with a Maintenance Plan. Since SQL Express doesn't have Maintenance Plans, it takes a bit of extra effort to set that up. Here's how I do it:

A Windows Scheduled Task runs daily, kicking off sqlexpressmaintplan.cmd:

sqlcmd -S SQLSERVER\INSTANCE -i d:\scripts\sqlexpressmaintplan.sql
gzip -fNr e:\SQLBackups

(The second line simply compresses (using gzip) the .BAK files to conserve space.)

This executes sqlexpressmaintplan.sql, which has the configuration for my "maintenance
plan". At a glance, it looks like a lot, but I've got it broken down into separate sections/tasks, so it's easy to comprehend and maintain.

-- integrity check on system databases; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- integrity check on user databases; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'CHECKDB',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- reindex my database; save reports for 7 days

exec expressmaint
@database = 'MyDatabase',
@optype = 'REINDEX',
@reportfldr = 'e:\SQLReports',
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup system databases; verify; save backups for 2 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_SYSTEM',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 2,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

-- backup user databases; verify; save backups for 4 weeks; save reports for 7 days

exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'e:\SQLBackups',
@reportfldr = 'e:\SQLReports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 4,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1


The real magic happens in the expressmaint stored procedure, created by SQL Server MVP Jasper Smith. (Also available as a standalone executable.) You can read more about this great tool on sqldbatips.com in two articles covering the stored procedure and the standalone app.

It's been a really long time since I originally set this up, and I'm having to revisit it now (and write about it) because I just rebuilt my primary workstation and server at home. Now that I'm reading back over this article, I can't remember why I chose to use the stored procedure instead of the executable version -- the exe would be a bit easier to drop into place, along with the batch file (the SQL script would no longer be necessary), next time I have to set this up on a fresh SQL
Express install. I'm going to try out the executable version this time around to see if it's any easier to set up and maintain (it looks like it will be).