Having issue with sqldatacompare CMD to generate insert scripts for data

Cho

Hello! I am trying to generate schema and data scripts using CMD "sqlcompare" and "sqldatacompare". The following "sqlcompare" command worked for me for generating the schema script and place them into a script folder:

> .\sqlcompare /Server1:MyServer01 /Database1:MyDatabase /makescripts:"\\MyServer01\My_SQL_Compare_BackUp\MyDatabase_20211021"

However, the "sqldatacompare" command below doesn't create the INSERTs script for me in the folder, when I run it, it only generate the schema scripts:

> .\sqldatacompare /Server1:MyServer01 /Database1:MyDatabase /makescripts:"\\MyServer01\My_SQL_Compare_Backup\MyDatabase_20211021"

Is there something I am missing in my sqldatacompare command?

Thanks!

Kurt_M

Hi @Cho

Could you try the following to see if this works for you:

sqldatacompare /Server1:<server name> /Database1:<database name> /Scripts2:<path to scripts folder> /Synchronize

Kurt_M

Hi Cho,

Instead of the /makescripts option, you'll actually need to use the /scriptfile option instead. I have pasted below a simple amendment of your command which shows how the /scriptfile option is to be used. You'll notice it's not much different from the /makescripts option. Using this should resolve your issue.

> .\sqldatacompare /Server1:MyServer01 /Database1:MyDatabase /Database2:MyDatabase2 /scriptfile:"\\MyServer01\My_SQL_Compare_Backup\MyDatabase_20211021\script.sql"

Below is the explanation on this option which can be found through the following command: sqlcompare /help /verbose.

/scriptfile:<scriptfile>
Alias: /sf
Writes SQL change migration script to specified file.
This option allows you to generate an SQL script to migrate the changes which can be executed at a later time. If the file already exists an error will occur, unless you have also used the /force switch.

Also, a helpful tip that I use:

You can output the help list to HTML by using this: sqlcompare /help /verbose /html /out:C:\help.html

I find it makes it far easier to read and to search.

Cho

Thanks @Kurt_M! I just tried it and it worked and produced one script file for the INSERTs for all tables. Do you know if it is also possible to have one INSERT script per table? (Similar to the way when we use the SQL Data Compare GUI to compare Database1 to an empty folder, then hitting "Deploy" will generate a Data folder with INSERTs scripts per table?)

David Atkinson

@Cho - can you explain why you need separate files?

Cho

Hi @David Atkinson, it is so that I could use the content in the "Data" script folder to compare data across different databases. It seems that if all the INSERTs are in one file, I cannot use that file to use SQL Data Compare to compare data across database1 <-> scriptfolder2

Cho

Kurt_M said:

Hi @Cho

Could you try the following to see if this works for you:

sqldatacompare /Server1:<server name> /Database1:<database name> /Scripts2:<path to scripts folder> /Synchronize

Thanks Kurt! /Synchronize is exactly what I was missing, my script works as intended now!