Copy content of SQL Server 2016 db

user_3850519 0
Viewed 130

I would like to copy whole content of database: schema & data between two SQL Server servers via a scrip, preferably embedded in linux docker image. Copy would be used on test environment for testing purposes.

I have few constraints:

  1. I'm able to able to reach DBs selected via sqlcmd protocol.
  2. I don't have access to files on servers
  3. I would like to execut script from linux

What solutions I discarded:

  • Backup (bak) files as I don't have file access.
  • Bacpac - as according to docs i don't have necessary permissions.

What i think may work but is seems over engineered:

  • Use sqlpackage to create DAC file as it is available on linux.
  • Use dac to update structure on target database
  • Use bcp from Mssql Tools to copy data.

Solution could fail when because of update of structure failure.

Is there any other option which i overlooked? Or better idea?

EDIT: I'v implemented solution as described above( sqlpackage + bcp ) and dockerized it HERE, all on linux.

Still, looking for better approach.

docker linux sql-server
4 Answers

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.

user_68355340

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:\test\sales_CustCategory.sql

display data to console:

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

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.

user_122257510

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

user_14881970
Related