It’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.