Automating Backups on SQL Server Express Version

Automating Backups on SQL Server Express Version is not possible as it does not have a SQL Server Agent installed with the instance, but don't worry as you can still do backups but manually using the free SQL Server Management Studio Express or automated by using a combination of scheduled task (Win2k3 below) / Task Scheduler (Win2k8) and the SQLCMD utility.

The SQLCMD utility comes with your SQL Express installation and lets you enter Transact-SQL statements, system procedures, and script files using the command prompt. So having said that, you can create a task running a saved script using this utility and schedule it on Task Scheduler.

First, you need a script to do your backup. And here is a sample:Let's say we have a database called “TestDatabase”, here is how it is done.

DISK - Where you want to save it and what's the filename, in our sample it's dynamically generated using the Date.

NOFORMAT - This specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior.

INIT - This specifies that all backup sets should be overwritten.

NAME - This specifies the name of the backup set it can have a maximum of 128 characters.

SKIP - This disables checks for the backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets.

NOREWIND - This specifies that SQL Server will keep the tape open after finishing the backup.

NOUNLOAD - This specifies that after the backup, the tape will still remain loaded on the drive.

STATS - This displays a message each time another percentage completes. You will see how this works when you run the backup as you will see something like this on your command prompt, this is for the 10%.

Now after backing up, you want to make sure that the backup works by verifying it using the “RESTORE VERIFYONLY” and that's the second part of the script above.For more information about the BACKUP command, you can go here.

Now the backup script is done and you can save it anywhere you want, but in this case we will save it as C:\YourBackupScript.sql.

Now you can test whether your script is running or not by typing this on the command prompt:

-S – Is for the servername, SQL Express usually creates instances for its database so check to make sure what's the instance name on your machine.

-i - Pertains to the cript you want to run.For more information about SQLCMD, you can go here.

Once you verified that it is running all OK, you can now create your scheduled task by going to Start -> Administrative Tools -> Task Scheduler. Now start creating one and go to the Actions tab and create an action like such: