How to backup SQL Server 2005 Express

A lot of my articles make use of or mention SQL Server Express, the light and cut-down and FREE version of Microsoft’s flagship database product Microsoft SQL Server.

One of the features of the full and very expensive version of SQL Server 2005 is the ability to run scheduled backups using SQL Maintenance Plans. SQL Server requires SSIS (SQL Server Integration Services) for these to be available – unfortunately SQL Server Express doesn’t include SSIS. There is a way to get scheduled backups for SQL Server Express though.

Before you begin you’ll need to install SQL Server Management Studio Express.

Part 1 – Create a backup script

  • Open SQL Server Management Studio Express and login as a user with sufficient permission to access the database(s) you want to backup. You can do this as ‘sa’ if you want.
  • Expand the name of your server or instance, expand Databases and select the database you want to backup.
  • Right-click the database name, select Tasks and then select Back Up.
  • Set the options you require for the backup. For example you might want to change the location where the backup files get created, edit backup set retention period etc.
  • Once you’ve set the options necessary click the Script drop-down option at the top of the backup window and select either ‘Script Action to File’, or, if you want to see/edit the contents of the script before saving it, select ‘Script Action to New Query Window’. I find it’s best to script the backup to a new query window because you can test it before saving.
  • Save the created script when you’re finished editing or enter an appropriate filename immediately if you selected ‘Script Action to File’.
  • If you saved the script, open it up – we’re going to test it before making it ‘live’.
  • Once you have the script open click the checkmark/tick button just to make absolutely sure the script is valid (it’s SQL Server-generated so it’d better be!).
  • Click the ‘Execute’ button if you want to run a test. If you are backing up a very large database this can take a while and you won’t get a whole lot of feedback while the backup is being run. When the backup finishes the results will be displayed in the Results pane, successful or not. If it fails, figure out why (e.g. check for sufficient disk space, permissions for the folder you’re backing up to etc).