{"id":68,"date":"2014-10-14T16:30:40","date_gmt":"2014-10-14T15:30:40","guid":{"rendered":"http:\/\/www.codereview.co\/?p=68"},"modified":"2014-10-14T17:06:42","modified_gmt":"2014-10-14T16:06:42","slug":"downgrading-a-sql-server-database-to-a-lower-version","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/downgrading-a-sql-server-database-to-a-lower-version\/","title":{"rendered":"Downgrading a SQL Server database to a lower version"},"content":{"rendered":"

Scenario proposal<\/span><\/strong><\/p>\n

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

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

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.\u00a0 SQL Server does not allow you to restore or attach a database from a higher version of SQL Server to a lower version\u00a0 of SQL Server. In this tip, we will look at a 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.<\/span><\/p>\n

Solution<\/strong><\/span><\/p>\n

The error message in the problem statement occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible.\u00a0 Backward compatibility is why we cannot restore or attach a database created from a higher version of SQL Server to a lower version SQL Server. However, there are a few options that can help us to downgrade the database from a higher version of SQL Server to a lower version SQL Server. These options include:<\/span><\/p>\n

\n
Generate Scripts wizard of SQL Server Management Studio<\/span><\/li>\n
SQL Server Integration Services<\/span><\/li>\n
Custom scripting and BCP<\/span><\/li>\n<\/ul>\n
In this tip we will use the Generate Scripts wizard of SQL Server Management Studio.\u00a0 Here are the basic steps we need to follow:<\/span><\/p>\n
\n
Script the database schema in higher version of SQL Server by using the Generate Scripts wizard of SQL Server Management Studio interface.<\/span><\/li>\n
Connect to the lower version of SQL Server, and then run the SQL scripts that were generated in the previous step, to create database schema and copy data.<\/span><\/li>\n<\/ul>\n
In the next section, I will demonstrate the steps for downgrading a SQL Server 2012 database to SQL Server 2008 R2 database.<\/span><\/p>\n
Note:<\/strong> For demonstration purpose, I’ll be downgrading the OUTLANDER<\/strong> database hosted o<\/em>n my SQL Server 2012 instance (IITCUK\\DEV01<\/strong>) to SQL Server 2008 R2 instance (IITCUK\\SQLSERVER2008<\/strong>).<\/em><\/span><\/p>\n
Step-by-Step Demo: Downgrading a SQL Server 2012 database to SQL Server 2008 R2<\/span><\/h3>\n
Step-1:<\/strong> Script the schema of the OUTLANDER<\/strong> database on the SQL Server 2012 instance (IITCUK\\DEV01) using the Generate Scripts<\/strong> wizard of the SQL Server Management Studio interface.<\/span><\/p>\n
In Object Explorer<\/strong> connect to IITCUK\\DEV01<\/strong>, right-click OUTLANDER<\/strong> database, expand Tasks<\/strong> and choose “Generate Scripts…”<\/strong>.<\/span><\/p>\n
\u00a0 $\"1\"$ <\/span><\/span><\/p>\n
This launches Generate and Publish Scripts<\/b> wizard. Click Next<\/b>, to skip the Introduction<\/b> screen and proceed to the Choose Objects <\/b>page.<\/span><\/p>\n
$\"2\"$ <\/span><\/span><\/p>\n
On the Choose Objects<\/b> page, choose option “Script entire database and all database objects”<\/b>, and then click Next<\/b> to proceed to “Set Scripting Options”<\/b> page.<\/span><\/p>\n
$\"3\"$ <\/p>\n
On the Set Scripting Options<\/b> page, specify the location where you want to save the script file, and then choose the Advanced<\/b> button.<\/span><\/p>\n
$\"4\"$ <\/span><\/p>\n
In Advanced Scripting Options<\/b> dialog box, set Script Triggers<\/b>, Indexes<\/b> and Primary Key<\/b> options to True<\/b>, Script for Server Version<\/b> to SQL Server 2008 R2<\/b>, and Types of data to script<\/b> to Schema and Data<\/b>.\u00a0 This last option is key because this is what generates the data per table.<\/span><\/p>\n
$\"5\"$ <\/p>\n
Once done, click OK<\/b>, to close Advanced Scripting Options<\/b> dialog box and return to Set Scripting Options<\/b> page. In Set Scripting Options<\/b> page, click Next<\/b> to continue to Summary<\/b> page.<\/span><\/p>\n
After reviewing your selections on Summary<\/b> page, click Next<\/b> to generate scripts.<\/span><\/p>\n
$\"6\"$ <\/p>\n
Once scripts are generated successfully, choose the\u00a0Finish<\/b> button to close the Generate and Publish Scripts<\/b> wizard.<\/span><\/p>\n
$\"7\"$ <\/p>\n
Step-2:<\/b> Connect to the SQL Server 2008 R2 instance (IITCUK\\SQLSERVER2008), and then run the SQL scripts that were generated in Step-1, to create the OUTLANDER<\/b> database schema and copy its data.<\/span><\/span><\/p>\n
In Object Explorer<\/b> connect to IITCUK\\SQLServer2008<\/b>, then in SQL Server Management Studio, open the SQL Server script you saved in Step-1.<\/span><\/p>\n
$\"8\"$ <\/p>\n
$\"9\"$ <\/p>\n
$\"10\"$ <\/span><\/p>\n
Modify the script, to specify the correct location for the OUTLANDER<\/b> database data and log files. Once done, run the script to create the OUTLANDER<\/b> database on IITCUK\\SQLServer2008 instance.<\/span><\/p>\n
$\"11\"$ <\/p>\n
Upon successful execution, refresh the Database<\/b> folder in Object Explorer<\/b>. As you can see in the following image OUTLANDER<\/b> database has been successfully downgraded.<\/span><\/p>\n
$\"12\"$ <\/span><\/p>\n
Thanks for reading this article,<\/strong><\/span><\/p>\n
Next steps :<\/strong><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01.Add this article to your database toolkit<\/strong><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2.Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03.Comment below if you need any assistance<\/strong><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Scenario proposal 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.\u00a0 After the downgrade of the database engine, you are unable […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[173,65,4,3],"tags":[8,14,15,13,12,16],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/68"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":7,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"predecessor-version":[{"id":357,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions\/357"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}