10

Are there any tools to convert SQL Server 2014 database to 2012?

I tried Generate Script but the generated script with data is too large and SQL Server Management Studio did not execute it, I need to have both schema and data.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Azarsa
  • 1,278
  • 3
  • 27
  • 37

3 Answers3

8

To my knowledge, there are basically three two options for migrating a database to a lower version of SQL Server, without using 3rd party tools:

  • Generate Scripts (not really suitable for large amounts of data)
  • Custom Scripting and BCP or Import/Export Wizard
  • SQL Server Integration Services (Transfer Database Task)

First option is not suitable in your case, as noted.

Second option is to simply script the structure of the database, and then use the Import/Export Wizard to copy the data, one table at a time. Note, that if you have foreign key constraints in your database, you might want to disable the constraints until after you have populated all your tables with data. This blog post explains in details how this can be done.

Third option uses the SISS Transfer Database Task which basically uses SMO to create the objects on the destination server and then transfers the data. This is the recommended way of migrating a database between SQL Server instances of different versions. SSIS requires that you have installed SQL Server Data Tools - Business Intelligence (SSDT-BI) for Visual Studio. Before SQL Server 2012, this was called Business Intelligence Development Studio (BIDS). You can download these here:

Dan
  • 10,480
  • 23
  • 49
  • How can I use SSIS Transfer Database Task? Microsoft SQL Server Data Tools must be installed in Visual Studio? or ... – Azarsa Jan 07 '15 at 08:40
  • I've updated my answer with some links regarding SSDT-BI and BIDS. – Dan Jan 07 '15 at 08:46
3

Use the script, but instead of executing it trough Management Studio, use the lightweight sqlcmd utility.

http://msdn.microsoft.com/es-es/library/ms162773.aspx

Oscar
  • 13,594
  • 8
  • 47
  • 75
  • 1
    If the database contains tables with many millions of rows, the generated script might become unpractically large (several tens or hundreds of GB's). It will be a pain to execute such a huge script even using the sqlcmd utility. – Dan Jan 05 '15 at 10:47
  • @Dan I had loaded files with several gigabytes of data with this method without problems.. – Oscar Jan 05 '15 at 10:49
  • Okay, I must admit I have never tried it. But typically, the script would take up 2-3x as much space as the database itself, which could be a problem if you do not have enough disk space on your environments. This could obviously be solved by breaking the script up into smaller pieces, but it just seems like a lot more work than using, for example, the [SSIS Transfer Database Task](http://msdn.microsoft.com/en-us/library/ms141204.aspx). – Dan Jan 05 '15 at 10:51
1

Oh well, Dan's second option does not work: after installing SSDT-BI for VS2013 and configuring the Database Transfer Task it says that the version of the source database instance has to be lower or equal to the version of the destination database instance: so, a migration from 2014 to 2012 (or in my case 2008R2) is not possible.

Adam
  • 63
  • 5