Automating Backups on SQL Server Express Version

By | July 13, 2010

Automating Backups on SQL Server Express Version is not as it does not have a SQL Server Agent installed with the instance but dont 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:
Lets say we have a Database called “TestDatabase”, here is how its done.

DECLARE @Filename varchar(100)
SET @Filename = N'C:SQL BackupsTestDatabase-' + 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 whats the filename, in our sample its 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 thats the second part of the script above.
For more info about the BACKUP command you can go here http://msdn.microsoft.com/en-us/library/ms186865.aspx

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 FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE" -S SQLSERVERNAMESQLInstance -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 whats the instance name on your machine.
-i - Pertains to teh cript you want to run
For more info about SQLCMD you can go here http://msdn.microsoft.com/en-us/library/ms162773.aspx

Once you verified that it is running all ok you can now create you 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 FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE”
Arguments : -S SQLSERVERNAMESQLInstance y  -i “C:YourBackupScript.sql”

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

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


Leave a Reply