Having issue with sqldatacompare CMD to generate insert scripts for data

ChoCho Posts: 4 New member
in SQL Data Compare
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!
Tagged:
· Share on Twitter

Best Answer

  • Kurt_MKurt_M Posts: 199 Silver 1
    Answer ✓
    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

    Kind regards,

    Kurt McCormick
    Product Support Engineer, Redgate

    Need help? Take a look at our Help Center

    · Share on Twitter

Answers

  • Kurt_MKurt_M Posts: 199 Silver 1

    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.

    Kind regards,

    Kurt McCormick
    Product Support Engineer, Redgate

    Need help? Take a look at our Help Center

    · Share on Twitter
  • ChoCho Posts: 4 New member
    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?)
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    @Cho - can you explain why you need separate files?
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • ChoCho Posts: 4 New member
    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
    · Share on Twitter
  • ChoCho Posts: 4 New member
    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!
    · Share on Twitter
Sign In or Register to comment.