Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


How to migrate a SQL Server database to a lower version

By:   |   Read Comments (32)   |   Related Tips: More > Restore


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.


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.


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:

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

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Wednesday, June 08, 2016 - 10:54:42 PM - Prathibha Back To Top




For my 3 GB SQL2014 database, I have create .sql file is of 11.5 GB, I am unable to open it in SQL server management studio, Please help me out any way to run this script in SQL2012 database?




Tuesday, May 17, 2016 - 2:26:01 PM - alemayehu Back To Top

 Very nice post and very well supported by screenshots that makes easy to implement. What i would like to suggest is that if the database size very large you may not be to open and run from the management studio rather you can run the script from the command prompt. This is what i did in my recent assignment

Alternatively you can simply create the schemas only and run on the lowerversion to create the database and schemas. Once this is done you can do the import operation but make sure you first populate the primary tables and then the foreign ones for tables that have constraints defined in it.


Monday, April 18, 2016 - 3:51:25 PM - Jeff Back To Top

 Thx for the great instructions. I am setting up a DW. The production machine was 2014 and the development 2012. First time I had to move data down to a lower version. You made it easy.

Tuesday, March 29, 2016 - 4:06:20 PM - khumera Back To Top


 How to resolve microsoft sql server 2008 r2 error 948.Error mesage cannot attach database since its created in version 661 and the current ssms supports version 665

Wednesday, November 11, 2015 - 5:23:44 PM - Thomas Strike Back To Top

Thank you very much Basit. I'm going to be doing a lot of upgrades over the next 6 months and even though we're testing you never know what will happen. Having this as a possible rescue in the event of problems is really making my life less stressful.



Wednesday, September 23, 2015 - 12:48:32 AM - Paul S Adams Back To Top

Thank you so much for this tip.

My need for the downgrade was quite different to the scenario you described, but nonetheless needed a similar rescue - we needed to supply data from our database (on SQL Server 2014) to a customer running SQL Server 2008 R2.

Awesome to find such a helpful piece of info freely available.

Tuesday, September 15, 2015 - 11:48:04 AM - rodrigo Back To Top

tks my friend! help my day!

Thursday, September 03, 2015 - 10:23:24 AM - Anshul Dubey Back To Top

Thanks man, It really helped !!! Kudos

Monday, June 08, 2015 - 5:19:21 AM - meysam Back To Top


big like

Tuesday, May 19, 2015 - 4:44:21 AM - Kenneth Back To Top

Hi, I received an error saying "Directory lookup for the file "C:\Database\SampleDB.mdf" failed with the operating system error 3(The system cannot find the path specified).

Even I created a folder with the same location as mention above. What do you think is the problem to this? BTW I'm just executing Schema Script not Schema and Data.

Thursday, May 07, 2015 - 10:16:06 PM - Ashwin Pai Back To Top

Thanks Boss, life saver!

Thursday, May 07, 2015 - 4:55:18 AM - Raja Back To Top

nice , its save my time..thanks


Thursday, April 09, 2015 - 1:19:30 AM - Krushna Back To Top

Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Monday, March 02, 2015 - 9:07:58 AM - nakaoh Back To Top

it not work for Big dabase (my database 5GB)

Saturday, November 22, 2014 - 3:45:36 PM - Mohamed Back To Top

But it didn't work when i choose sql server 2005 

Tuesday, October 07, 2014 - 4:59:24 AM - PratiK Back To Top

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 Back To Top
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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

@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 Back To Top

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 Back To Top

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 Back To Top

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.


Tuesday, November 20, 2012 - 9:49:53 AM - JJ Back To Top

Thanks for the info. Very helpful.

Monday, November 19, 2012 - 5:00:42 PM - sreekanth bandarla Back To Top

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 Back To Top


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 Back To Top

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 Back To Top

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 Back To Top

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.

Learn more about SQL Server tools