Category: Backups

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.

Kevin Hill has a Youtube video on the basics of backups:

You’ll want to go full screen to see what’s going on.  This video is targeted at non-DBAs, but give it a watch anyway…I can take the heckling!

If you want to keep down the video path, Sean McCown had a series of user group meetings on creating backups, syntax, tuning backups, restoring backups, etc.

Andy Mallon shows how to move your backups up to Azure Blob Storage:

Geographically-redundant storage, on a cool access tier currently costs about $0.02/GB. That’s a fraction of what it would cost you to have it on your NAS, let alone having multiple geographically redundant copies, and the effort to set up and maintain your copy to off-site.

And up to a certain size, it’s faster to retrieve the backups from Azure than calling the off-site storage company.

Rob Farley has a set of questions you should ask yourself regarding your backups:

Does your disaster testing include a situation where a well-meaning person has taken an extra backup, potentially spoiling differential or log backups?

Does your disaster testing include random scenarios where your team needs to figure out what’s going on and what needs to happen to get everything back?

Something which might be helpful would be to catalog the reason why you restored a particular backup (or when somebody asks you for a backup but you can’t do it), and then have a plan to handle that scenario in the future.