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.


Hey Doc, can we attach only the data file in SQL Server?

“Hey Doc, I have salvaged the MDF file of an important database. Can I attach it to SQL Server in order to use it?” was the question from an old colleague.

Yes, you can…

“And oh! I don’t have the log file. And I renamed the MDF file by mistake too.”

No problem…

This is how you do it…

When you bring up the Attach Databases dialog and select the salvaged and renamed file, you would see the following on it.

Attach Database Dialog

The second grid lists the original database files followed by the Not Found message. This is obviously since you do not have them in the original location. At this stage you cannot move ahead by clicking on OK. You will have to remove both the entries using the Remove button below that grid.

Then you have to add the new data file; click on the Add Catalog button, then browse and select the new MDF file (the one which you had salvaged and renamed). You can then optionally change the name of the new (to be attached) database from the Attach As textbox.

Attach Database Properties

Click on OK and Voila!, you’ve got your old database back.