Stack Overflow Asked by mjpolak on November 27, 2021
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:
What solutions I discarded:
What i think may work but is seems over engineered:
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.
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
Answered by a lead alcove on November 27, 2021
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
Answered by Emmanouil Karamalegos on November 27, 2021
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.
Answered by James Anderson on November 27, 2021
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.
Answered by MarTim on November 27, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP