Copy content of SQL Server 2016 db

Have you tried using Microsoft's MSSQL-Scripter Tool? This site details using MSSQL-Scripter and python to dump schema and/or data from CLI.

Some of their code examples:

write schema to a file:

mssql-scripter -S .sql2016 -d WideWorldImporters --include-objects sales.CustomerCategories -f c:testsales_CustCategory.sql  

display data to console:

mssql-scripter -S .sql2016 -d WideWorldImporters --data-only --include-objects sales.CustomerCategories

Instead of using Management Studio generated script your could try to create it through sys.* and populate tables with a script created by INFORMATION_SCHEMA.COLUMNS into dynamic queries or convert the data into XML. If your installation of SQL Server does not have access to write into any folder accessible by you then you could just configure a small exe with connection string into the db.

So you would need to write a script for each object type and a script for fetching the data.. I don't think it worth it since your approach is faster, but I just mentioned this in case it triggers any ideas.

For example you could create the Stored Procedures like:

SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL 
               DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + char(13) + char(10) +
               OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)
               FROM sys.procedures
               WHERE is_ms_shipped = 0 and Name = 'Loop Name'

PS Be Careful with BCP it sucks :D

I have been using SQL schema compare and SQL Data compare in Visual Studio with SSDT to some success for the same scenario as you.

Best sequence is to first do a schema compare - but un-select Users / Permissions and Role Members from the options cog.

Perform the update, then compare again but this time do Logins only from Non-Application Scoped objects.

then do just Users, Permissions and Role Memberships.

Now you can do data comparison - click to show only records that are only in source (as your target DB is empty)

If you get foreign key issues, then you might have to select certain tables first, update them, then do the remaining tables.

Beware that Data compare doesn't do tables beyond a certain size - but I have been ok with up to 500,000 records I believe.

Next time you have to synchronise your DB, it should be a lot faster as there will be less work to do in the compares.

In SQL Server management studio, you can right-click the source database and choose Tasks>Generate scripts. There you can select to export schema and data. Then you end up with a possibly huge SQL script that you can execute on the target database. You possibly have to edit it a bit to avoid it will try to create your target database if that already exists. Same for users within the database.