SQL Server 2014 Backup Basics

There is nothing mysterious about SQL Server backups. They are essential, however you use your databases. Grant Fritchey explains the basics of database backups and restores with SQL Server 2014.

Getting SQL Server backups right is the single most important thing that a data professional can do for the business they work for. Inappropriate, missing, or untested backups can actually cause the entire business to fail. That issue makes backups a fundamental business problem, not a technical one. However, the solution to the business problem is through technical means, the various backup processes offered by SQL Server 2014.


A backup is nothing more than a copy that is created as a type of insurance policy in the event that the original goes away. The same applies to SQL Server database backups to an extent, but database backups are not simply a file copy. They are a very specific type of copy that is aware of the transactional nature of SQL Server. This copy will be created in such a way as to deal with transactions that are ‘in flight,’ that have not yet been completed. Simply copying the files that define a database will not deal with transactions and can lead to serious data corruption. For this reason, you should, in most circumstances, use the native backup processes or, third party tools that work directly with the native processes such as Red Gate SQL Backup. There are some large scale systems that will need to work with non-standard backup mechanisms such as SAN snapshots. These are far outside the scope of this article.

Most of the fundamental backup mechanisms and some of the more advanced ones are covered in this article. However, database backups present a very complex topic. This article will ensure that you have the information you need to get backups in place on your systems but there’s even more to learn about the processes within SQL Server that underlie the backup.

In order to get started with SQL Server backups, you first need to first understand the recovery models offered by SQL Server.

Recovery Models

All backups within SQL Server are affected by transactions and transaction log management. The methods of transaction log management are controlled by the database Recovery Models. There are three:

Simple: All transactions are written to the log. After a checkpoint, all committed transactions are automatically removed from the log, also known as truncating the log. No point-in-time recovery is possible.

Bulk-logged: All transactions are written to the log except certain types of minimally logged operations such as BULK INSERT and INSERT INTO. The log can only be truncated with a log backup operation. Point-in-time recovery is possible as long as no minimally logged operations are in any of the required log backups.

Full: All transactions are written to the log. The log can only be truncated with a log backup operation. Point-in-time recovery is possible.

While the Bulk-logged Recovery Model can somewhat reduce the size of logs, the limitations it places on point-in-time recovery make it less viable for most databases on most systems. The overriding majority of people use either the Simple or Full-Recovery Model. I’m going to focus on the two primary models within this article.

It is the requirements of the business that determine whether you need the Full or Simple Recovery model. You may need to work with the business to define the Recovery Point Objective (RPO). The RPO more or less defines the amount of information that the business could tolerate losing. Within Simple Recovery, all the data since the last Full or Differential backup will be lost. This is because the log is truncated after each checkpoint (an automated process that cleans up memory and transactions within SQL Server). Within Full Recovery, it is only the data in the system since the last Log backup that is lost. It may even be possible to  retrieve that data by running what is known as a tail-log backup (more on that in the Restore to a Point-in-time section below).

So, the first determination has to be what RPO will you be shooting for within the database in question. If it’s a secondary system with data that is not that important, Simple Recovery can make your backups and log management much easier. But you must remember that you surrender the ability to restore to a point-in-time. For most production systems, where the data is vital for the business, you should plan on Full-Recovery along with the necessary scheduled log backups.

Backups under Simple Recovery

To begin working with a database in Simple Recovery we need to first set it. I’m using AdventureWorks2012, the Microsoft supplied sample database (available here). To make sure it is in Simple Recovery I can run this T-SQL statement:

That will place the database into Simple Recovery and this database no longer has the ability to be restored to a point-in-time.


To get started with a backup you can issue the following T-SQL command:

That will create a page-by-page copy of the complete database, including any uncommitted transactions. Uncommitted transactions are included in the backup in order to support a process called recovery. Recovery  happens during a restore (more details on that in the restore section below). The backup is called a Full backup because it is all of the database, including every bit of data, all structures and code, even the database security information. A file is created at the location specified. It’s better to backup to file and then copy that file to other storage locations such as an off-site location or secondary media such as tape (if anyone is still using tape) because it makes the backup process faster and safer. You also have the ability to do a faster restore because you have the backup stored on a disk.

I’m using T-SQL instead of the graphical user interface for two reasons. First, you’ll understand better exactly how backups work by learning from T-SQL since it pares everything down to the bare essential commands and doesn’t have all sorts of clutter like the GUI. Second, when you start building out your backup processes on your systems, you’re going to need to automate and control these, and you get much better options for automation and control through T-SQL, so best to learn it right from the start.


If you are using Simple Recovery, you can then take regular backups.  If, for example, you back up every week, you are, however, still at risk of losing up to a week’s worth of data. To avoid this, you can take additional differential backups between the full ones.  A differential backup is a copy of all pages within the database that have been modified, in any way, since the last full backup, plus any uncommitted transactions for recovery. The most important part of that last sentence are the five words, ‘since the last full backup.’ Let me explain that. Assume that we take a full backup on Sunday evening. Then, on Monday evening we take a differential backup. The differential backup will contain all the pages that have been modified since Sunday, one day’s worth. If, on Tuesday evening, we take another differential backup, that will contain all the pages modified since Sunday, two day’s worth. And so it goes.

Differential backups are handy because they make for smaller, faster backups. This means you’re putting less load on the system during the backup process. But, they can add to the time it takes to restore a database, which will  impact your Recovery Time Objective (RTO). That is the amount of time it takes you to restore your databases. That number goes up as you add additional work, such as restoring a differential backup or restoring log backups on top of restoring the full backup. RTO is something you have to take into account when working out your backup strategy with the business.

The T-SQL syntax to get a differential looks like this:

You’ll note that the syntax looks almost identical to the backup syntax except for the change in the file name, and the addition of the WITH DIFFERENTIAL clause. There are a number of additional options available in the WITH clause. We’ll only cover a few in this article. For more detail, I strongly suggest using the Books Online.


Differential backups come with a potential snag.  They represent the difference between the current database and what it was when the last full backup was taken. Imagine that you have a scheduled backup regime where you take a full backup, followed by differentials over a couple of weeks.  Obviously, to do a restore, you need that backup and the latest differential.  However, Tim, your rather sketchy colleague, takes another backup in the meantime in order to copy the database for a test. Having done so, he then deletes the backup since he doesn’t need it any longer. Then, your Database crashes and burns. You do the full restore successfully, but then your differential doesn’t bring you up to date because it represents the difference from Tim’s deleted backup. Instead, you get an ugly-looking error. So, a special command is needed to ensure that you can take extra ad-hoc backups safely:

Using the COPY_ONLY statement will ensure that this full backup doesn’t interfere with subsequent differential backups.

Backups under Full Recovery

In order to recover to a point-in-time, you must be able to perform log backups. This means having a database in Full Recovery:

Now, I not only can take log backups of this database, but I need to take log backups of this database.


Nothing really changes for a full backup when the database is in Full Recovery. The syntax and results are identical. The same thing goes for differential backups. They don’t change what they do or how they do it. You just have to worry about the addition of the log backups.


With a database in Full Recovery, if you don’t run log backups on a scheduled basis, your log file will grow. It will continue to grow until it fills the drive (or drives) that you’ve placed it on. You must run regular log backups. The syntax is shockingly simple:

This will backup the log of the AdventureWorks2012 database. Note, the only real change in syntax is going from the word DATABASE to LOG. That’s really all it takes. This will copy all the transactions in the log, committed or not. Then, it will truncate the log, which means that  all committed transactions are removed from the log. That will free space within the log for more transactions. But, note, it will absolutely not reduce the size of the log file. It just makes room for more transactions.

We need to introduce one more concept to our backups at this point. Up to now, all the examples have shown how to do one backup. But, if you are going to set up log backups, you will be doing lots of them. A good starting point would be to do log backups every 30 minutes. That’s 48 log backups during one day. If you ran the T-SQL statement above 47 more times, you would have only one file named AdventureWorks_Log.bak on the D: drive. This is because SQL Server will append backups into a file, more or less stacking them together. You can control this behavior by choosing how you want to control your backups. You can do everything to one file, or, you can go to multiple files. But, if you ever attempt to perform a backup to an existing backup file name, SQL Server will stack that backup within that file. To stop this behavior, you can choose to modify your syntax like this:

The INIT key word tells the backup process to throw away any existing backups and initialize the file with this new backup you’re running. It’s worth noting though, if you were to run this command 47 more times, you would still only have a single file, but it would only have the last log backup. That’s potentially a career-changing problem. UPDATE: Per Sean McCown, it’s always a best practice to include FORMAT with INIT in order to avoid corruption of the header preventing a successful backup. Thanks Sean.

In order to restore to a point in time, you must have all the log backups from the point of recovery, meaning the restore of a full or differential backup, in a continuous chain. You can restore log backups for a given database against any full backup of that database as long as it falls within the time range of those log backups (making log restores completely different than differential restores). However, if there is a  break in the log chain, then all the log backups after that break are useless: You’ve lost that data.

You have two alternatives:  You can either stack the backups into a single file or have them all as individual files. Your choice depends on how you wish to manage the process. You can have a single file, which makes file management easy, but knowing what’s in it is more difficult (there are queries you can run, we’ll cover them in the Restore section) or you can know what’s in the backup because you name it carefully, but you’ll have lots more backup files to deal with. I’ve worked both ways and both have drawbacks. In general, I choose to keep full and differential backups as unique files and then stack the log backups into a single file.

You can schedule  your log backups with  any kind of scheduling tool that can make the necessary T-SQL call, but the easiest way to do it is to use the built-in service SQL Agent.

File and FileGroup Backups

For very large databases (and by that, I mean, at least 500gb, but more like 5-10tb or more), it can become too expensive to regularly run a straight full backup . So, where needed, you can choose to backup smaller pieces of the database by choosing to back up one of the files or file groups that make up a database. But before you start backing up files and filegroups you need to think about the restore process. The reason this was put into the Full-Recovery section is because, under Simple Recovery, you can only ever restore a READ ONLY file or filegroup. You can restore a file or filegroup under Full Recovery, independently from the rest of the database restore, which we’ll talk about next, but you must have a full chain of log backups in order to restore a read/write file.

To illustrate this, I’ll add a filegroup and a file to my AdventureWorks2012 database:

With that in place, I can then choose to backup just a filegroup:

I could now restore this filegroup independently of the rest of the database. For more on recovery from file, filegroup and partial backups, read this article by Sean McGeehee.

Restoring a Database

A vital part of protecting your business is getting backups in place. But backups, as we’ve seen, are just files. A restore means that you have a database again. You can only restore  what you’ve previously backed up and in the same way that it was backed up. By this I mean that if you have a full backup, and that’s all, you can’t do a differential or a log restore. Further, you can’t selectively restore bits and pieces of that backup. For example, someone dropped a stored procedure and you want it back. You can’t go to your backup and restore just that procedure. You’d need a tool like SQL Compare to do that.

In order to restore a database, there cannot be any connections in place. The processes within SQL Server that perform the restore must have exclusive access. You’ll need to have everyone disconnect from the database, including yourself. Once the system has access, the restore is very simple:

But if I run that with the current state of my database, Full Recovery, I’m likely to see this error:

That means that I need to run a special type of log backup, tail log, which I’ll cover in the next section. I can choose to ignore this error and force the restore by simply modifying my restore command slightly:

That will result in a database restored to the point-in-time when I took my first backup. You could also choose to run a restore to a different database name entirely, but, because a database is a set of files, you have to take this into account to avoid generating errors and add an additional clause, WITH MOVE:

The MOVE statement uses the logical file names to move them to new physical locations. This is also the first time I’ve shown that you can have multiple WITH clauses. Just separate them by commas as you can see with the two MOVE statements, one for each file in the database. In the event that you don’t know what files make up a database, you can use another command to retrieve this information from the backup file itself:

This will show the logical file names and the physical locations for the database that was backed up. You’ll also see a bunch of additional information about the files that define that database.

Restore to a Point-in-time

If you don’t need to ever recover data up to the latest minute, you can just work with full and differential backups and never have to worry about log backups. But, if you want to restore to as close as possible to a particular point-in-time because of an error or some other reason, you’ll need to use log backups. To set up this test, we’ll do run the following script:

  • ensures my database is in Full Recovery
  • does a full backup
  • then does an edit, modifying data
  • then does a log backup to capture that transaction
  • then waits two minutes
  • does another modification
  • finally we’ll take a another log backup:

Notice that my second UPDATE statement is a huge mistake. It doesn’t have a WHERE clause, so it has modified all the Sales.Customer rows. I don’t want that, so I’m going to do a point-in-time restore. First, I need to restore the database, but, leave off the last step of a full restore, the recovery, where it rolls forward or back any open transactions from the time of the backup:

I went ahead and backed up the log one more time, just as a precaution. Because of that, I didn’t have to use the WITH REPLACE option, but, again, as a precaution  in case another transaction came through before my RESTORE operation runs, I put it in. You can see the status of a database by running this query:

This database is still not back online.

Now I need to restore my logs to get to the point-in-time just before the mistake was made. All the log backups that were taken went to a single file, so I’ll need to identify which backup I’m referring to within that file when I run the process. Here’s how it will work. First, I have to start with the logs that are applicable to my full backup and the point-in-time I’m interested in, so, I’ll take a look at the backups within the file:

In my case, this only returns two backups, but it could return any number depending on how you set up your backup processes. With the knowledge of the files I need to refer to, and the knowledge of the point-in-time I wish to stop at, just before the offending error, I can build a log restore like this:

I had to reference the file within the backup because I chose to stack them together this time. I also had to supply the STOPAT time for my log so that I don’t accidently run over and I left the database in a recovering state so I could apply more logs. Here’s the next statement:

I will continue restoring logs, incrementing the file, until I’m sure that I’ve hit the last log that will have the transactions ending at the time I have defined. Then I’ll stop. But, the database will still not be recovered, so I’ll have to do one more step:

That will put the database through its final recovery, bringing it online to a point-in-time.

Backup to URL

The novelty of SQL Server 2014 is that it allows you to use Azure Blob Storage in association with your on-premises databases. You can backup directly to a URL. This allows you to immediately have your backups in an offsite location as insurance against the loss of your facilities. The process is easy. First, you have to set up an Azure account and, on Azure, a blob storage account. Once set up, the rest is easy. You’ll need to get the Access Key from your storage account to then create a Credential within SQL Server:

You have to give the Credential a name, then you use your Storage Account name and the Access Key. With that in place, there’s just a slight modification to the backup command:

In addition to showing you how to use the URL in place of a disk and include the Credential, two other WITH statements that you can use on your backups are added. You can NAME backups to give them more descriptive designators to make it easier to understand why a backup was created or what it’s for. This is available to you when you run a RESTORE HEADER_ONLY command. You can also see how long a backup is taking by adding the STATS command and an increment measured in percentages. STATS = 10 will show as each 10% of the pages of the full backup are copied over, allowing you some idea of the status of the backup as it runs.

A restore is just the same thing in reverse:

Backing up to Azure storage is currently limited to files of 1TB or less. You’ll need to manage those files out on Azure storage too. We can help with Cerebrata Azure Management Studio.


Database Snapshots

A database snapshot is not really a backup as we consider it, but, it can be used to create a “copy” of your database and then restore from that copy, so it’s useful to know what a snapshot is and how it works. Snapshot backups were introduced in SQL Server 2005 and are available with Enterprise versions of SQL Server. When you create a snapshot, a read-only copy of your database is created by maintaining copies of pages of the database that are modified, but only those pages that are modified. In other words, before a page gets modified, it will be copied over to the snapshot. This makes the snapshot a constant copy of the database, a lot like a backup. But, it’s not really a backup, nor should this mechanism be used to substitute for backups under most circumstances. I would suggest only using it for occasions when you need a short-term backup of your database. For example, during a production upgrade, you could take a snapshot instead of a full backup to allow you to rollback from the changes you’re making to your database. This is because, once you have a snapshot in place, you can actually revert the database back to the state at which the snapshot was taken. Because it’s only moving around pages that have changed, taking a snapshot is radically faster than backing up a database and the same goes for restoring the snapshot.

The command required to create a snapshot is utterly different than the backup commands we’ve been running:

We’re actually creating a database. It’s a read only copy of your database. You need to make sure you have room to store the pages, which will grow as the underlying database is modified, but it’s very simple and fast.

You can ‘restore’ these too. You will need to make sure that you only have one, so if you create more than one snapshot on a database, you’ll need to remove all except the one you want to restore. Once that’s done, the process is incredibly simple:

That will restore the database, by working in more or less in a reverse fashion from the original creation of the snapshot, copying over the pages that were copied in the other direction.

Best Practices

There are any number of “best practices” for database backups. I’m only going to cover a few of the most important. First and foremost, and I do repeat this a lot, database backups are something the business should be defining, not technical people. Work with your business to identify the best methods for ensuring that your information is protected.

You should backup to a different disk to the one where you store your data and the operating system of your servers. This is to help to insure against a single failure point. That’s also why it’s a good idea to use off-site storage, maybe through backup to URL or through other processes. If you didn’t have to worry about disks failing, then you really wouldn’t need to do backups of your databases at all. But hardware does fail, so take that into account.

The vast majority of databases should be configured with Full Recovery in mind. This means you will need to set up log backups on a regular basis. The frequency really depends on your recovery requirements that you can work with the business to define.

As you’ve seen, it is pretty simple to get backups set up and running. There’s not much to it. But, a backup is nothing but a file that is stored somewhere. It’s useless unless you can restore it. The single best mechanism you have to ensure that you can restore it is to actually restore it.  By doing so, you not only test whether the backups worked, but also get practice in the process of restoring it when the time comes. Practice your restores frequently.

Finally, I used T-SQL in this article to show how to perform backups and restores. I did so because you should be using scripting for all your backups. That’s because scripting is going to allow you to exert maximum control over what gets backed up, how it gets backed up, where it gets backed up, etc. It’s also a good idea to come up with a naming convention for your backups so that it’s relatively easy to identify what that file represents. You can incorporate database names, server names and even dates and times into the name of the file. It’s up to you, but clarity is your friend in these situations. Scripting will help here too.

For even more on backup best practices, read through this article on 7 Preventable Backup Errors.


This was an overview of the capabilities of the backup processes for SQL Server 2014. Most of what was covered is largely applicable, as written, all the way back to SQL Server 2000. I’ve tried to identify when different functionality was introduced, but you can always refer to the Books Online for your version of SQL Server. Just remember, backups are primarily a business problem, not a technology one. To arrive at a good backup strategy, you must work with your business to ensure that you’re supplying adequate coverage for them. Don’t forget to practice restores as often as you can, especially if your backups are complex. Managing backups isn’t hard, but it’s vital that you get it right. Take your time. Think through your strategies. Mix and max the different technologies you have on hand in order to arrive at an optimal method for your business.