Backup SQL Server Express Databases - Fundamentals

by Philipp Stiefel, originally published January 10^th, 2017

Based on a photo by Christian Bardenhorst, used here under CC0 licensing

Microsoft SQL Server Express Edition is the free edition of Microsoft’s SQL Server. SQL Server and especially it’s 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.

Despite SQL Express being free, it actually has all the features available you usually need in a backend server for small and medium database projects. All, except one that is critical. It is missing the SQL Agent. SQL Agent is SQL Server’s scheduling component to run automatic tasks. Amongst other useful purposes, it is used to schedule automatic backups of your databases.

Backups are something you cannot go without. – I do not need to tell you that, I hope.

SQL Express does support the backup features of SQL Server. So, you can do manual backups in SQL Server Management Studio exactly the same way you would do with a “Full” Edition of SQL Server.

If you suddenly have to deal with backups on SQL Server because you upsized your database from Access to SQL Server, chances are, you do not have much previous knowledge about this topic. As backups are critical, I first digress into the theory and fundamentals of backups with SQL Server, including SQL Express, before I show you how to setup automatic backups with SQL Express.

The Backup dialog of SQL Server Management Studio has quite a few options, but as this article focuses on backing up a SQL Express database, I think we can get away with just looking at the simple options here.

When manually backing up your database you will most likely just do a full backup of a database.

For that you select the database in SQL Server Management Studio’s Object Explorer, open the context menu and select Backup… from the Tasks-sub-menu. In the dialog, you add a file by selecting the folder and typing the desired file name into the file name field and confirm the file selection. You can then just hit OK in the main backup dialog and SQL Server will back up your database to the selected file. Easy.

If you depend on the backup you are about to create, you should switch to the Media Options Tab in the backup dialog and activate the Verify backup when finished-option.

Important: If you are backing up a production database to get a copy for your development environment or for any other reason, that will have that backup file be moved elsewhere or deleted shortly after, you should check the Copy-only backup-checkbox. This way the backup created will not be used as base for any subsequent differential backups. For databases with recovery model set to Full the transaction log will not be truncated after the backup then. - I’m going to explain different backup types in a minute. It will become clear then why this is very important.

To prevent a misconception here, let me explain the concept of Backup Devices. The file we selected in the backup dialog, is not just a direct file representation of the backup. It is a Backup Device and it can contain any number of backups of a database. Selecting the same file name again in another backup operation using default settings will place an additional backup in that backup file (backup device). 

A very nice feature coming with backup devices is the backup timeline that is displayed upon restoring a database from such a backup device. If you got several backups in one file, you can choose the state of the database you want to restore with the graphical timeline in SQL Server Management Studio. – This would be possible with individual backup files as well, but I would require you to select the applicable file(s) from the file system.

On the other hand, writing all backups of a database to the same file (Backup Device) will create a problem over time, as a single file will grow pretty large and backup retention is hard to manage. It is advisable to rotate the backup file every once in a while. You should consider database (backup) size and the backup management process to decide on a suitable rotation interval.

How much data loss are you prepared to tolerate? – You might be quick to answer: “None!”

Sure, backups are extremely important, so it would be nice if we could backup everything all the time. You can aim for this, but to do this with reasonable reliability comes at a price.

Backups take up space. Yes, storage space is pretty cheap nowadays and the database size of a SQL Express database is limited to 10 GB anyway. So, this is probably not going to be the primary constraint. Keep in mind though that you might need to keep more than a couple of days’ worth of backups around to be protected against every scenario of data loss.

The backup process degrades performance. Creating a database backup takes quite a substantial amount of disk, and to a lesser extent, memory and CPU resources away from SQL Server’s normal processing power.  This reduction in performance might very well be noticeable by your users and it might take a couple of minutes to back up a big database. – Remember, we’re still within the limits of SQL Express here. Backing up a terabyte size database will take considerably longer.

Moving the backups elsewhere uses network bandwidth. Backups that remain on the same computer, in the same server room, or in the same building are still at risk. To be prepared for scenarios such as a fire in the building, you need to store (a copy of) your backups elsewhere.

Now, let me ask again: How much data loss are you prepared to tolerate?

There are several different types of backups you can create with SQL Server. You should know at least the basics of the most common types.

Full database backups

As the name suggests, Full database backups include everything required to restore a database from scratch. They are easy to understand and easy to handle. Their disadvantage is the considerable file size and the considerable processing resources it takes to create them for a huge database.

Differential backups

Differential backups just contain all the changes to your database since the previous full backup. To restore a database to the latest differential backup, you need the most recent full backup and the differential backup you want to restore. That might be quite a few backups you need to sift through to get your database back into shape, unless you simply want to restore to the most recent state of the database.

On the plus side, Differential Backups can be created much quicker and require only a fraction of the storage space. It puts less burden on your server to create them and you can move or copy them elsewhere more easily. Hence, you can create these backups more frequently than full backups without much negative effects.

The fact that differential backups can be done much more frequently, obviously reduces the time between backups. However, it does not change the fact that you are going to lose all changes to the data after the most recent backup if disaster really strikes.

Transaction log backups

If you are actually aiming for zero data loss in an event of a disaster, you need to back up the SQL Server transaction log as well. Unless the tail of the log files gets lost in the disaster event, backup strategy allows for a point-in-time-recovery up to exact point of failure.

The drawback are much bigger transaction log files, as their space cannot be reused until the log is backed up. Transaction log backups require the Recovery Model of your database to be set to Full.

I’m not going deal with transaction log backups in any more detail here. I think you wouldn’t be using SQL Express if you are going to need them.

We’ve covered the fundamentals of SQL Server Backups as far as I think anyone dealing with them should be aware of. There are more topics, like Partial Backups and Media Sets, you should know if you are responsible for huge database on SQL Server. However, that is beyond the scope of a small SQL Express database.

Now we are ready to actually implement an automatic Backup strategy for our SQL Express database. I explain how to do this without SQL Agent in the second part of this series.

Share this article: