How to backup SQL Server 2005 Express

Part 2 – Schedule the backup

This part is simply about creating a Windows scheduled task – pretty easy really.

If you installed SQL Server Express into the default location the syntax for the command you need to run is as follows (note the Binn directory used, not Bin!).

C:Program FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE -s  -i

For example:

C:Program FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE -s my_database -i c:scriptsbackup_my_database.sql

So, let’s setup the scheduled task.

  • Open Control Panel and select Scheduled Tasks.
  • Click Add Scheduled Task, and browse to C:Program FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE when prompted for the command to run.
  • Select an appropriate frequency for the job and click Next.
  • Enter when you want the job to run and click Next.
  • Enter the credentials for the job. These credentials should have permissions to both the database and the directory the database will be backed up to. CLick Next.
  • Check the box that says open advanced properties for this task when I click Finish then click Finish.
  • Change the ‘Run’ command to be the complete command above including the name of the database and the script you want to run, e.g. C:Program FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE -s my_database -i c:scriptsbackup_my_database.sql.
  • Click OK and confirm the password for the user running the scheduled task if you are prompted to do so.

That’s it, you’re done. If you want to make absolutely sure it’s going to run as expected you can right-click the scheduled task and select ‘Run’ – this will do make sure everything is configured ok. Make sure you see a SQL backup file in the selected location after this. If so, everything should be ok for the scheduled run.

Don’t forget to add the backup directory to your offsite or tape backups (if you are doing them, AND YOU SHOULD BE!)

Here is a complete backup script for one of my databases. It’s configured to backup everything in a database called ‘prod’ and retain the backups for a period of 7 days after which they will expire. This helps limit the amount of disk space used by the backups. This script is also set to verify the backup when done.

BACKUP DATABASE [prod] TO  DISK = N'C:BackupsSQLprod.bak' WITH  RETAINDAYS = 7, NOFORMAT, NOINIT,  NAME = N'prod-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'prod' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'prod' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''prod'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:BackupsSQLprod.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO