Schedule a daily backup with SQL Server(ssms)

As you know MS SQL Server Express Edition hasn’t Agent Service, which can be used to schedule daily backups or other periodic administrative tasks. But you can use for this standard Windows Schedule tool.

SQL Server Express Edition has some limitation, but anyway it is very powerful tool for storing data. For most little or middle projects you can use it without any restrictions. But Express edition has a little problem, it hasn’t SQL Server Agent. But 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 problem: write a script which will do a daily backup. Of course you can use MS Management Studio for generating current script (as you know you can download Management Studio Express Edition too from 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 = 'E:\Backups\DatabaseBackup\Ulm\StadtUlmSitecore_Master_' + Convert(varchar(8), GETDATE(), 112) + '.bak' 
BACKUP DATABASE [StadtUlmSitecore_Master] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N'StadtUlmSitecore_Master', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

This script makes a backup file with name db_backup_YYYYDDMM.bak where YYYYDDMM – it is 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 INB032\SQLEXPRESS -U sa -P Simple@123 -i StadtUlmSitecore_MasterBackup.sql
7z a -tzip E:\Backups\DatabaseBackup\Ulm\StadtUlmSitecore_Master_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.zip E:\Backups\DatabaseBackup\Ulm\StadtUlmSitecore_Master_*.bak
del E:\Backups\DatabaseBackup\Ulm\StadtUlmSitecore_Master_*.bak

Where: SERVERNAME – database instance name (server name), UserName – sql user, which has a privileges for making backups, Password – password of this user, schedule.sql – name of script with we created on previous step. Second and third lines of this bat script do an archive of this backup file and then delete 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 (MS 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 will be located this bat script. And make sure that if you used other folder names or file names at previous steps you will change these names at this bat script too. You can try to run this bat script and check that backup file will be created and zipped.

Last step is to create schedule task. In each Windows versions it can be different way to create this task. This is how to create schedule task for Windows XP -http://support.microsoft.com/kb/308569, this is link how to for Windows 7 -http://windows.microsoft.com/en-US/windows7/schedule-a-task. For other versions I think you can find a how-to at Windows helps or other blog posts.

When you will create this Windows task you should check that user by whom these tasks will be launched has rights to create files at folders, where script will put backups, and this user has 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.