Automate a backup with SQL Server 2005 Express

When it comes to backing up a SQL database it is simple to initially setup a SQL Maintenance Plan and schedule this to backup the database and log files and then start to work on a comprehensive strategy.

But the problem comes when you need to do this with a SQL Server 2005 Express database as this version does not come with a SQL Agent so you cannot use a Maintenance Plan.

The solution comes in using a SQL Script file, configure this to backup the databases and then move these files off to a location to be backed up to tape or disk.

The first thing to do is to install the SQL Server Management Studio as this will help you generate the SQL script file, the Management Studio can be downloaded here



Once you have connected to the SQL Server instance, find the database you want to backup and then choose the option to backup the database. Once you have setup the backup choose the option to Script | Script Action to File.

This will allow you to save the backup and a .SQL file which can then be ran as a script later. Save the file to a location for your SQL backup scripts.

Now you have a .SQL file you can open this and Management Studio will open the file and hows the SQL commands, execute the script to test the backup. If this is successful and you have the relevant .BAK files you are ready to automate this process.

Using Windows Scheduled Tasks create a new task to backup the SQL Databases and when asked for the application to use browse to

C:\program files\microsoft sql server\90\tools\binn\SQLCMD.EXE

This application will run the .SQL file as a command line, next save the task with the appropriate schedule for your backups.

Open the task and edit the command line for the task to add the details of which instance to backup and where the .SQL file is located. This is appended to the command as follows

SQLCMD -S .\SQLSERVERINSTANCE -i "C:\MySQLBackup\Backup.sql"

Save the task and manually run the task to confirm it will backup your databases correctly.

Once this part is completed you can now setup the move of the BAK files with the robocopy application.

Robocopy is a great tool to copy and move files, and it is part of the Windows 2003 Resource Kit that can be downloaded from

http://www.microsoft.com/downloads/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en

Robocopy has many options but in my case I wanted to move the files to another location for backup to disk so I used the command

robocopy C:\MySQLBackups \\server\sqlbackup /MOV

The /MOV switch will delete the source files once copied but will NOT delete the source folder, this means we keep the folder referenced in the SQL backup script.

I then used scheduled tasks again to create a new task that executes the robocopy command at 30 minutes after the SQL backup executes.

Now I have a daily SQL backup and the files are moved off to a network share for backup to disk later.