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.

DECLARE @Filename varchar(100)
SET @Filename = N'C:\SQL Backups\TestDatabase-' + _
	convert(varchar, GetDate(), 102) + '.bak'
BACKUP DATABASE [TestDatabase] TO DISK = @Filename WITH NOFORMAT,
INIT, NAME = N'TestDatabase-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10

declare @backupSetId as int
select @backupSetId = position from msdb..backupset _
    where database_name=N'TestDatabase' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'TestDatabase' )
if @backupSetId is null
begin raiserror(N'Verify failed. _
	Backup information for database ''TestDatabase'' not found.', 16, 1)
end
RESTORE VERIFYONLY FROM DISK = @Filename WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

You can see from my parameters that I had used the following:

1. DISK - Where you want to save it and what's the filename, in our sample it's dynamically generated using the Date.
2. 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.
3. INIT - This specifies that all backup sets should be overwritten.
4. NAME - This specifies the name of the backup set it can have a maximum of 128 characters.
5. 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.
6. NOREWIND - This specifies that SQL Server will keep the tape open after finishing the backup.
7. NOUNLOAD - This specifies that after the backup, the tape will still remain loaded on the drive.
8. 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%.

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.

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:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" 
	-S SQLSERVERNAME\SQLInstance -i "C:\YourBackupScript.sql"

Then you will see this on your screen:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 6872 pages for  database 'TestDatabase', file 'TestDatabase_data' on file 1.
100  percent processed.
Processed 2 pages for database 'TestDatabase',  file 'TestDatabase_log' on file 1.
BACKUP DATABASE successfully  processed 6874 pages in 1.030 seconds (54.669 MB/sec).
The backup set  on file 1 is valid.

Now going on to the parameters:

• -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:

Action: Start a program
Program/Script: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE”
Arguments: -S SQLSERVERNAME\SQLInstance y -i “C:\YourBackupScript.sql”

Now save that and go to the triggers tab and add your daily trigger:

There you have it, wait for the schedule to run and you now have your backup.