Schedule a Daily Backup with SQL Server Express

As you know, Microsoft SQL Server Express Edition does not have Agent Service, which can be used to schedule daily backups or other periodic administrative tasks. But you can use it for this standard Windows Schedule tool.

SQL Server Express Edition has some limitations, but anyway, it is a very powerful tool for storing data. For most small or medium size projects, you can use it without any restrictions. But Express edition has a little problem, it does not have a SQL Server Agent. However, you can use SQLCMD command line tool and standard Scheduled Tasks for Windows instead of it. If you want to set automating administrative periodic task, first of all, you need to write a SQL script. We want to solve a problem: write a script which will do a daily backup. Of course, you can use Microsoft Management Studio for generating current script (as you know, you can download Management Studio Express Edition too from the Microsoft site): just click “Script Actions to…” instead of OK button at “Back Up Database” dialog box and you will get a script.

For daily backups, I usually use this script:

DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'D:\Backup\db_backup_' + Convert(varchar(8), GETDATE(), 112) + '.bak' 
BACKUP DATABASE [MyDataBase] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  _
	NAME = N'db_backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

This script makes a backup file with name db_backup_YYYYDDMM.bak where YYYYDDMM is the current date. This format will give you an opportunity to have every day backup at separate files. Correct this script and try to run it, check that you will get a backup file (I set “d:\backup” folder for files, change it if you want). Next step – save this script to file schedule.sql and put it to “c:\scheduled tasks\” (you can choose any other names for script file and folders, just make sure that you will change it at all places where it will be used). At this folder, create a file backup.bat with next contain:

sqlcmd -S SEVERNAME -U UserName -P Password -i schedule.sql
7z a -tzip D:\Backup \db_backup_%date%.zip -i! D:\Backup\db_backup_*.bak
del d:\Backup\db_backup_*.bak

Where: SERVERNAME – database instance name (server name), UserName – SQL user, which has privileges for making backups, Password – password of this user, schedule.sql – name of the script with we created in the previous step. Second and third lines of this bat script do an archive of this backup file and then delete the backup file. I use 7z (http://www.7-zip.org/) utility for this. If you want, you can use any other archive tool, or maybe leave backup file unzipped (Microsoft SQL Server 2008 and higher has an opportunity to backup database with compression, so you can just set it in SQL script). If you will use 7z like me, you need to set full path to 7z.exe tool or you can just copy 7z.exe and 7z.dll files from installed folder of 7z tool to folder where this bat script will be located. And make sure that if you used other folder names or file names in the previous steps, you change these names in this bat script too. You can try to run this bat script and check that the backup file will be created and zipped.

The last step is to create a schedule task. In each Windows version, it can be a different way to create this task. This is how to create schedule task for Windows XP, this link is how to for Windows 7. For other versions, I think you can find a how-to at Windows help or other blog posts.

When you will create this Windows task, you should check that the user by whom these tasks will be launched has rights to create files at folders, where script will put backups, and this user has the right to execute bat file.

Like this, you can run not only daily backup. You can execute some procedures, re-index tables or maybe some other administrative stuff – just place what you want at schedule.sql script.