How to migrate a SQL Server database to a lower version

Problem

After recently upgrading a SQL Server instance to SQL Server 2012 a few days ago, you noticed that your application is not functioning properly. You decided to roll back the upgrade by downgrading the SQL Server database engine to SQL Server 2008 R2. After the downgrade of the database engine, you are unable to attach the databases or restore the backups of the databases, even though the database compatibility level is set to the downgraded version of SQL Server. You receive the following error message, when you attempt to restore the database:

Msg 1813, Level 16, State 2, Line 1 
Could not open new database ‘DatabaseName’. CREATE DATABASE is aborted. 
Msg 948, Level 20, State 1, Line 1 
The database ‘DatabaseName’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

This error message is generated because SQL Server automatically upgrades the database, when you restore or attach the database from lower version to higher version. SQL Server does not allow you to restore or attach a database from a higher version of SQL Server to a lower version of SQL Server.

Solution

Checkout my tip (ie. How to restore a SQL Server database to a lower version) in which I discussed one time procedure which we can follow to downgrade the database from a higher version (SQL Server 2012) of SQL Server to a lower version (SQL Server 2008 R2) of SQL Server.

This article is published on MSSQLTips.com.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s