Save $3,500 by Creating Scheduled Backups Using SQL Server Express

SQL Server Standard Edition is a powerful database, but for many situations it is overkill.

Oftentimes, Access applications work perfectly well within the confines of SQL Server Express, including:

• 10 GB max database size
• No SQL Server Agent
• No SQL Profiler
• 1 socket/4 cores max CPU utilization
• ~1 GB available memory
• No Maintenance Plan Wizard

That last one means that you can't use a guided user interface to set up recurring backups in SQL Server Express.  If you can live with the other restrictions, though, you can save yourself thousands of dollars* without having to sacrifice your backups.

^{* SQL Server Standard Edition 2019 is currently $3,586 for a two-core license.}

I'll show you how to do that with a combination of Ola Hallengren's SQL Server maintenance scripts and the built-in Windows Task Scheduler.

Overview

Don't let the total number of steps discourage you.

I split up several steps that could easily be combined to make it easier to troubleshoot any problems that you encounter along the way.  You can do this!

Here are the steps I follow when setting up database backups on a new instance of SQL Server Express:

1. Create a database for testing
2. Run the Ola Hallengren backup as a query inside SSMS
3. Restore the backup on another device
4. Run the Ola Hallengren backup as a query from a cmd window
5. Create a scheduled task to run the osql command
6. Run the scheduled task to verify it works
7. Schedule it to run in the next 5 minutes
8. Log off during the scheduled run time
9. Log back on and confirm it ran
10. Schedule the task to run on its production schedule as appropriate
11. Come back in a week to ensure it is running
12. Drop the test database

For the rest of this article, I will break down the steps with screenshots and sample commands.  Some of these commands are specific to my situation, so you will need to adapt them to fit your individual needs.

Prerequisites

Before getting started, you will need the following:

• An installed instance of SQL Server Express
• SQL Server Management Studio
• The latest version of Ola Hallengren's SQL Server Maintenance Solution

Caveats and Disclaimers

Every situation is unique, including yours.

The steps demonstrated below were performed on a clean install of SQL Server Express (the best time to set up backups is before you start storing data in the first place; the second best time is now).  

These steps should be safe to perform in most live production environments, but obviously the stakes are raised in such situations.  You are ultimately responsible for your own data (or your client's data, as the case may be).  As with anything you find on the internet, you should be sure you understand what each step is doing before you do it.

With all that throat-clearing out of the way, let us proceed.

0. Install Ola's MaintenanceSolution.sql

1. Download MaintenanceSolution.sql from Ola's website
2. Open SSMS and connect to your instance of SQL Server Express
3. File > Open > File... ([Ctrl] + [O]) and open MaintenanceSolution.sql
4. Execute the script with [F5]

This step creates several stored procedures in the master database of your SQL Server Express instance.  By itself, this step does nothing to back up your data. But it makes everything else much easier.

1. Create a database for testing

1. In SSMS, right-click Databases > New Database...
   • Database name: TestOlaBackup_Simple
2. Click Script > Script action to new query window
3. Click [Cancel]
4. Execute the script with [F5]

This step is optional if you already have one or more non-system databases in your SQL Server instance.

2. Run the Ola Hallengren backup as a query inside SSMS

We execute the script in SSMS first because that's where it's easiest to troubleshoot.

1. In SSMS, expand Databases > System Databases
2. Right-click master and choose New Query
3. Refer to Ola's documentation to create an appropriate backup command
   • You'll need to create the destination backup folder if it does not exist
4. Copy and paste the T-SQL into the new query window
5. Execute with [F5]

Here's the sample script I used for this step:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'D:\SqlBackup',  -- you'll likely need to change this
@BackupType = 'FULL',
@CleanupTime = 60  -- keep three days worth of backups

Here's what it looks like in SSMS along with the backup file it created:

3. Restore the Backup on Another Device

Don't assume that just because you've created a backup that you can do something with it.  

The last time you want to be testing your restore process is after your original data has been lost.

1. In SSMS, right-click Databases > Restore Database...
2. Select Source: (•) Device
3. Click [...] next to the Device text box
4. Click [Add] and browse to the .bak file created in the previous step
5. Click [OK]
6. Click [OK] to restore the file

If you run into any trouble with the restore process, be sure to sort it out before moving on.  And then thank your lucky stars you took the time to test the process out now before you actually needed it.

4. Run the Ola Hallengren backup as a query from the cmd window

Since SQL Server Express does not include the SQL Agent (which lets you schedule tasks to run within the SQL Server instance), we'll need to rely on Windows Task Scheduler.

However, we don't want to be troubleshooting our command line statement and the scheduled task at the same time (Task Scheduler is finicky when it comes to quoting of command line statements).  This step is all about making sure the command line works the way we expect.

The whole theme of this article is to break down the process into small, incremental steps so that debugging any single error is dead simple because we are isolating every variable of the process.

Here's the command line I used:

osql -S . -d master -E -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'D:\SqlBackup', @BackupType = 'FULL', @CleanupTime = 60"

And here's what it looks like from the cmd window (along with the osql options, which I got by running osql /?):

5. Create a scheduled task to run the osql command

In the steps listed below, I create a "GB" folder to hold scheduled tasks that my company (Grandjean & Braverman) has created.  This is especially useful on client servers where other vendors may maintain their own scheduled tasks.

In the description, I include a URL to the associated case in our company's bug-tracking database, FogBugz.  I highly recommend including a link to whatever your equivalent documentation site is.

I set the task to run whether the user is logged on or not.  Since I am storing the backup on a local drive, I also checked the box to not store the password with the scheduled task (some corporate Group Policy settings won't allow storing the password).

To reduce reliance on the PATH environment variable, I used the full path to the osql.exe utility.

1. Open mmc.exe as Admin
2. [Ctrl] + [M] to add the Task Scheduler snap-in for Local computer
3. Add folder to "Task Scheduler Library" named GB
4. Right-click GB folder and choose "Create Task..."
   • General tab
     • Name: Backup SQL Express User Databases
     • Description: {URL to FogBugz case} https://grandjean.fogbugz.com/?10407
     • (•) Run whether user is logged on or not
     • [√] Do not store password
   • Triggers tab - [New...]
     • Begin the task: "On a schedule"
     • Start: {today} [11:23:00 PM]  (avoid starting on the hour to reduce resource peaks with other scheduled tasks)
     • (•) Weekly:
       • [  ] Sunday  [√] Monday   [√] Tuesday   [√] Wednesday
         [√] Thursday   [√] Friday   [  ] Saturday
     • [√] Enabled
   • Actions tab - [New...]
     • Program/script: {full path to osql.exe} - Note the enclosing double quotes:
       "C:\Program Files\Microsoft SQL Server\150\Tools\Binn\OSQL.EXE"
     • Add arguments:
       -S . -d master -E -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'D:\SqlBackup', @BackupType = 'FULL', @CleanupTime = 60"
   • Settings tab - (accept defaults)
     • [√] Allow task to be run on demand
     • [  ] Run as soon as possible after a scheduled start is missed
     • [  ] If the task fails, restart every:...
     • [√] Stop the task if it runs longer than "3 days"
     • [√] If it won't end, force it to stop
     • [  ] If it's not scheduled to run again, delete it after:...
     • If it's already running, then: "Do not start a new instance"
   • Click [OK] to save the task

6. Run the scheduled task to verify it works

1. Right-click the newly created task and choose Run
2. Check the output folder to ensure there is a new backup file with a current timestamp

7. Schedule it to run in the next five minutes

1. Double-click to edit the task
2. Go to the Triggers tab
3. Choose the Weekly trigger and click [Edit...]
4. Set the Start Time to sometime in the next few minutes
5. Click [OK] to save the changes to the trigger
6. Click [OK] to save the changes to the task

8. Log off during the scheduled run time

It's important to know that your backups will run even if you are logged off of the machine.

I temporarily scheduled my task to run at exactly 5:00 PM.

9. Log back on and confirm it ran

I logged back on at 5:01 PM and confirmed with both the "History" tab of the Scheduled task and the destination folder that the task had run successfully:

10. Schedule the task to run on its production schedule as appropriate

In my situation, I just want to run the backup at the end of each typical work day (Mon - Fri).  Be sure to set a trigger that makes the most sense for your situation.

1. Double-click to edit the task in Task Scheduler
2. Go to the Triggers tab
3. Choose the Weekly trigger and click [Edit...]
4. Set the Start Time to whenever you want the task to run (normally during off-peak hours)
5. Click [OK] to save the changes to the trigger
6. Click [OK] to save the changes to the task

11. Come back in a week to ensure it is running

This last step is important because it catches things that are difficult to test in realtime, such as:

• Automatic reboots following Windows Updates (especially problematic with Bitlocker Encryption on the O/S drive)
• Sleep settings on the SQL Server device
• Sleep settings on the destination folder's device
• Accumulated backups that aren't getting deleted properly
• Etc.

Be sure to check the time stamp on the backup files to be sure they're not the original ones from your testing.

12. Drop the test database

If you created a test database in Step 1, you can drop it now.

Final Notes

Be sure to take some time to explore the example backup commands over on Ola Hallengren's site.

Also, the @Databases parameter is very powerful:

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

The 'USER_DATABASES' value will automatically back up any new databases that you create on the SQL Server instance.  I like to combine that with the minus sign to exclude any specific databases that I don't want to back up for whatever reason.

Referenced articles

All Hail Ola!

If you’re managing SQL Server and you’re not using Ola Hallengren’s scripts, then you’re doing it wrong.

No Longer SetMike Wolfe

Image by Lisa Gielis from Pixabay