Automating SQL 2005/2008 Express Backup in 5 Easy Steps

I was recently asked to backup all system and user databases on a SQL 2005 Express installation. Unfortunately, SQL 2005 Express native backup capabilities are not on par with SQL 2005 Standard/Enterprise. SQL Express includes functionality for backing up a single DB using SQL Server Management Studio Express, but this is only ideal for backing up a single database and it cannot be scheduled. So here are the steps for automating this process.  If you’d like more detailed information, check out this much more thorough article at http://www.sqldbatips.com/showarticle.asp?ID=27.

Step 1:

Save the lines here in a file called expressmaint.sql to C:\SQLScripts.

Step 2:

Open an administrator command prompt to “c:\SQLScripts\” and run “sqlcmd -S .\SQLExpress -i c:\sqlscripts\expressmaint.sql”.

Step 3:

Save the lines below in a file called sqlbackup.sql and save it in C:\SQLScripts.  Be sure to change the variable contents for @reportfldr to whatever is appropriate for your environment.

exec expressmaint
   @database      = '$(DB)',
   @optype        = 'DB',
   @backupfldr    = '$(BACKUPFOLDER)',
   @reportfldr    = 'c:\SQLScripts',
   @verify        = 1,
   @dbretainunit  = '$(DBRETAINUNIT)',
   @dbretainval   = '$(DBRETAINVAL)',
   @rptretainunit = 'copies',
   @rptretainval  = 2,
   @report        = 1

Step 4:

Save the lines below in a file called sqlbackup.cmd and save it in C:\SQLScripts.  Be sure to change “.\SQLExpress” to the name of your SQL Express server and instance” and don’t forget to change the BACKUPFOLDER location to whatever is relevant in your environment.  NOTE: Each “sqlcmd” begins a new line.

sqlcmd -S .\SQLExpress -i"c:\SQLScripts\sqlbackup.sql" -v DB="ALL_USER" -v BACKUPFOLDER="e:\SQLBackups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"

sqlcmd -S .\SQLExpress -i"c:\SQLScripts\sqlbackup.sql" -v DB="ALL_SYSTEM" -v BACKUPFOLDER="e:\SQLBackups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"

If you were to run the 2nd sqlcmd above your output would be similar to this:

Step 5:

Create a scheduled task to run “C:\SQLScripts\sqlbackup.cmd” at your desired backup frequency.