Tuesday, June 5, 2012

SQL Server "downgrade path not supported" when trying to attach or restore database

I attempted to attach a database from SQL Server 2008 R2 into a SQL Server 2005 instance. This resulted in the "downgrade path is not supported" problem. This is because the databases are not backward compatible due to differences in the structure of the data file (MDF)

From SQL Server 2008 R2 (or whatever you're using), you can use a script generation wizard that will automatically put all the schema and data into one script. This can then be executed on the older version of SQL Server.

1. Open SSMS for SQL server 2k8 R2

2. Right-click on the database, chooses Tasks, then Generate Scripts

3. Click Next

4. Click Advanced

5.  Change the server version to whichever version you're downgrading to. Then change it the types of data to script to "schema and data". Make any other changes to the defaults based on your situation, then click Ok

6. Click Next

7. Click Finished

8. Wait for the script generation to complete

9. Execute the script in the older version's SSMS

Note 1: In my case the database takes longer to move than it does to install a new instance, which means the generated script will be big too. So on the server where I ran into the problem I simply installed SQL  server 2008 R2 side-by-side with SQL Server 2005, in order to generate the script on the server where it's to be executed. This saved a lot of time!

No comments:

Post a Comment

There was an error in this gadget