SQL Server Recovery Models and Backup Types

This is the fourth of a series of articles discussing backup and recovery for M2M businesses.

Once again, I’m giving you a very high level overview of these concepts and I am not going to cover every option available, only those which are important to companies that use M2M. If you want to dig deeper into today’s topic, and for that matter any topics regarding SQL Server administration, I recommend you read articles written by the great Paul Randal on his blog In Recovery, and this article he wrote for TechNet Magazine last year. I’ve “borrowed” liberally from many of his articles to write this one. However, be warned that he’s a genius and he sometimes explains topics in such detail that his articles can induce brain aneurysms. As with everything in database administration, when I am asked questions about backup and recovery my answer is “it depends.” To make things easier, I’m going to discuss backup and recovery with a basic SQL Server set up as an example.

Before we talk about recovery models and backup types, we need to briefly touch on the basic structure of a SQL database.

Every SQL Server database has at least two files, a data file and the transaction log. Basically, every change made to your database is first made in the transaction log and is then written to the data files later. The transaction log is a sequential list of transactions and their effect on your data.

For our example, we’re going to assume that your transaction log and database files are on separate volumes (not just named drives but on separate RAID arrays.). For the sake of simplicity I will call those Drives, though they represent a set of drives working together in an array. So, for our example the server has the transaction log on one drive and the data files on a second drive.

Backup Types

Full Backup

A full backup creates a complete image of the database, and the database can be restored from just one file. The backup can be restored either on the original server or another SQL Server entirely. Every Recovery Model includes the use of full backups. In the examples which follow we are going to take our full backups every night at 10pm, but when you actually schedule them is up to you.

Differential Backups

Differential backups can be used with all three Recovery Models and they can be very helpful if your database is large and takes a long time to backup. Differential backups only contain data which has been changed since the last full backup. It’s a bit more complicated than that, but as I said, this is a high level overview. Differential backups are not incremental, they are cumulative. Consider our example above where we are taking full backups every day at 10pm. If we took a differential backup every 6 hours, and the database was being updated continuously, then the differential backup at 4am would contain 6 hours worth of data. However, the 10am differential backup would contain 12 hours of database changes, not just an additional 6. There is a diminishing rate of returns with Differential Backups for this reason. Differential backups are particularly useful with the Full Recovery Model (more on that later).

Transaction Log Backups

Unlike differential backups, transaction log backups are incremental and are a record of all transactions against a database since the last transaction log backup. Transaction log backups are only necessary with the full and bulk logged recovery models.

Recovery Models

To explain the effect of each Recovery Model, let’s assume that we are taking a full backup of the database at 10pm every night, and we’ve had an issue at 4pm during a work day. The issue could be software related, user error, or a hardware failure that requires a restore from backup.

There are three recovery models for SQL Server, but we are only going to discuss two of them (simple and full) because the third is unlikely to apply to M2M companies.

Simple Recovery Model

In the Simple Recovery Model, the transaction log is cleared automatically as data is committed to the data files. There are some exceptions, but remember that this is a high level overview. This makes backup and restore easier because you do not have to worry about backing up the transaction log. However, with this model you can only recover to your last full or differential backup, there is no point-in-time restore.

So, your server fails at 4pm and you attempt to restore your backups. The best case scenario is 18 hours of productivity lost. For some businesses, this is acceptable. If this is an acceptable risk for your business, then feel free to use the Simple Recovery Model.

Full Recovery Model

In the Full Recovery Model the transaction log space is not cleared until it’s backed up. Once again, it’s a bit more complicated than that, but I’m trying to simplify concepts for this article. If the transaction log isn’t backed up, it simply grows until it either runs out of disk space (most likely option) or reaches the threshold set for its maximum size. M2M and other programs using SQL Databases become unusable until it’s corrected.

Using our example, let’s assume you were taking transaction log backups every fifteen minutes. Each transaction log backup is incremental, which means that each backup must be retained, and in the event of failure, must be applied in order which is called a log chain. Each chain must start with a full backup and continues until broken or until the last transaction in the log. If any one of those transaction log backups is bad, then the chain is broken and you cannot restore further than that.

If your database server loses the drive containing the database file, but the transaction log drive and the drive where the t-log backups are located are fine, you should be able to restore back to the moment of failure, and committed transactions would not be lost. If both your database file drive and transaction log drive failed, and your full backup and transaction backups were viable and on a separate server, then the most you would lose would be no more than fifteen minutes of work, which is acceptable to most M2M companies. Let’s go through the steps to recover in the above scenario. The admin would need to:

1. Create a “tail of the log” backup. This backs up any remaining transactions since your last transaction log backup.
2. Restore the last viable full backup.
3. Restore the entire log chain since that last full backup including the “tail of the log” backup. This requires restoring 72 (18 hours worth) of log files which is time consuming and tedious.

Differential backups can particularly important in the full recovery model. Remember that they are cumulative. This makes the restore path much shorter. For example, what would happen if we were doing differential backups every 2 hours and we lost the data drive at 4pm? Well, you would do the following:

1. Create a “tail of the log” backup. This backs up any remaining transactions since your last transaction log backup.
2. Restore the last viable full backup from last night.
3. Restore the last differential backup which was taken at 2pm
4. Restore 8 transaction logs to get you back to 4pm.

Differential backups restore much more quickly than parsing through the corresponding transaction logs and it’s a more straightforward process to boot. However, since most M2M databases are not very large, most companies I’ve dealt with do not use differential backups. In fact, in my experience the vast majority are in Simple Recovery Mode though my polls don’t reflect it.

Backup Location

The last important aspect of database backups is where to place them. Many experts I’ve talked to over the year have differing options, but my method is this. Create your backups in a local share on the server, but then immediately copy them to a network share, not a local drive. If your server dies, and your backup files were on it, you’ve got a serious problem. Save your backups on your file sharing server (or another server) so that if your database server dies, you can quickly restore to another.

In a future article I’ll discuss database file and backup corruption issues. Yes, I know that you were told that data corruption was a thing of the past when you migrated from Visual FoxPro to the SQL versions of M2M, but that is patently untrue. Stay tuned for details.