SQL Backups

SQL Backups are their own beast that behave differently than file system backups. But with proper care, these little beasties can be tamed to become invaluable allies instead of unpredictable, dangerous, wild animals.

Hopefully that intro put a smile on your face. I hate writing boring articles. SQL Backups are a straight-forward thing to implement, but you need to understand a few things about SQL, the types of backups available, and have some information about your company’s policies so that you can set the backups properly. Fear not! I’ll walk you through the steps….including the most important last step — restoring!

Company Policies

The first thing you need to know are what are you company policies about data loss, data retention, recovery time, and storage. These answers drive your backup policies.

Data Loss

How much data can your company afford to lose if you have to restore? 15 minutes enough? 30 minutes? A day? Another way to think of this is: Once we restore, how much data has to be entered by hand? This is something specific to your environment and may change over time.

Recovery Time

How fast do you need to recover in the case of a restore? Immediately? An hour? More? When there is an issue, you need to prioritize what gets restored. Your development systems can probably wait a while, but you want your production environments up as quickly as possible. This also affects where you store your backups.

Data Retention

How long do you need to keep backups around? Along those lines, how valuable are your old backups? If you restore to last week, can the company really use that information, or will too much be missing that it would be worthless? Your different environments might have different needs. The development environment may be changing constantly and having older backups would be useful so you could see what was in place last month. For production, restoring to last week would be pointless as there is too much data changed since that backup.

Storage

How much space do you have to store your backups? Usually someone high up the food chain thinks it would be great to keep a year’s worth of data on premises….until they see how much storage is needed. Although disks are cheap, they aren’t free. The quick answer is storing things offsite or on slower media. That choice impacts your recovery time. If you must pull a backup from the cloud, how fast does that backup come down? How long until the backup is usable? Even if your vendor supports quick speeds, if your location has a slow internet connection, then you are the bottleneck for the restore time.

Recovery Model

SQL offers three recovery models for each database: Simple, Full, and Bulk-logged. This is set in the database properties window.

In the Simple recovery model, data is written to the data file as transactions complete. The transaction log is not used as part of the backup. The only backup that can be done to this recovery model is a Full backup. Per Microsoft, “The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable.”

The Full recovery model does make use of transactions logs. When you do backups, you need to back up the entire database and then back up the transaction logs. We’ll get more into this when we talk about transaction log backups below. The important thing that Full recovery model allows us to do is restore to a point in time. That means if Bob deleted all our customers at 10:30pm, we could restore to exactly 10:29:59pm and our customers will be back! This is great for handling issues like someone deleting or changing a large amount of data.

The final recovery model, Bulk-logged, is typically only used when loading large amounts of data for a short amount of time. Transactions are still logged, but bulk operations are minimally logged. An example of a bulk operation would be uploading one million customers into the database. Logging each of those inserts would significantly slow the system down. Instead, we do a full backup, bulk upload our data, then do another full backup. That way we have a copy of the database before the upload, and one when all the information is in place.

The recovery model is set per database. You may have your production database as Full, and your development as Simple. That’s OK. If you are meeting your goals and policies, it’s fine to have different databases with different recovery models.

Types of Backups

There are three types of backups natively supported by SQL Management Studio (SSMS): Full, Transaction Log, and Differential. Each of these has different impacts on your backup and restore strategy.

Full backups back up the entire database. This is the one type of backup where only a single file can be used to restore the database. A Full backup does not back up the transaction log. That is a separate operation.

A Differential backup contains the changes since the last full backup. Without going into details, you can think that this backup stores all the “bits” that have changed since the last full backup. There’s a lot more that goes on, but this will suffice for what we need. Every time a differential backup is performed, it has all the changes since the last full. This includes the information that is in previous differential backups. Since this backup has only the changes, it is typically smaller than the Full backup.

Transaction Log backups are often referred to as incremental backups. These backups have the changes since the last full or transaction log backup, whichever is latest. When a transaction log backup is performed a “marker” is written to the log to indicate the last transaction that was backed up. SQL can re-use the space that was backed up. Doing the transaction log backup is what keeps the log file from growing out of control. A full backup does not affect the transaction log. That’s why it’s important to perform transaction log backups on a regular basis. These backups are typically quick, and files are small.

When a differential or transaction backup is written, a marker is written to the backup file indicating what Full backup it goes with, and what sequence this file is since the full backup. Ok, there’s a lot more to it than that, but I’m trying to keep it easy to follow. Basically, your full backup and the subsequent differential and transaction logs make a “chain” of backup files. When you restore, you can’t skip a link in the chain. If you’re missing the 2^nd transaction log, you can’t jump to the 3^rd log and complete the restore. You must have all the files in sequence.

Your decision above from Data Loss (How much data can I lose) affects the frequency of the differential or transaction logs. If you can only lose 15 minutes of data, then you should be running a differential or transaction log backup at least every 15 minutes.

Ok, there is sort of a fourth type of backup: Copy Only backups. These are exactly what their name implies: copies. For example, if you want to copy your production system into a test environment, you can do a Full, Copy Only backup of production and restore it to test. Copy Only backups do not write “markers” to the database to indicate a backup happened. This way they don’t interfere with your chain of differential and transaction log backups.

Restoring

Everyone talks about backup strategies, but the backups are worthless if you don’t know how to restore and understand how the choices you made for backups affect your restoration process. That item above for Recovery Time greatly affects how we should do backups so that we have things to make the restore match that expectation. Let’s go through how to recover based on the types of backups above. Then you can better decide what backup types are needed for your specific databases and policies.

Regardless of the strategy, the first thing you do is always restore the full backup. What you do after that step changes based on other choices. Ok, the first thing you do is pull out the manual you wrote for how to do the restores for your specific environment. What? You are writing a manual after reading this article, right?

Full Backup Only

Let’s take the easy case first: our database is in Simple Recovery Model. For this, the only thing you can do is restore a full backup. If we do a full backup every night at midnight and a twister comes by and knocks out our office on Tuesday at 7pm, we’ll restore Tuesday’s full back up from midnight. Simple. Easy. Quick. Keep in mind, we lost everything from midnight to 7pm. That may be acceptable, maybe not. The Data Loss choice you made above affects if it is acceptable or not.

Transaction Log Backup

Now let’s make things a little more complex by changing our recovery model to Full and adding Transaction Log backups. In general, you’ll restore the full backup and then apply the transaction logs since the Full backup. Let’s say we’re doing a Full Backup every night at midnight and transaction log backups multiple times during the day. Disaster happens after the third transaction log backup for the day. To recover, we’ll restore the full backup, and then each of the transaction logs in order (T1, T2, T3 from our image below). We only lost the information entered after T3.

That Data Loss choice affects our frequency of transaction log backups. If we can only lose 10 minutes of data, then we need to back up our transaction log at least every 10 minutes. With transaction logs, remember that we can recover to a point in time. We don’t have to take every transaction in the log, instead we can tell SQL specifically when to quit applying transactions.

It is very important that you apply the transaction logs in order and there isn’t a missing transaction log. SQL provides an easy way to do this. If you use SSMS, when you select your backup files, SQL reads the files for the “markers” to put them in the proper order for restoring. This makes the process much easier to do and quicker than applying the files manually one by one.

Differential Backup with Transaction Log Backups

Differential backups are a little more complicated but can be very quick to restore. The pattern for this is to restore the full backup, the most recent differential, then apply the transaction logs since the most recent differential. We’re going to do a full backup on Sunday at midnight, differential backup every weekday night at midnight, and transaction log backups multiple times a day. Tuesday after our second log backup (T8 in the picture below) aliens come by and take our SQL server. Now what do we do? Obviously, you’ll want to get pictures of that spacecraft to send to the media, but we’re going to focus on getting our data back in this article.

First, restore your full backup from Sunday, then Tuesday’s differential, and then the transaction logs since that differential (T7 and T8). We don’t need Monday’s differential because Tuesday has Monday’s differential data. Differential backups also have data from the transaction log backups since the full or preceding differential. That’s why we don’t need the transaction log files other than T7 and T8. Once we’re done, we’ll have all the data back to T8’s backup.

How to Create Backups

Maintenance Plans

Maintenance plans are the easiest way to create your SQL backups. You can use the Maintenance Plans Wizard inside SSMS to quickly create your backups and schedule them. The Maintenance Plan tool will schedule the appropriate jobs inside SQL. A nice feature of these plans is having SQL remove old backup files (handling that Data Retention choice from above).

Manually

You can always create a backup at any time by right-clicking on a database in SSMS, choosing Tasks, and then select Backup. That will display a dialog to walk through the rest of the backup. This is an easy way to create a Copy Only backup for use in copying from one environment to another.

3^rd Party Software

There are several packages available that will perform backups for you. When using these package, you need to make sure:

• It will back up your SQL Database and not the MDF/LDF files. Restoring the MDF and LDF files directly can result in corrupt and unusable databases. The software should interface with SQL to perform the backup functions.
• The backup correctly handles transaction log backups. Many packages will just dump or clear the log file. That means you are just getting a full backup, and you might as well be using Simple recovery model. If you want to restore to a point in time, you need to make sure your package can support that.
• Do not implement maintenance plans having database backup steps. These will interfere with the package’s backups and potentially break that backup chain.
• Test both the backup and restore of the database using the package. If you don’t know how to do the restore, you might as well not have done the backup. In both cases, you won’t get your data back.

Closing

Regardless of your choices for database settings, backup strategies, and backup software, you need to test both the backup and the restore of your databases. If you don’t test, you won’t know that the backup was successful, and you won’t know the processes to restore. You don’t want to find out you have no processes when you need to recover from a disaster.

Regularly review your policies with stakeholders. Make sure their priorities and expectations haven’t changed. That includes integrating your backup (and restore) strategy with your Disaster Recovery plan (link to disaster blog?) and the team that handles such recovery. That team may be you, or you may be a member of that team. You want to make sure you know where your database fits in that disaster plan.

Run tests of your backup and restore plan and have the plan documented. When disaster strikes, you don’t want to waste brainpower trying to remember steps. Having a document to follow and check off ensures you won’t forget something or do steps out of order. Testing this plan can expose changes to your environment that weren’t communicated. Are there additional databases? Did a database get removed? Did the server change? Running the simulation will expose these issues and give you the opportunity to update your documentation so you are ready for when disaster strikes.

Keep that backup beast tamed. It’s very easy for it to run wild and get out of control, but with a little discipline, you’ll have that beast happy and purring like a little kitten.

Links

• Managed IT/ERP
• Whitepapers
• YouTube
• Disaster Planning
• SQL Training

- last updated February 4, 2025