Ever Since I wrote the blog post regarding how to make SQL Server backups go faster and better, I have been bombarded with several colleagues and professionals regarding backup issues and questions that they had in mind regarding SQL Server backups. Many of those questions were repetitive, and since there were a huge number of questions, I decided to post my answers to the most common and significant SQL Server issues in my blog. Oh, and if you want to check out my post regarding better and faster SQL Server backups, just follow the link given below:
Now, for today I shall address one of the most common questions regarding SQL Server backups:
Can I restore a backup onto a different version of SQL Server? And do I have to worry about any issues in doing so?
Well, I can explain this in many ways, but for the most part I’ll say, Yes. We can restore to a different version of SQL Server, but we can only restore upwards. Meaning, we can restore from 2000 to 2005, 2005 to 2008R2, 2008R2 to 2012, and so on, but we can never restore in the reverse order. Each version of SQL server makes modifications to the binaries of the database and its storage, even the metadata. Sad thing is, Microsoft doesn’t go back in time and re-write the previous versions to support these changes. And if we really need to restore in the reverse order, we have to try scripting out the schema and data.
In order to know which version of SQL Server a backup originated, we need to look at the header information of the backup file using a command as shown below:
RESTORE HEADERONLY FROM DISK = ‘d:\SQLBKUP\backup_001.bak’;
This command would return information that includes details of the major, minor and build version of the SQL Server instance for that particular backup file and this allows us to validate that we are attempting to restore an appropriate version.
Now for the second part of the question. When restoring a database to a newer version of SQL Server, it is possible to hit incompatibilities within the database. The safest approach is to run the Microsoft’s Upgrade Advisor on the database we wish to migrate, in order to ensure that it’s ready. and then take a backup and restore it to the new instance.
After the restore, the database will be running in the compatibility mode that it had prior to the upgrade. This means that it will be set to support the functionality of the version of SQL Server from which we migrated. In order to take full advantage of the new version of SQL Server, we will need to modify the compatibility level of our database. We can do this through the GUI, or by running the T-SQL as show below:
ALTER DATABASE Backup001 SET COMPATIBILITY_LEVEL = 110;
The different numbers of compatibility_level represent different version of SQL Server, 90 for 2005, 100 for 2008 and 2008R2, 110 for 2012, and so on.
Before ending I’d like to add that not all upgrades are possible. Microsoft only allows us to jump forward a maximum of two versions. For example, we cannot restore a SQL Server 2000 database backup to a 2012 instance. We would have to first restore it to 2008/2008R2, update the compatibility level, perform backup and then restore that backup to 2012.
That’s it for this time. I shall certainly tackle a new issue that is quite common to SQL Server backups. Until then, I’d like to hear from you all too. So, feel free to comment.