Automating SQL Express Backups

SQL Express allows you to distribute a free edition of SQL Server with your applications. There are an enormous amount of 3rd party applications that use it and MSDE (it’s SQL Server 2000 equivalent) for their data repository such as Sharepoint (by default). Generally speaking, the user may not even know they have the software on their workstation since it doesn’t install anything on their Programs menu. The main weakness in SQL Express is that it doesn’t have a scheduling tool. In this set of scripts, you’ll receive a canned solution for automating backups of SQL Express. The same logic can be applied to MSDE by changing SQLCMD to OSQL in the batch file. No other syntax used is SQL Server 2005 specific.

 

The script contains three files:

 

• BackupExpress.sql – Does the bulk of the work, backing up every database on the instance other than tempdb.
• BackupExpress.cmd – The batch file that executes the script. Must pass in the instance name.
• ScheduleBackups.cmd – Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.

To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.

 

I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week’s backup. So, there’s lots of room for improvement if you’re feeling adventurous. Please send me any updates and I’ll post them for all, giving you credit. The main thing missing from this solution is the index rebuilds, which I’ll probably add in the next release.