Automating backups for SQL Server 2008 R2 Express Edition

One of the limitations of SQL Server Express is that you can’t automate backups in it via the SQL Server Agent like you can the other versions of SQL Server. You have to manually write the backup scripts and then schedule them to run in Windows Scheduled Tasks. This really isn’t a big deal, but it is annoying.

Automating backups in SQL Server Express really isn’t that hard, but to save someone else the trouble of having to figure out how to do it I’ve posted the below solution that I’ve used in the past. It’s a quick-n-dirty script, which I’m sure can be improved, but it works well.  For the sake of this example, I’m going to be directing everything to a “DatabaseBackups” directory, which has two directories in it. “Scripts” for all of the scripts we’re going to write, and “Daily” for all of the daily backup files to be stored.

First, here’s the actual SQL script that backs up the database. We’re going to save it as dailyBackupScript.sql. (NOTE: Replace the text “YourDBhere” with your database name, and specify the desired location of your backup directory)

*Update 07/07/2015 – If you’d rather use Ola Hallengren’s solution, see my post on Using Ola Hallengren’s SQL Server Maintenance Solution with SQL Server Express Edition.

You can actually run the above code in SQL Server Management Studio (with the correct database name, of course) and it will back up your SQL Server Express database.

Now, the next thing we need to do is make sure that our backup files get deleted after a certain amount of time. In this particular case, let’s only keep 30 days worth of backup files on the server. Below is a VB script that will go through our backup directory and delete any .BAK files that are over 30 days old. It will also keep a log file of everything it deletes. We’re going to save this as deleteDailyBAK.vbs. (NOTE: Replace the “sFolder” value below with whatever directory you specified for your backups in dailyBackupScript.sql)

And finally, we need to create a cmd file that calls both processes together. This is the file that we’ll actually schedule to run every day. We’re going to save this as dailyBackup.cmd (NOTE: Replace the directory paths listed below with whatever directory you specified for your backup scripts. You will also need to replace “SQLEXPRESS” with your database server and instance names.)

And that’s it. Just schedule dailyBackup.cmd to run in Windows Scheduled Tasks for each day, and you’ve got daily automated backups of your SQL Server 2008 R2 Express Edition databases.

(Visited 1,118 times, 2 visits today)

1 thought on “Automating backups for SQL Server 2008 R2 Express Edition

Leave a Reply