Automate your SQL Server Express Backups

by Philipp Stiefel, originally published February 9^th, 2017

Based on a photo by Cz_Miki, used here under CC0 licensing

This is the second part of a three-part series. If you are familiar with SQL-Server-Backups and just want to know how to implement an automatic backup of your SQL Server Express Database without SQL Agent, just read on.

If you never dealt with SQL Server backups before, I strongly recommend you go back to the previous article on the theory of SQL Server backups and read it first.

SQL Server Express

Microsoft SQL Server Express Edition is the free edition of Microsoft’s SQL Server. SQL Server and especially its Express Edition is the natural upgrade path for a plain Access database, which has outgrown Access in size, concurrent users, and/or data security requirements.

SQL Express is missing one critical component you are going to need if you are hosting a workgroup of small business database on it. It’s missing SQL Agent, SQL Server’s scheduling component to run automatic tasks, including automatic backups of your databases.

As SQL Express supports the backup features of SQL Server, you can do manual backups in SQL Server Management Studio exactly the same way as you would do with a “Full” Edition of SQL Server. What you can’t do with SQL Express is schedule your backups to be run automatically, neither via the UI nor via SQL Scripts.

Manual backup for production data is not an option. Someday you will forget to do a backup. And, according to Murphy’s Law, that will be day before something catastrophic happens to your database. So, unless your database in SQL Express is only a replication of a database that lives on a “big server” somewhere else and is regularly backed up on that other server, you need to think about backing up your data from day one.

Windows Task Scheduler

The obvious solution to scheduling anything on a Windows computer is Windows Task Scheduler, which comes with any edition of Microsoft Windows since Windows 98.

My first idea was to use the neat command line maintenance utility of SQL Server (sqlmaint.exe). This would have enabled us to create a backup directly from the command line or from Task Scheduler. Unfortunately, this utility does not work with SQL Express.

The universal tool to access a SQL Server Instance from a Task Scheduler task is the very useful command line client SQLCMD. SQLCMD can connect to a SQL Server instance and enables you to execute any SQL command or even a SQL Script file from the command line.

The BACKUP command in T-SQL

To use SQL Server’s integrated backup features without any UI-interaction, you simply execute the BACKUP command in Transact-SQL.

The BACKUP command has as many option as the Backup Dialog in SSMS, but for backing up a SQL Express database you are probably going to need basic options only.

The minimal backup instruction would be this:

This just backs the database named YourDatabase to a file YourDatabase.bak the filesystem. You can pass either a full file system path or a path relative to the backup directory of your SQL Server installation. As there is no complete path specified for the backup file in our example, the backup will be written to the default backup directory of your SQL Express instance.

The whole point of this article is to automate the backup process. So we need to wrap a bit of logic around this simple call, to use a unique file name each time we create a backup.

The file name we supply to the BACKUP Command is not just a direct file representation of the backup. It is a Backup Device as described in the previous article.

SQLCMD command line options

SQLCMD has a whole host of command line options. I’m going to explain only those options for SQLCMD we’re actually going to use here:

Be careful, case matters! Lower case letters refer to totally different options than the corresponding upper case letters.

A basic backup command

As we learned in the previous part of this series, you can create either a Full Database Backup or a Differential Backup of an SQL Server database. Both is done using the BACKUP command.

For a Full backup, you only have to supply the database name, the output device type (DISK or TAPE) and a name/path to output file. Here is an example:

(same as above)

If you already got a full backup in place and want to do just an additional, differential backup to save time and space, you would specify the WITH DIFFERENTIAL option to that command.

To execute this backup statement with SQLCMD you would use this command line:

"C:\path\to\SQLCMD.exe" ^
-Q "BACKUP DATABASE YourDatabase TO DISK = 'YourDatabaseBackupfile.bak' WITH DIFFERENTIAL;" ^
-E -S YourServer -b

(Note: ^ is the line continuation character in DOS. This is a single command line spread over multiple lines.)

You can create a new Task in Task Scheduler with a “Start Program”-Action and add the above command line as “Program/script”.

My backup script

The Task created above would do the basic backup. I’m not content yet, though. I would rather have some more logic in my script.

Before I show you the script I wrote, I would like to state the requirements for my database backup script.

1. I would like to have a daily full backup and additional differential backups every two hours.
2. All backups of any one day should be inside the same file (Backup Device) to allow for easy recovery and easy retention management. The date the backup was created should be included in the file name.
3. The backups should be checked for integrity after creation.
4. If the backup fails, an email should be sent to me.

I could put the logic to achieve all that inside a Batch- or Powershell script, but I’m much more comfortable with T-SQL. So I put all that except for the email into a T-SQL-Script.

So here is my script:

I guess I should explain some of the stuff going on in there.

The SET @VariableName  = $(VariableName); statements at the top are there to keep the script flexible. I can pass the variables from SQLCMD to the SQL script using the -v argument and hence use one script create different backup types for different databases to different output files with just one script file. The expressions $(VariableName) will be replaced by SQLCMD before executing the script.

Unless an explicit name for the backup file is supplied, I’m generating a file name from the database name, the current date and the backup type in the SET @OutputFileName = … statement. The AUTO type of backup will result in one full backup and subsequent differential backups being put into one backup file (backup device) per day.

To decide whether the script needs to do a full or a differential backup, I invoke RESTORE LABELONLY. The RESTORE LABELONLY … statement will try to read the contents from an existing backup file. If the file exists, there has to be a full backup in there already and a differential backup will be added to the backup device. If the file doesn’t exist, an error will occur and will be caught in the CATCH-Block. The script will then create a new backup file by doing a full backup.

After the new backup was written to the file, I use the RESTORE VERIFYONLY … statement to check the integrity of the backup file. This will raise an error if there is a problem with the backup file.

The command line to execute this script could look like this:

"C:\path\to\SQLCMD.exe" -E -S YourServer -b ^
-i "C:\path\to\BackupScript.sql" ^
-v BackupDB="'YourDb'" BackupType="'AUTO'" OuputDir="NULL" OutputFileName="NULL"

To use this command line in your environment, you need to adjust my dummy variable values to something valid on your computer. Copy this command line into the “Program/Script” entry box of a new Task Scheduler task. Then set an appropriate execution interval (every 2 hours in my case) and save the task. – Voila, you’ve got an automatic backup of your SQL Server Express database scheduled.

But wait! My final requirement is still missing.

Email on backup failures

I want to be sent an email if the backup fails. Usually, this would be a piece of cake. You would simply call the msdb.dbo.sp_send_dbmail-procedure of SQL Server and the email would be sent by Database Mail. – Unfortunately, Database Mail is another feature not available with SQL Express.

There are some crude hacks to be found on the internet explaining how to get Database Mail working in a SQL Express installation. But Database Mail is explicitly listed as not available in SQL Express in Microsoft’s edition/feature-matrix. So, I would consider it a license violation to enable it anyway.

In my scenario sending the email with the Powershell Send-MailMessage Cmdlet was the easiest alternative. I wrapped the Call of our Backup command line in a small (2 lines) cmd-script. The first line just invokes our backup on SQL Express, as above in the Task Scheduler command line, and the second line sends an email if the error level is 1 or higher.

@ECHO OFF

"C:\path\to\SQLCMD.exe" -E -S YourServer -b ^
-i "C:\path\to\BackupScript.sql" ^
-v BackupDB="'YourDb'" BackupType="'AUTO'" OuputDir="NULL" OutputFileName="NULL"

IF ERRORLEVEL 1 "%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe" ^
-command "& {Send-MailMessage ^
-SmtpServer 'mail.server.invalid' ^
-From 'backup@server.invalid' ^
-To 'admin@server.invalid' ^
-Subject 'Database backup failed' ^
-Priority 'High'}"

(Remember: ^ is the line continuation character in DOS. It works in scripts as well.)

So, finally we got a working solution satisfying all my requirement.

But, wait, there is more…

Using a stored procedure instead of a script

Well, the above solution works. There is nothing technically wrong with it. – Except… I hate it! Looking at the SQLCMD variables in the script that tie the script exclusively to SQLCMD with that interwoven dependencies makes me feel sick.

I prefer a slightly different solution. All the actual commands to back up the database remain the same. But, instead of putting them in a script, I rather create a stored procedure that invokes the backup.

Now, where to put this stored procedure? Like the script, the stored procedure can be used to backup any database on the server. Where do administrative, server-wide procedures reside usually? - Yes, you are right. In the master database.

Do not worry, it is perfectly OK to create your own procedures in the master database. – If they are administrative utility procedures. Do go overboard and create tons of stuff in master, but in some defined cases such as this one, it is ok. Keep in mind what you added to master, though. If you set up another SQL-Server elsewhere you need to add that stuff manually again. – Always check this is OK with the DBA responsible for that particular server. Some DBAs might have a strong, opposite opinion on this. If you put something into “their” master without permission, they might come after you with pitchforks and torches.

If you don’t have permission or don’t feel comfortable adding a proc to master, you could create a custom utility database to contain custom maintenance procedures such as this one.

Ok, back to the technical details. To create a procedure out of our script, we just add a procedure definition and incorporate the variables that were set from SQLCMD as regular parameters to the stored proc. Then we add return statements at the two exit locations of the proc. That’s it.

Here is our stored procedure:

The command line invoking the backup procedure can be simplified now. We just need to invoke the stored procedure.

"C:\path\to\SQLCMD.exe" -E -S YourServer -b ^
-Q "EXEC sp_BackupSQLExpress 'YourDB', 'AUTO'"

You should notice that I just invoke the procedure by its name, without any database or schema qualifier. Naming the proc sp_... inside master has another nice effect. We can call this proc from anywhere within that SQL Server instance and do not even need to specify the database or the schema of the owner. The procedure name resolution will automatically look for a procedure beginning with sp_... in the master database.

If you put your version of this procedure in another database, you should reference it by its full name including database name and schema name.

Copy the backup to a safe location

Be aware there is one important step missing in this article. You need to keep a copy of your backups elsewhere (off-site). Otherwise, your whole backup process would have been in vain, in the case of catastrophic events, like the building burning down.

You should add a copy instruction to an external network share, a FTP-Site or something similar to your backup batch script to keep your backups safe. In my own backup implementation, I use a PowerShell script to upload the backups to Azure storage. There are lots of very different valid options, so this is beyond the scope of this article.

3rd party tools

After reading this far, you know how to create automatic backups by only using tools, which are available with every SQL Express installation. This is my preferred way of tackling this problem.

Still, it is good to know, there are several third-party tools available that provide a graphical user interface and scheduling features to create backups on SQL Express. There is a question on DBA-Stack-Exchange with some of those alternative Backup tools for SQL Express mentioned in the answers.

Restore – is coming next…

The original plan was to wrap this mini-series up with some general advice on the restore of your databases here at the end of second article. However, it just did not feel right to let you figure out the details yourself after giving some slightly bossy, general advice.

So, I decided to extend the series to a third article about how to restore your database (including the bossy advice).

Share this article: