T-SQL Tuesday #85–Cross Version Restores

tsqltuesdayIt’s T-SQL Tuesday time, and this month Ken Fisher hosts. His topic is backups, and while I’m sure there will be plenty of views on backups, I wanted to touch base on a relatively simple item.

This is the monthly blog party started by Adam Machanic. Whether you want to write today or at some point in the future, this is a good chance to showcase your knowledge and test yourself on a topic.

Cross Version Restores

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.

There is a caveat, however. You cannot restore a backup of master, model, or msdb on a newer version. This is called out in the RESTORE command ( https://msdn.microsoft.com/en-us/library/ms186858.aspx).

That’s interesting, and it means a few things. First, if you really need to upgrade msdb, then you need to upgrade the instance. Does this mean you can’t do a side by side upgrade? No, because you could do this (for 2012 to 2016):

  • Install a new SQL Server 2012 instance.
  • Restore master and msdb.
  • Upgrade to SQL Server 2016.
  • Now attach or restore your SQL Server 2012 databases.

I wasn’t aware of this fact until browsing BOL. I had assumed I would be able to move msdb and model, though perhaps not master, to a new version.

That’s a handy piece of information to keep around, and it means that you should always be aware of those objects outside of user databases when you upgrade. Keep scripts handy in a VCS in case you need to create them on a newer version.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

One Response to T-SQL Tuesday #85–Cross Version Restores

  1. Pingback: A semester’s worth of Backup and Recovery blogs – The TSQL Tuesday 85 Rollup | SQL Studies

Comments are closed.