CODEWITHSUNDEEP
sql-serversql-server-2012database-migration
Azarsa
Azarsa

Reputation: 1308

SQL Server 2014 backup to 2012

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.

Upvotes: 10

Views: 31156

Answers (3)

Dan
Dan

Reputation: 10680

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:

Upvotes: 8

Adam
Adam

Reputation: 73

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.

Upvotes: 1

Oscar
Oscar

Reputation: 13960

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

Upvotes: 3

Related Questions