How to migrate a SQL Server database to a lower version

By: Basit Farooq   |   Read Comments (13)   |   Related Tips: More > Restore

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:

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

Solution

The error message in the problem statement occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible.  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:

• Generate Scripts wizard of SQL Server Management Studio
• SQL Server Integration Services
• Custom scripting and BCP

In this tip we will use the Generate Scripts wizard of SQL Server Management Studio.  Here are the basic steps we need to follow:

• Script the database schema in higher version of SQL Server by using the Generate Scripts wizard of SQL Server Management Studio interface.
• 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.

In the next section, I will demonstrate the steps for downgrading a SQL Server 2012 database to SQL Server 2008 R2 database.

Step-by-Step Demo: Downgrading a SQL Server 2012 database to SQL Server 2008 R2

Step-1: Script the schema of the OUTLANDER database on the SQL Server 2012 instance (IITCUK\DEV01) using the Generate Scripts wizard of the SQL Server Management Studio interface.

In Object Explorer connect to IITCUK\DEV01, right-click OUTLANDER database, expand Tasks and choose “Generate Scripts…”.

This launches Generate and Publish Scripts wizard. Click Next, to skip the Introduction screen and proceed to the Choose Objects page.

On the Choose Objects page, choose option “Script entire database and all database objects”, and then click Next to proceed to “Set Scripting Options” page.

On the Set Scripting Options page, specify the location where you want to save the script file, and then choose the Advanced button.

In Advanced Scripting Options dialog box, set Script Triggers, Indexes and Primary Key options to True, Script for Server Version to SQL Server 2008 R2, and Types of data to script to Schema and Data.  This last option is key because this is what generates the data per table.

Once done, click OK, to close Advanced Scripting Options dialog box and return to Set Scripting Options page. In Set Scripting Options page, click Next to continue to Summary page.

After reviewing your selections on Summary page, click Next to generate scripts.

Once scripts are generated successfully, choose the Finish button to close the Generate and Publish Scripts wizard.

Step-2: 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 database schema and copy its data.

In Object Explorer connect to IITCUK\SQLServer2008, then in SQL Server Management Studio, open the SQL Server script you saved in Step-1.

Modify the script, to specify the correct location for the OUTLANDER database data and log files. Once done, run the script to create the OUTLANDER database on IITCUK\SQLServer2008 instance.

Upon successful execution, refresh the Database folder in Object Explorer. As you can see in the following image OUTLANDER database has been successfully downgraded.

Next Steps

• To avoid this issue, always make sure that you perform a full backup of the database before you upgrade the SQL Server and database to a higher version of SQL Server.  In addition, be sure to thoroughly test the application prior to releasing the application to the users.
• Consider this downgrade option as your last option to rollback from an upgrade because the time and storage needed can be very large.
• With a very large database be sure you have sufficient storage to support the data needs.
• Be sure to verify row and object counts as well as test your application before releasing to production.
• Additional Resources:
  • Why Can't I Restore a Database to an Older Version of SQL Server?
  • SQL Server Database Engine Backward Compatibility
  • SQL Server Upgrade Tips

Last Update: 11/15/2012

About the author

Basit is a Senior Database Administrator and has worked in the IT industry for 11+ years.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, November 15, 2012 - 10:31:54 AM - RD Francis

Read The Tip

Do keep in mind, of course, that this is probably not a practical option with a large database.  When I've had to do this with 5-10GB DBs, especially when I know I'll be doing it more than once (data coming in from an external site that running a newer version of SQL than we are), I use a variation of this plus SSIS, as follows: 1.  Generate the DB scripts as above, schema only. 2.  Create the empty database. 3.  Use the "Import/Export" commands to import data from the newer version DB into the older one.  Save the SSIS package thus generated. 4.  You may need to modify the default column mappings; for instance, if there are columns of the timestamp datatype, those can't actually be moved over this way.

Thursday, November 15, 2012 - 2:01:24 PM - JY	Read The Tip
This is a misleading title as there is no way to "restoe a sql server database to a lower version". What is described here in the tip is how to migrate a sql db to a lower version.

Friday, November 16, 2012 - 3:54:38 AM - Mark Pointon

Read The Tip

I'd like to higlight the following section from your article: In Advanced Scripting Options dialog box, set Script Triggers, Indexes and Primary Key options to True, Script for Server Version to SQL Server 2008 R2, and Types of data to script to Schema and Data.  This last option is key because this is what generates the data per table. Please can you confirm that you have tested the feature of scripting for a particular environment, in the sql version you are using. I raise this as this feature does not work in SQL 2008. I'd scripted a 2008 db to restore onto a 2005 db and it still include SQL 2008 specific commands and data types. I'd guessing this feature still does not work.

Friday, November 16, 2012 - 9:00:05 AM - Basit	Read The Tip
Mark, i have tested this procedure by downgrading SQL Server 2012 database to SQL Server 2008 R2. This works perfectly fine for me. See above screen shots. The options are slightly different in SQL Server 2008, 2005 and 2000.

Monday, November 19, 2012 - 5:00:42 PM - sreekanth bandarla	Read The Tip
I feel, changing the title to "Migrating Database/Moving Data Manually to a lower Version". IMHO, Restoring the Database to Lower Version is Incorrect and Misleading Title.

Tuesday, November 20, 2012 - 9:49:53 AM - JJ	Read The Tip
Thanks for the info. Very helpful.

Tuesday, November 20, 2012 - 10:43:12 AM - John Jakob	Read The Tip
Yes, the scripting of schema and data will certainly work. But it is not a very practical solution for larger databases -- i.e., with tables containing millions of rows. The scripts will be HUGE, and it will take prohibitively LONG to run those scripts!     DBAs should understand that. -j

Tuesday, November 20, 2012 - 11:05:38 AM - John Jakob

Read The Tip

Another consideration here is referential integrity.   Even for small databases, if your database has good RI (lots of foreign keys), you may get into trouble when you try to run the scripts, because of data dependencies! You may find that you have to populate your tables in a certain order, so that parent tables are populated before child tables, etc.  Sometimes this sequence of data population can be accommodated by running the scripts in a certain order -- but that could become tedious... A better approach (that I've used in the past) is to edit the script files manually, and STRIP OUT all the FK constraints, saving them off to a separate file.   Then run the scripts as normal on the target, getting data into all the tables first.  Then, after all the tables have data in them, start applying the FK scripts.

Wednesday, November 21, 2012 - 8:13:49 AM - Jeremy Kadlec	Read The Tip
JY and sreekanth, Thank you for the feedback and sorry for any confusion.  We have updated the tip title.  Let us know if this makes more sense. Thank you, Jeremy Kadlec

Sunday, November 25, 2012 - 4:09:20 PM - Luc DM	Read The Tip
@RD Francis - point 4 : another contstraint that needs to be stripped out of the script (and reenabled afterwards) is IDENTITY. Not possible to import data into an active IDENTITY column.

Sunday, January 13, 2013 - 12:35:34 PM - Paul Andrew

Read The Tip

The script has been created on the SQL 2012 server and copied over to my SQL 2008 R2 server but when I try to open the .sql file this error pops up: Error HRESULT E_FAIL has been returned from a call to a COM component. Have you seen this before? Any idea what I should try to get beyond the error? I am trying to restore two SQL 2012 databases from .bak files, original sizes 27GB and 7GB and they created scripts of 577GB and 184GB. Thanks, Paul

Monday, January 14, 2013 - 12:30:56 AM - Paul Andrew	Read The Tip
Sorry, ignore my question. I understand now that these files are too large to be opened.

Wednesday, April 17, 2013 - 3:35:27 PM - Varun	Read The Tip
As pointed out by Mark Pointon  This seems to be misleading, if there are NEW T-SQL functions used within your StoredProcedures like (LAG,LEAD or even OVER CLAUSE with RANGE attribute) generating a SQL 2008 compatible scripts doesn't automatically converts those to SQL 2008 standard and would result in error upon calling.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. The COMPLETE Performance Solution for SQL Server - SQL Sentry Demystify TempDB Performance and Manageability