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?
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.
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.