Click here to Skip to main content
13,899,467 members
Click here to Skip to main content
Add your own
alternative version


24 bookmarked
Posted 28 Sep 2010
Licenced Ms-PL

Schedule a Daily Backup with SQL Server Express

, 30 Jun 2017
Rate this:
Please Sign up or sign in to vote.
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' 

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 \ -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 ( 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.


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

Software Developer (Senior)
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Member 123643903-Jul-17 22:31
memberMember 123643903-Jul-17 22:31 
QuestionVery Nice Pin
ifink10-Sep-15 6:24
memberifink10-Sep-15 6:24 
SuggestionSchedule a daily backup with SQL Server Express Pin
Deepak.jais877-May-12 3:30
memberDeepak.jais877-May-12 3:30 
GeneralMy vote of 4! Pin
Vivek Johari15-Nov-10 5:49
memberVivek Johari15-Nov-10 5:49 
GeneralMy vote of 3 Pin
aunanue2-Oct-10 5:16
memberaunanue2-Oct-10 5:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190306.1 | Last Updated 1 Jul 2017
Article Copyright 2010 by outcoldman
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid