SQL Server 2005 Express Scheduled Backup – How?
A lot of my articles make use of or mention SQL Server Express, the light and cut-down and FREE version of Microsoft’s flagship database product Microsoft SQL Server.
One of the features of the full and very expensive version of SQL Server 2005 is the ability to run scheduled backups using SQL Maintenance Plans. SQL Server requires SSIS (SQL Server Integration Services) for these to be available – unfortunately SQL Server Express doesn’t include SSIS. There is a way to get scheduled backups for SQL Server Express though.
Before you begin you’ll need to install SQL Server Management Studio Express.
Part 1 – Create a backup script
- Open SQL Server Management Studio Express and login as a user with sufficient permission to access the database(s) you want to backup. You can do this as ‘sa’ if you want.
- Expand the name of your server or instance, expand Databases and select the database you want to backup.
- Right-click the database name, select Tasks and then select Back Up.
- Set the options you require for the backup. For example you might want to change the location where the backup files get created, edit backup set retention period etc.
- Once you’ve set the options necessary click the Script drop-down option at the top of the backup window and select either ‘Script Action to File’, or, if you want to see/edit the contents of the script before saving it, select ‘Script Action to New Query Window’. I find it’s best to script the backup to a new query window because you can test it before saving.
- Save the created script when you’re finished editing or enter an appropriate filename immediately if you selected ‘Script Action to File’.
- If you saved the script, open it up – we’re going to test it before making it ‘live’.
- Once you have the script open click the checkmark/tick button just to make absolutely sure the script is valid (it’s SQL Server-generated so it’d better be!).
- Click the ‘Execute’ button if you want to run a test. If you are backing up a very large database this can take a while and you won’t get a whole lot of feedback while the backup is being run. When the backup finishes the results will be displayed in the Results pane, successful or not. If it fails, figure out why (e.g. check for sufficient disk space, permissions for the folder you’re backing up to etc).
The complete script for one of my databases is shown at the end of this article.
Part 2 – Schedule the backup
This part is simply about creating a Windows scheduled task – pretty easy really.
If you installed SQL Server Express into the default location the syntax for the command you need to run is as follows (note the Binn directory used, not Bin!)
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -s <database> -i <sql script> |
For example:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -s my_database -i c:\scripts\backup_my_database.sql |
So, let’s setup the scheduled task.
- Open Control Panel and select Scheduled Tasks
- Click Add Scheduled Task, and browse to C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE when prompted for the command to run.
- Select an appropriate frequency for the job and click Next.
- Enter when you want the job to run and click Next.
- Enter the credentials for the job. These credentials should have permissions to both the database and the directory the database will be backed up to. CLick Next.
- Check the box that says open advanced properties for this task when I click Finish then click Finish.
- Change the ‘Run’ command to be the complete command above including the name of the database and the script you want to run, e.g. C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -s my_database -i c:\scripts\backup_my_database.sql.
- Click OK and confirm the password for the user running the scheduled task if you are prompted to do so.
That’s it, you’re done. If you want to make absolutely sure it’s going to run as expected you can right-click the scheduled task and select ‘Run’ – this will do make sure everything is configured ok. Make sure you see a SQL backup file in the selected location after this. If so, everything should be ok for the scheduled run.
Don’t forget to add the backup directory to your offsite or tape backups (if you are doing them, AND YOU SHOULD BE!)
Here is a complete backup script for one of my databases. It’s configured to backup everything in a database called ‘prod’ and retain the backups for a period of 7 days after which they will expire. This helps limit the amount of disk space used by the backups. This script is also set to verify the backup when done.
BACKUP DATABASE [prod] TO DISK = N'C:\Backups\SQL\prod.bak' WITH RETAINDAYS = 7, NOFORMAT, NOINIT, NAME = N'prod-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'prod' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'prod' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''prod'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\Backups\SQL\prod.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO |
9 Responses to “SQL Server 2005 Express Scheduled Backup – How?”
Leave a Reply
I can get the backup script to run but the easy part isn’t working for me. I am using Windows 7 scheduled tasks and recieving the error “The file name, directory name or volume label syntax is incorrect, 0x8007007B.
Thanks,
Alan
Alan: Hmm that’s odd. Have you tried setting the ‘start in’ directory for the scheduled task? Sorry but I’m on my Mac right now so don’t have access to Windows 7 to confirm the exact setting. Do you have any further information?
I was able to get it to run.
Now I am trying to delete the expired files. Have you found a way to do that?
I could create 7 jobs and overwite Monday’s file on Monday, Tuesday’s on Tuesday etc. Do you have a “more elegant” way of getting this done?
Thanks,
Alan
Alan Stealth: Given the limitations of not having full access to SSIS etc I just use a scheduled task to do this. It’s probably not as ‘elegant’ as overwriting the existing files but it does work. It also gives me the opportunity to do more than just delete the files at the scheduled time, e.g. send me a message if the deletion fails, send me a message if it’s successful etc.
Hi chris, thank you for the script but i having problem restore the database, it said :”System.Data .SqlClient.SqlError: the tail of the log for the database “” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.”
Please help me out on this. Thank you!
Vincent: I’m sorry to if this sounds bad but have you tried doing what it suggests and modifying the script so that it includes the NORECOVERY option?
Is it possible to add the date to the filename. EX: db_backup_2010_1_14.bak?
Chris H: Yes it is definitely possible to do that. You would use the YEAR function (http://doc.ddart.net/mssql/sql70/ya-yz.htm) etc in conjunction with the GETDATE function (http://msdn.microsoft.com/en-us/library/aa258905%28SQL.80%29.aspx) of Transact-SQL in SQL Server. You would also need to set the filename as a variable using DECLARE first and then use it in the script as mentioned in the article. http://msdn.microsoft.com/en-us/library/ms187752.aspx has some good info about data types/variables in MS T-SQL.
Hope that helps.
Running into an issue with the credentials, I think. When I open the SQL Studio Express, I need to click on OK to get in. When I run the script, I need to do the same thing. When I use the credentials that will work with the system, the script fails in the task scheduler but really no error information is offered.
Using XP Pro
SQL Studio Express 2005