Category: Backups

John Deardurff explains what happens in the transaction log when you restore a backup:

In the example, the database performed a checkpoint at noon and a backup had been taken at that time. The restore process will capture all the transactions up until the point the database had been backed up. After the database has been restored, the recovery process will roll forward transactions 2 and 4 because they had been committed to the transaction log before the point of failure. Since transactions 3 and 5 did not commit before the time of system failure, the undo process will roll back the transactions to keep the data in a consistent state.

Read the whole thing.

Aaron Bertrand covers reasons for backups, backup models, and also a vital part of the backup process:

Now, all of the above may be review for you, but a much more important part of this story is that you need to be TESTING your backups. I’ve seen many customers who have been happily taking backups and storing them on some drive somewhere, and then when disaster strikes and they actually need to restore them, they can’t – maybe they had been backing up corruption all along, or the backups were failing but they were ignoring alerts, or they weren’t taking log backups frequently enough to meet their RPO, or they were only taking full backups.

Testing backups is vital; just because the backup process reported success doesn’t mean that you’ll necessarily be able to restore that backup when the time comes that you need it.  It’s also good to drill people on restoration skills, as things get a bit more stressful when three levels of management are standing behind your chair asking you what’s taking so long.

Derik Hammer shows one of the most useful features of database restores:

In order to restore point-in-time, you need to restore the full backup with NORECOVERY. This tells SQL Server not to initiate crash recovery which is a process that performs the redo and undo operations on your database to roll back the uncommitted transactions and roll forward the committed ones.

Once the full backup is restored you will need to restore the rest of the LSN (log sequence number) chain in the appropriate order. If you are not using differential backups, this means that you need to restore each log file until you cover the point-in-time that you are targeting. If you do have one or more differential backups, just restore the most recent differential which was taken before your target point-in-time and then all log backups between then and the target.

Derik also discusses restoring to marked transactions, something I’ve never used before but which could be very useful for known, major changes (like database code rollouts).

Robert Davis explains the NORECOVERY option when running a backup (rather than restore) command:

NORECOVERY applies only to log backups. When you run a log backup with NORECOVERY it takes the normal log backup and it also puts the database into a restoring state. This means that absolutely no transactions can run in the at database that isn’t covered by the log backup. At least not until someone recovered it. So is a scenario coming clear where that might be helpful? Think migrations.

Read on for more details.  It’s not something you’ll probably do frequently, but knowing it may make a future operation smoother.

Steve Jones explains that you can restore most backups to a later version, but can never restore to an earlier major version:

I would hope that most people know that a SQL Server database backup has a version. This version corresponds to a version of SQL Server, and for the most part, we can’t restore a database backup to an earlier version of SQL Server. Some exceptions might be a similar CU version there the database format hasn’t changed, but certainly not to any prior Service Pack.

However, can you restore to a later version? Can I take a SQL Server 2012 database backup and restore it to a SQL Server 2016 instance? Sure I can. In fact, lots of people upgrade their systems this way. Install a new SQL Server instance, take a backup on the old version and bring it forward. In fact, you can restore (or attach) a SQL Server 2005 database backup on SQL Server 2016.

Read on for more details and caveats.

Thomas Rushton advocates for enabling backup compression:

SQL Server backup compression – does what it says on the tin. Instead of SQL Server taking a backup by reading pages / extents of data from the database file and writing them out to the backup file, it compresses the data before it writes. It’s not the best compression you’ll get, as it won’t read the entire file before compressing it; however, it’s good enough that on OLTP databases that contain normal varchar / numeric data you could see savings of over 75% – indeed, I have some databases that save 90% of disk space. And that’s made my storage guys happy. Well, less unhappy.

One side note:  if you’re also encrypting backups (available in the product since SQL Server 2014), encryption happens after compression, so you can take advantage of both.

Dave Mason looks at alternatives to restoring databases:

Database Snapshots

A snapshot creates a read-only static view of a source database. With a snapshot, DML statements can be run on the source database and the snapshot database will preserve the original data. The snapshot can be used to “undo” data changes in the source database. There’d likely be more T-SQL/scripting work involved than a simple database restore. However, a snapshot has less “overhead” than a backup (at first). As noted in the MSDN documentation, “As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.” In addition, there are prerequisites for and limitations on database snapshots.

Restoring a database backup should be easy, but it might also tell you that there was a failure somewhere.  If you’re regularly restoring backups because of data entry issues, then it might make sense to keep a history of the data so you have tools to fix issues short of the nuclear option.

Kenneth Fisher crashes his own T-SQL Tuesday party:

This has led to the belief (or at least I believe this is one of the causes) that you can only restore a single differential backup. And up until the last few weeks I’d believed that myself. So, to set up a fairly simple test. I’m going to take a backup, create a table, and make some changes with differential backups in between the changes.

Check it out.  I don’t really see a good case for restoring multiple differentials (because each differential has the complete set of changes since the last full backup, so the differential at X+t is a proper subset of X+2t if your goal is to restore back to X+2t), but having multiple differentials, absolutely.