How to migrate a SQL Server database to a lower version

By:   |   Updated: 2022-02-24   |   Comments (67)   |   Related: 1 | 2 | > Restore


Problem

Recently, we had a request to restore a SQL Server 2012 database to SQL Server 2008R2. We tried a backup and restore of the database, but we got the following error message:

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 checks the version of the database when restoring and does not allow you to restore a database from a newer version of SQL Server to an older version of SQL Server.  This is the same issue you have if your try to attach a database from a higher version of SQL Server to a lower version of SQL Server.

In this tip, we will look at one approach to downgrade the database from a higher version of SQL Server to a lower version 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 of 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:

  • Use the Generate Scripts Wizard in SQL Server Management Studio
  • Use SQL Server Integration Services
  • Create Custom Scripting and BCP

In this tip we will use the Generate Scripts Wizard in SQL Server Management Studio. 

Here are the basic steps we need to follow:

  1. Script the database schema and data from the higher version of SQL Server by using the Generate Scripts Wizard in SSMS.
  2. Connect to the lower version of SQL Server, and run the SQL scripts that were generated in the previous step, to create the database schema and 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).

Steps to Downgrade a SQL Server Database Using SSMS Generate Scripts Wizard

Step 1

Script the schema of the OUTLANDER database on the SQL Server 2012 instance (IITCUK\DEV01) using the Generate Scripts wizard in SSMS.

In Object Explorer connect to IITCUK\DEV01, right-click on the OUTLANDER database, expand Tasks and choose "Generate Scripts...".

In Object Explorer, 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.

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 click the Advanced button.

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

In the Advanced Scripting Options dialog box,

  • set Script for Server Version to SQL Server 2008 R2 (or whatever version you want)
  • under the Table/View Options, set Script Triggers, Script Indexes and Script Primary Keys to True
  • and set 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 the Advanced Scripting Options dialog box and return to Set Scripting Options page. In Set Scripting Options page, click Next to continue to the Summary page.

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

Generate and Publish Scripts - Summary Interface

Once the 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 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, the OUTLANDER database has been successfully downgraded.

Verifying OUTLANDER database downgrade

Notes

There are a few things to be aware of when using this approach.

  • This solution creates one large SQL file that has the scripts to create the database objects and also INSERT statements for the data in the tables.
  • For a large databases, the SQL file can get very large if you script out both the schema and the data and could be hard to load into an editor.  Also, you may get a memory related error message from the editor if the file is too big.
  • For large databases, around 1GB or more, if this approach does not work, then you should look at using SSIS to migrate the database or create custom scripts to script out the objects and BCP out the data for each of the tables.  You can use this Generate Scripts wizard to just generate the schema without the data and use SSIS or BCP to export and import the data.
  • This approach works for SQL Server 2005 through SQL Server 2019.  Some of the scripting options might be a bit different in newer versions, but the process is still the same.
  • Before just executing the script, you should review the script to make sure everything looks correct such as the path of the database files, database options, etc.
  • Also if you are using new functionality that does not exist in the lower version, SQL Server won't be able to create the objects and you will need to review the scripts that were generated and update the code accordingly.
  • For a very simple database this approach should work pretty easliy, but you might need to spend some time making some modifications to the script for a more complex database.
  • Below is a list of all of the scripting options. If you click on an item, the bottom part of the screen gives you a short definition of the option.
script options
Next Steps
  • To avoid this issue, always make sure that you perform a full backup of the database before you upgrade 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:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-02-24

Comments For This Article




Friday, August 18, 2023 - 10:40:02 AM - Edgar Gomez Back To Top (91498)
Great information. Very usefull. In my case it was SQL2017 to SQL2012. We finally used generated scripts for schemas and BCP for data. Some minor adjustments were needed, but is was sucessfull.

Wednesday, May 19, 2021 - 7:42:34 AM - SQLJ Back To Top (88708)
This won't create insert data in the script!!
Any idea how to have this in it?

Wednesday, March 3, 2021 - 1:06:11 AM - Afzal Back To Top (88325)
As in SQL 2019 second (date & Time Stamp) enable by default, can we enable second in 2016? we are facing issue our application developed on SQL 2019 and we have handle seconds in date & time stamp but now need to use Application on 2016. Not able to restore backup so we are using import and export function

Monday, February 15, 2021 - 4:56:51 AM - Walter Rodrigues Back To Top (88238)
I get "The Operation could not be completed" when trying to Open the Script file.

Am downgrading from 2019 to 2017

Monday, January 18, 2021 - 12:54:48 AM - Arun tamang Back To Top (88060)
Hello,

Can we downgrade Ms SQL standard 2019 to 2014 ?

Thursday, January 7, 2021 - 6:41:04 AM - Christson Back To Top (88014)
I tried downgrading the database from 2017 to 2014 but got this error message:
Cannot execute script
Insufficient memory to continue execution of the program(mscorlib)

but I have enough memory on my system

Tuesday, January 5, 2021 - 9:29:11 AM - FlauzerOriginal Back To Top (88003)
This won't work if stored proc and function are encrypted.

Thursday, December 3, 2020 - 7:02:37 AM - Mircea Dragan Back To Top (87876)
You say that it works for SQL Server from 2017 to 2005. As you know SQL Server 2017 supports always encrypted columns. What happens when you try to downgrade a database with one or more always encrypted columns? Be aware you may not have the certificate to view those columns.

Tuesday, September 22, 2020 - 12:14:13 PM - Terry Back To Top (86515)
Very well written and very useful. I got one error whilst script was being created in SQL2017 for SQL2014.

However, what I was really hoping is for a way to automate this procedure to Export the script daily.

We have Upgraded ServerA as it was a requirement by SoftwareProviderA for a new upgraded version of their software.

We used to take the backups and restore the database to ServerB which uses Reporting Software only to interrogate the data but it is not Compatible with SQL2017.

We are happy for the data to be a day old so would like to setup overnight job to export data from 2017 to 2014.

Having to manually do this every day will be too time consuming.

Wednesday, September 16, 2020 - 10:36:51 PM - Ricky Lively Back To Top (86493)
You can load larger script files than SSMS likes to load using SQLCMD from the command-line.

Thursday, July 30, 2020 - 4:45:29 PM - Salvador Mendoza Back To Top (86219)

Still working in 2020!

From SQL 2017 to 2014 why? Because 2014 is 32 bit and it cannot be upgraded to 64 bit

Thanks a lot

Wednesday, May 6, 2020 - 9:45:29 AM - Jeff Back To Top (85583)

Just wanted to say "thank you". This saved me a ton of time this morning.

Friday, September 27, 2019 - 7:43:55 PM - FLAVIO TORRES Back To Top (82609)

Muito bom. Obrigado :)

Monday, August 12, 2019 - 12:06:38 PM - Fernando Jacinto Back To Top (82039)

replying to: Thursday, April 11, 2019 - 6:56:53 AM - RAM C

Dear Team,

We are currently using the SQL server 2014 Express and we go for the SQL server 2017 full std version because of our regulars usage are going very high so, please can you advise us before the upgrade what are the things we need to verify?

Thank you,

B RAM C

You can use https://datamigration.microsoft.com/scenario/sql-to-sqlserver?step=1 and download related free tools for a successful migration.

Friday, May 17, 2019 - 4:11:05 AM - Odilbek Back To Top (80112)

Thanks a lot. It helped me

Thursday, April 11, 2019 - 6:56:53 AM - RAM C Back To Top (79529)

Dear Team,

We are currently using the SQL server 2014 Express and we go for the SQL server 2017 full std version because of our regulars usage are going very high so, please can you advise us before the upgrade what are the things we need to verify?

Thank you,

B RAM C

Sunday, March 31, 2019 - 10:30:45 PM - athan Back To Top (79434)

I'm facing error.... like "Exception of type 'System.OutOfMemoryException was thrown.' by the way the size of the SQL FILE is 20GB"

Saturday, March 23, 2019 - 4:34:54 AM - Loai Al-Utaibi Back To Top (79382)

 thanks a lot

Wednesday, February 20, 2019 - 1:14:00 AM - Dega Back To Top (79069)

It helped me.

Thank you!

Friday, January 18, 2019 - 8:17:21 AM - Ismaila Jonathan Back To Top (78808)

Nice tutorial you have up there sir. Thumbs up. 

However, I am facing a little challenge here. My restore creates the database and migrates the table names and schema of the old database, but the new database doesn't have all the old data in those tables imported.

Funny enough, the restore also edits the system database named 'master'. It migrated all the old database data alongside the old table names and schema to the master database. 

Monday, October 22, 2018 - 9:12:15 AM - Andreas G Back To Top (78020)

 when detaching a mdf file and attaching it to a newer server version it gets an internal version update without even telling the user that this happened. Right now I had the situation that I needed to test a software if a certain bug appears in SQL 2012 and not appears in SQL2014. But the R&D server was upgraded to 2014 and my SQL2012 has no network connectivity.

Although the MDF file has the SQL version stamping for version 110 (that of 2012) it has a second, invisible mark that tells the older SQL server that a certain version requirement is not met. To confuse users even more the it says something like "the database filoe has the version 792 and the current server version is 702". So after MS has the product name "SQL server 2012" and an internal version number 11.0.2100 (for RTM) there is a third mark "702" to obfucscate the version totally.

 

 

Tuesday, August 21, 2018 - 11:11:21 AM - Ben Back To Top (77265)

Thanks

it didn't work cause functions and procedures were encrypted the other alternative is to replicate but I'm struggling with


Wednesday, July 25, 2018 - 8:14:18 PM - chavdar Back To Top (76831)

If scripted objects and data great than 2gb this method generate error when open the file. You must start script with osql.

Wednesday, May 9, 2018 - 10:23:48 AM - Jeff Sweeney Back To Top (75904)

 Thank you, very helpful.

 

Wednesday, July 26, 2017 - 10:46:48 AM - alemayehu Back To Top (63282)

 

 If the database size is small you can generate the script that contains the schema definition and the data. Once you generate the script you can do some editing specially the location of the data files (.mdf and .ldf) you can run the script from SSMS or SQL cmd preferably the later one as SSMS is limited to 2 GB.

 

For large databases first generate the database and DB object scripts only (no data), edit the script specially the location of the data files (.mdf and .ldf) then run the script it will create the database and schemas. Once this is done, you can then start to export the data from the source tables to the new tables in the new database. Make sure to populate the primary tables first then the secondary ones as the foreign key constraints enforce that. Or you may drop and then create the foreign keys after migrating the data.

 

 

Friday, July 21, 2017 - 5:18:55 AM - Jaks Back To Top (59918)

 Hello, Mr. Basit,

I have tried your solution, but it seems above process only able to send the database schema, not the data inside.

Do you have any other solution if I want to downgrade back up the data also?

 

Wednesday, June 28, 2017 - 5:34:51 AM - Ramon Back To Top (58475)

Hey,

man thanks so much for that tip!

Worked like a charm!

Thursday, May 11, 2017 - 6:47:26 AM - Carl Back To Top (55672)

 If the generated file is too large for SSMS use SQLCMD.

Friday, March 3, 2017 - 9:18:39 AM - Willi Back To Top (47060)

Hello,

I have done this, but we had the following problem:

The scipt was so big, that SQL Management Studio craches, when we tried to load the file.

I solved this by using a little program in C#, which read the file line by line and execute the several inserts.

This took very long, but it came to an end.

Do you have another idea to solve this?

Regards Willi

Tuesday, February 7, 2017 - 10:26:10 AM - STUART SMITH Back To Top (46075)

 

Sometimes the database is too large to transfer the data in the same steps as described above. Just use the steps above to transfer the Schema and then you can create an Access Database with links into both servers to transfer the data. The Queries you build in Access are straight forward and easy.

Sunday, October 9, 2016 - 11:13:15 PM - Angel Back To Top (43520)

Gracias! Fue muy útil!!!!!

Thursday, September 29, 2016 - 6:55:29 PM - Leonel Ambrosio Back To Top (43461)

 Receiv this error

 

 Memoy insuficient, this script

Wednesday, September 28, 2016 - 12:49:16 PM - cgunner Back To Top (43439)

Why didn't you make a backup prior to upgrading to SQL 2012??? 

 

Thursday, September 22, 2016 - 2:15:48 PM - Chris Back To Top (43395)

Yes, thank you for the well-written documentation.
Would not have attempted it otherwise ...

For what it's worth, I attempted this on a 2016 to 2008 R2.
Original database size 19 GB.

I cancelled the script creation when the file reached 60 GB, and I had to manually kill the associated SPIDs.
Maybe this works OK for 2012 downgrade, but not so sure for 2014 or 2016.
The DB had been upgraded from 2014 to 2016 during a prior restore, so that might have had something to do with it.

 

Wednesday, September 7, 2016 - 10:39:59 AM - James Back To Top (43276)

 This did not work for me. I got the following errors.

 

Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at least 4 MB to accommodate a copy of the model database.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'test1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 911, Level 16, State 4, Line 3
Database 'test1' does not exist. Make sure that the name is entered correctly.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'test1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'test1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'test1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'test1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

 

Wednesday, June 8, 2016 - 10:54:42 PM - Prathibha Back To Top (41646)

 

 Hi,

 

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?

 

Regards,

Prathibha

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

 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 (41260)

 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 (41088)

 

 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 (39053)

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.

 

Thomas

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

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 (38684)

tks my friend! help my day!

Thursday, September 3, 2015 - 10:23:24 AM - Anshul Dubey Back To Top (38595)

Thanks man, It really helped !!! Kudos

Monday, June 8, 2015 - 5:19:21 AM - meysam Back To Top (37770)

Thank,s

big like

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

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 7, 2015 - 10:16:06 PM - Ashwin Pai Back To Top (37132)

Thanks Boss, life saver!

Thursday, May 7, 2015 - 4:55:18 AM - Raja Back To Top (37115)

nice , its save my time..thanks

 

Thursday, April 9, 2015 - 1:19:30 AM - Krushna Back To Top (36869)

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

 

Monday, March 2, 2015 - 9:07:58 AM - nakaoh Back To Top (36400)

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

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

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

Tuesday, October 7, 2014 - 4:59:24 AM - PratiK Back To Top (34859)

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

It works for me.

Tuesday, August 5, 2014 - 12:21:57 PM - IWouldLikeToUseThisArticle Back To Top (34011) 
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 Back To Top (32179)

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 4, 2013 - 6:58:57 PM - Raymond Ney Back To Top (27694)

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 (23405)

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 (21432)

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 (21425)

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 (20524)

@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 (20454)

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 (20431)

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 (20430)

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 Back To Top (20428)

Thanks for the info. Very helpful.

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

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 (20379)

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 Back To Top (20372)

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 (20362)

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 (20359)

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.














get free sql tips
agree to terms