How can we help you today? How can we help you today?

Having issue with sqldatacompare CMD to generate insert scripts for data

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!
Cho
0

Comments

6 comments

Sort by
  • 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.

    Kurt_M
    0
  • 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?)
    Cho
    0
  • David Atkinson
    @Cho - can you explain why you need separate files?
    David Atkinson
    0
  • 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
    0
  • 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
    0
  • 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!
    Cho
    0

Add comment

Please sign in to leave a comment.