SQLskills SQL101: Restoring to an earlier version

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

One of the questions I get asked every so often is whether it’s possible to attach or restore a database to an earlier version of SQL Server. Usually the explanation behind the question is that the person accidentally attached the only copy of their database to a newer version than they wanted, or they were just trying out a pre-release version and now want to put their database back into their production system.

So is this possible? The very simple answer is: No.

SQL Server is down-level compatible but is not up-level compatible. This means you can take a database from an earlier version and attach/restore it to a newer version (I explained about this in a post here), but you can’t go backwards to an earlier version.

Why is this the case?

Upgrade steps

An upgrade, whether intentional or accidental, is a one-way operation and it is extremely difficult to reverse its effects. When you upgrade between versions of SQL Server, a series of upgrade steps are performed on the database. Each step usually involves some physical changes to the database, and each step increases the physical version number of the database.

For example, one of the major changes performed when a database was upgraded from SQL Server 2000 to SQL Server 2005 (yes, old and unsupported, but an easy-to-explain example) was to change the structure of the database’s system catalogs (often called the system tables or database metadata) that hold various metadata about tables, indexes, columns, allocations, and other details regarding the relational and physical structure of the database.

As each of these upgrade steps is performed, the database version number is increased. Here are some examples:

  • SQL Server 2016 databases have version number 852
  • SQL Server 2014 databases have version number 782
  • SQL Server 2012 databases have version number 706
  • SQL Server 2008 R2 databases have version number 661

This version number allows SQL Server to know the last upgrade step performed on the database, and whether the in-use SQL Server version can understand the database being attached/restored.

Here’s an example of restoring a SQL Server 2012 database to a SQL Server 2014 server:

RESTORE DATABASE [Company2012]
FROM DISK = N'D:\SQLskills\Company2012_Full.bak'
WITH REPLACE;
GO
Processed 280 pages for database 'Company', file 'Company' on file 1.
Processed 3 pages for database 'Company', file 'Company_log' on file 1.
Converting database 'Company' from version 706 to the current version 782.
Database 'Company' running the upgrade step from version 706 to version 770.
Database 'Company' running the upgrade step from version 770 to version 771.
Database 'Company' running the upgrade step from version 771 to version 772.
Database 'Company' running the upgrade step from version 772 to version 773.
Database 'Company' running the upgrade step from version 773 to version 774.
Database 'Company' running the upgrade step from version 774 to version 775.
Database 'Company' running the upgrade step from version 775 to version 776.
Database 'Company' running the upgrade step from version 776 to version 777.
Database 'Company' running the upgrade step from version 777 to version 778.
Database 'Company' running the upgrade step from version 778 to version 779.
Database 'Company' running the upgrade step from version 779 to version 780.
Database 'Company' running the upgrade step from version 780 to version 781.
Database 'Company' running the upgrade step from version 781 to version 782.
RESTORE DATABASE successfully processed 283 pages in 0.022 seconds (100.430 MB/sec).

Up-level compatibility (or lack thereof…)

Versions of SQL Server cannot read databases upgraded to more recent versions of SQL Server – for instance, SQL Server 2012 cannot read a database that’s been upgraded to SQL Server 2014. This is because older versions do not have the code needed to interpret the upgraded structures and database layout.

Here’s an example of trying to restore a SQL Server 2014 database to a SQL Server 2012 server:

RESTORE DATABASE [Company2014]
FROM DISK = N'D:\SQLskills\Company2014_Full.bak'
WITH REPLACE;
GO
Msg 3169, Level 16, State 1, Line 51
The database was backed up on a server running version 12.00.4422. That version is incompatible with this server, which is running version 11.00.5343. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 51
RESTORE DATABASE is terminating abnormally.

In earlier versions, the messages weren’t always quite as nice and easy to understand.

And some people confuse database compatibility level with the database version. Compatibility level has nothing to do with up-level compatibility – it just changes how some query processor features behave.

Summary

The simple thing to bear in mind is not to attach the only copy of your database to a newer version. It’s always better to restore a copy of a database, then you’ve still got the original database to fall back on, for whatever reason. This applies even if you’re deliberately performing an upgrade – I’d still want to keep the older copy of the database around in case some problem occurs with the upgrade.

If you *have* attached your only copy of the database to a newer version and want to go back to an earlier version, your only option is to script out the database structure, create the database again on the older version, and then transfer all the data from the newer version to the older version. Very tedious.

Hope you found this helpful!

PS There’s a comment below asking whether you can move back to an earlier SP or CU. Yes, for user databases, as long as the newer SP/CU didn’t change the physical version number (and none of them since 2005 SP2 and 2008 SP2 have done that).

8 thoughts on “SQLskills SQL101: Restoring to an earlier version

  1. Hi Paul. Quick question in relation to this, you’re talking about going between SQL versions. How about going between CUs or SPs? I’ve seen this work, at least I thought it worked, where I took a DB that had been upgraded to a CU (let’s say SP2 CU13), and then attached it to a SP2 CU11 server and watched it downgrade. I’ve been using a product called DxEnterprise for a while now, and it relies on that system to move from a newer patch to an older patch for fallback should things go sideways. What are your thoughts on that, is that a no-no, or normal behavior?

    Thanks!

    1. That should work fine for user databases, as long as the newer SP/CU doesn’t do something that bumps the physical version number. SP2 of 2005 and SP2 of 2008 both did this, but nothing since then. I’ll put this into the main post too. Thanks

  2. Hi Paul,We were having a discussion other day where I work(we use db from multiple vendors), when someone mentioned there are other vendors who provide the functionality to restore from higher to lower level version. Do you think Microsoft will provide this “feature” in the future as a fall back mechanism?
    We have seen cases where some major upgrades cause significant application problems in production even with through testing in non prod environment.

  3. What if you were to use the Import/Export Wizard to export data from all tables to a blank database on an earlier version, then script out all indexes, triggers, users, procedures, ect. (assuming there are no features in these not available in the earlier version), and run them on the earlier version. It would be much more work that not putting the DB on a higher version in the first place, but it seems like it has a chance of being successful.

  4. Hi Paul,
    SQL 2008 and 2008 R2 are version 655 and 611 respectively. Your value of 665 for R2 seems confusing to me. Would you clarify?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.