Automate Schema changes in SQL Agent job

SQL_ME_RICHSQL_ME_RICH Posts: 112
edited April 7, 2016 3:06AM in SQL Compare 11
Hello -

Would like to automate a schema compare and deploy, and run it from a SQL job.

Do you have a TSQL script in your library somewhere for this kind of job, that I can tweak and use in my environment?

The things I want automated are any changes from the source (all objects), but without dropping any objects that the target may have that the source does not.

Thank you

Comments

  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    Hi,
    Thank You for your forum post.

    Sadly I am not aware of any TSQL scripts that can be run via a SQL Agent job that will allow a user to use the SQL Compare Command Line to automate a comparison.

    I do have an example batch file that can be scheduled via the Windows Scheduler to automate your comparisons as follows using this example script:
    C:Program Files (x86)Red GateSQL Compare 11>SQLCompare.exe /Server1:MyServer1
    SQL2012 /Server2:PS-EDDIED2SQL2014 /Database1:AdventureWorks UserName1:sa /Password1:p@ssw0rd /Database2:Blank
    UserName2:sa /Password2:p@ssw0rd/out:"E:SQL Comparecompare.txt" /Exclude:User /ScriptFile:"E:SQL Com
    paresqlscript1.sql" /force /synchronize
    

    All the available command line syntax and switches can be found in the SQL Compare On-line help available HERE.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Hello Eddie, and thank you for the response.

    Unfortunately - I am needing to automate this process (because of the type of database in question - it makes multiple schema changes daily on the front end - it's a database for a API monitoring tool).

    It looks like you are referencing a .txt file from the example you gave. Is this the diff between the two that you then have the Task Schedule pick up to process the diff with? If so - I am guessing there must be a way to automate the diff to text?

    Thanks again
  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    Hi,
    Thank you for your reply.

    The example syntax is just for minor guidance, the help file is what you need to read.

    The example syntax is comparing two databases (there is a mistake as I omitted the /server2 switch), the /out switch to a text file is simply redirecting the console output to a text file to show the differences. The /scriptfile switch writes the changes to a SQL script that you can run outside of the SQL compare process. The /force switch is allow the text file and SQL script to be overwritten. The /synchronize switch deploys the changes to the target database. You can omit the /out, /scriptfile and /force switches, the comparison and deployment processes will still run.

    The information you need to get you up and running is in the help file, along with example syntax.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.