Scheduling Backups in SQL Server 2005/2008 Express Edition

Express editions of both SQL Server 2005 and 2008 do not include the SQL Agent service (even though it is there in an un-enablable disabled state in the SQL Server 2008 edition). This makes it a challenge when one wants to perform a scheduled backup of an Express edition database.

I had to perform such a task today, and here is the solution shared for your benefit. I must warn you however, that this is but a simple solution in order to satisfy one of our simple in-house tasks. You can of course expand upon it to do more wonderful things.

The steps I performed were:

  1. Wrote a stored procedure which would create a backup.
  2. Wrote T-SQL code to execute the stored procedure and put it in a .sql file
  3. Wrote a batch file to call the .sql file.
  4. Schedule the batch file using the Windows scheduler.

Take a look at the Code Sample.

If you would like to schedule much more complex maintenance tasks on SQL Server 2005/2008 Express edition databases, here is something cool: ExpressMaint.