How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
How to migrate a SQL Server database to a lower version
By: Basit Farooq | Read Comments (18) | 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...".
1 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
This launches Generate and Publish Scripts wizard. Click Next, to skip the Introduction screen and proceed to the
Choose Objects page.
Connect with MSSQLTips
Follow @mssqltips 9,990 followers
2.405
Like 4.4k
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.
2 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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.
3 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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.
4 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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.
5 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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 Farooq is a Senior Database Related Resources
Administrator and has worked in the
IT industry for 11+ years. More SQL Server DBA Tips...
View all my tips
6 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
Print Share 3 Like 54 Tweet 8 +7 Become a paid author
Learn More
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.
Notify for updates
*Name *Email
Paragraph
*** 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.
Note: your email address is not published. Required fields are marked with an asterisk (*)
Get free SQL tips:
*Enter Code Save Comment
Saturday, November 22, 2014 - 3:45:36 PM - Mohamed Read The Tip
Tip Comments Pending Approval
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.
7 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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.
8 of 9 23/11/2014 15:15
How to migrate a SQL Server database to a lower version http://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-serve...
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
SQL XEvent efficiency with SQL Profiler simplicity - Idera SQL XEvent Profiler for free
How you can avoid the 5 worst days in a DBA’s life – read “The 3AM Meltdown” new from the DBA Team
SQL XEvent efficiency with SQL Profiler simplicity - Idera SQL XEvent Profiler for free
Read the SQL Server database transaction logs discover unauthorized changes and recover lost data
Follow Learning Resources Search Community More Info
Get Free SQL Tips DBAs Tutorials Tip Categories First Timer? Join
Twitter Developers Webcasts Search By TipID Pictures About
LinkedIn BI Professionals Whitepapers Top Ten Free T-shirt Copyright
Google+ Careers Tools Authors Contribute Privacy
Facebook Q and A Events Disclaimer
Pinterest Today's Tip User Groups Feedback
RSS Author of the Year Advertise
Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.
9 of 9 23/11/2014 15:15
