Automating SQL 2005 Express Edition Backups

SQL2005 Express Edition w/ Management Studio Express looks like it is a very nice DB and management console, it falls down in one place (that I can see) and that is it is missing the maintenance Wizard that was found on SQL 2000.

Now I cant complain as it is a free application and a small stored procedure can restore most of the functionality as regards backups.

Not all the procedures that are required are activated by default and it is very simple to re-activate them. simply copy and paste this code into a text editor and save it as c:/enableprocs.sql

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now execute this command from a "run" prompt

sqlcmd -S .\SQLExpress -i c:\enableprocs.sql

this enables the Ole Automation Procedures and the XP_CMDshell.

Get the expressmaint.sql script here

extract the script and save the .sql file to c:\expressmaint.sql

execute this command

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

This installs the expressmaint stored procedure which is necessary for this backup method.

PARAMETERS

@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

This is the script that I use to backup all user databases, save it as  fullbackup.sql in c:\
exec expressmaint
   @database      = 'ALL_USER', 
   @optype        = 'DB',
   @backupfldr    = 'd:\backups',
   @reportfldr    = 'd:\reports',
   @verify        = 1,
   @dbretainunit  = 'weeks',
   @dbretainval   = 1,
   @rptretainunit = 'weeks',
   @rptretainval  = 1,
   @report        = 1

If you look at the parameters you will see that it backups all user databases, it puts the backups in d:/backups and the reports in d:/reports
it also retains reports and backups for 1 week.

Then just set up a scheduled task in windows to run

sqlcmd -S .\SQLExpress -i c:\fullbackup.sql

on whatever schedule you like. I recommend daily backups if not every 12hrs.