Automate Schema changes in SQL Agent job

SQL_ME_RICH · March 31, 2016 2:41PM

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

Eddie D · April 4, 2016 7:34AM

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&gt;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

SQL_ME_RICH · April 4, 2016 4:55PM

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 D · April 7, 2016 3:06AM

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

Automate Schema changes in SQL Agent job

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk