Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

How to migrate a SQL Server database to a lower version

MSSQLTips author Basit Farooq By:   |   Read Comments (17)   |   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:

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

Note: For demonstration purpose, I'll be downgrading the OUTLANDER database hosted on my SQL Server 2012 instance (IITCUK\DEV01) to SQL Server 2008 R2 instance (IITCUK\SQLSERVER2008).

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

In Object Explorer, right-click OUTLANDER database, expand Tasks and choose

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

SQL Server Management Generate and Publish Scripts Wizard - Introduction Screen

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.

Choose option Script entire database and all database objects in the SQL Server Management Studio Generate Scripts Wizard

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

Choose option Specifying scripting options in the SQL Server Management Studio Generate Scripts Wizard

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.

Choose option Set scripting options including scripting the Schema and Data

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.

Generate and Publish Scripts - Summary Interface

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

Generate and Publish Scripts wizard iterating through each object

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.

SSMS


Opening SQL Script to install OUTLANDER database


Opening SQL Script to install OUTLANDER database

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.

Restoring OUTLANDER database

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

Verifying OUTLANDER database downgrade
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:


Last Update: 11/15/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, October 07, 2014 - 4:59:24 AM - PratiK Read The Tip

Make snapshot replication for required database in inferior version from superior version, This is very fast.

It works for me.


Tuesday, August 05, 2014 - 12:21:57 PM - IWouldLikeToUseThisArticle Read The Tip
Hello,
In your document, you wrote "script primary key option to true" but in your picture, you bordered "foreign key".
Please, can you tell me/us what is the good option ?
Best regards. 

Tuesday, June 10, 2014 - 8:53:56 PM - John Hamilton Read The Tip

To avoid the headaches of text file and script size on large datasets, you can simply write a query against another server and save the result-set into your current server.

 

SELECT * INTO 2008Table FROM 2012Server.2012Database.2012Table

 

You will need to setup a "linked server" first.  This will eliminate the sloppy large scripts and let the servers talk directly to each other.


Wednesday, December 04, 2013 - 6:58:57 PM - Raymond Ney Read The Tip

Great article. Thank you. It worked for me as I wasn't quite ready to move to SQL2012 yet and had a small DB I'd like to use on 2008. I used a text editor (editplus was my choice), then copy/pasted into SSMS as much as SSMS could handle and that seemed to do the job. My script was 880 megs. A bit too much to load into SMSS.

Thanks again for the article. Been a fan of this site for many years!


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.

 

 


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.


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


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.


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


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.

 


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 - 9:49:53 AM - JJ Read The Tip

Thanks for the info. Very helpful.


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.


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.


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.


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.


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.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.