Automating Backups on SQL Server Express Version
July 13, 2010 Leave a comment
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 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 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 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 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 Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE”
Arguments : -S SQLSERVERNAME\SQLInstance y -i “C:\YourBackupScript.sql”
There you have it wait for the schedule to run and you now have your backup.