I have Database A on SQL Server 1 that is SQL Server version 2016. I need to migrate it to Server 2 that is SQL Server version 2012. Database A is pretty small, only about 5 GB total.

I don't think there's a way to migrate it via a Full backup, so what would be the best way to move the database?...BCP?...or is there better methods?

  • 2
    Right click on Database ->Task->Generate Scripts... in Advanced "Types of data for script" then select "Shema and data" And try to run script in your lower version – CR241 Feb 12 '20 at 23:41
  • 1
    You might want to check out dbatools.io--it has a Copy-DbaDbTableData command. docs.dbatools.io/#Copy-DbaDbTableData – Tony Hinkle Feb 13 '20 at 1:51
  • @CR241 I like the idea of Generate Scripts, but I think if the database is over 1 GB or so, it becomes difficult to move the data. – J.D. Feb 13 '20 at 2:19
  • @CR241 Yea unfortunately SSMS won't let me open the script file because it's too big. :( I get a generic "The operation could not be completed." error. – J.D. Feb 13 '20 at 16:53
  • @J.D. if script size is large, you must use sqlcmd utility to execute it. Open Command Prompt (cmd) with Administrator Privileges (Or right click at cmd and select ‘Run As Administrator’). Run following command: sqlcmd -S YourServer\DBInstance -i "C:/path-to-script/script.sql" You can also specify database username and password if required. sqlcmd -S YourServer\DBInstance -i "C:/path-to-script/script.sql" -U sa -P MyPassword – CR241 Feb 13 '20 at 18:36

BCP by itself may not be sufficient as it won't address non-table objects like stored procedures. To migrate the schema, one option would be to use a Visual Studio database project importing it from the 2016 instance and deploying it to your 2012 instance. Afterwards, two options to import your data rows are BCP and the Import/Export wizard in SSMS.

  • 1
    Depending on table design, you can also use data compare to move the data too from 2016 to 2012 after the deploy is complete, in addition to BCP and Import/Export wizard. – Antoine Hernandez Feb 15 '20 at 0:16

You can generate a BACPAC using SqlPackage.exe or the Export Data-Tier Application wizard in SSMS. There are some limitations on what objects can be exported, however, in general, you will be able to export most of the objects in your database.

This is similar to MattyZDBA's answer except in a BACPAC the schema and data are wrapped in a single file and you don't need to use Visual Studio.

  • I've only experimented a little with Visual Studio database projects but they seemed needlessly cumbersome. I have heard of the BACPAC / DACPAC method before but never explored it. Is my understanding based on the limitations article you linked, that those limitations only apply to the SQL Server 2012 version? – J.D. Feb 13 '20 at 13:24
  • Also, when I try it I'm getting a bunch of errors on some of my stored procs on a specific schema. Is there a way to specify which schemas you want to include in the BACPAC? (I see in the second step "Export Settings", in the Advanced tab, you can choose which table schema to export, but not for any other entity types.) – J.D. Feb 13 '20 at 13:43
  • 1
    No, you cannot specify a subset of schemas to export, even using the command-line SqlPackage.exe. To do that, I would suggest you use Visual Studio and Import a Database. You can then remove the objects you don't want to deploy, publish your database to the lower version and use BCP to copy your data. – HandyD Feb 13 '20 at 22:26
  • Ah ok, seems kind of silly Microsoft doesn't offer that in the BACPAC Export tool. Probably wouldn't have been that hard for them incorporate as a feature. Ok, thanks! – J.D. Feb 13 '20 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.