A customer recently was concerned about the time to run SQL Compare for a large database. They were synching with the command line, but at times they want to just sync up a procedure or two from one database to the other.
I knew this could be done and passed along some ideas, but decided to write a post. This post looks at how to do this.
A CLI Comparison
The SQL Compare command line is pretty easy to use. Lots of switches and options, but the simple thing is point it to a couple instances and databases and get a comparison. Here’s a command line.
sqlcompare /server1:AristotleSQL2017 /server2:AristotleSQL2017 /database1:compare1 /database2:compare2
And the result. You can see below I have a table and three procedures that are different.
If I want to limit what’s compared, I can certainly use a filter, but from the command line, there’s a simple way to see certain objects. There is an INCLUDE switch that I can use to just set a filter here without creating a file.
For example, if I want to just see stored procedures, I can do this:
sqlcompare /server1:AristotleSQL2017 /server2:AristotleSQL2017 /database1:compare1 /database2:compare2 /Include:storedprocedure:
This gives me just my three stored procedures.
Likewise, I can also change this to a table and just get that object.
If I want a specific object, I can get that as well. Here I use the include like this:
/Include:storedprocedure:[GetMyTable]
Then I get just my one object, with a faster compare. Only this one is checked.
Then if I add the Synchonize switch, the changes will get deployed.
I often find that people are looking to deploy quickly just a known object or two for some hotfix or out of band change. Using the command line let’s me pick an object that I know about and build a comparison for just that object.
There are lots of options and ways to use SQL Compare, and I’d urge you to explore a bit as you look to improve your database deployments. If you don’t have it yet, download an eval and give it a try.