{"id":4710,"date":"2017-04-20T10:50:24","date_gmt":"2017-04-20T17:50:24","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4710"},"modified":"2017-06-06T08:45:29","modified_gmt":"2017-06-06T15:45:29","slug":"sqlskills-sql101-restoring-to-an-earlier-version","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-restoring-to-an-earlier-version\/","title":{"rendered":"SQLskills SQL101: Restoring to an earlier version"},"content":{"rendered":"

As Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101<\/strong> blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

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.<\/p>\n

So is this possible? The very simple answer is: No.<\/p>\n

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<\/a>), but you can’t go backwards to an earlier version.<\/p>\n

Why is this the case?<\/p>\n

Upgrade steps<\/strong><\/p>\n

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.<\/p>\n

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\u00a0database’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.<\/p>\n

As each of these upgrade steps is performed, the database version number is increased. Here are some examples:<\/p>\n