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?
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.
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).