In the last few posts, I’ve written about using the SQL Compare command line for a specific object and shown how to get a report. This post will look at getting the actual script.
When we run the command line or generate the report, we see what’s changed, and even the details of the chances, but how will that get deployed? The actual script is often important to a DBA to ensure that these changes won’t cause problems in a live environment.
To get the script, there is a /scriptfile parameter (or /sf) that will output the file. You add this in similar way as you do the report file, including the path. The important thing here is to ensure that you have write access to the path.
I have added a few differences in my databases, and you can see them in my complete report:
If I want to see what will be run for all these changes, I can add the /sf and get the script. In this case, I’ll add this to the end of the CLI call:
/sf:C:UsersSteveDocumentschanges.sql
This produces a script that looks like this:
It’s a normal “SQL Compare” script, with comments at the top as well as the various transaction items.
This is good, because I can see there is a table drop in here. I actually renamed a table, so this is a problem. I might want to then decide how to handle this, or not to deploy this change.
Note: Using SQL Source Control or SQL Change Automation allows this to be handled in other ways.
I can also combine this with a single table inclusion to check one item. For example, I can run this:
sqlcompare /server1:AristotleSQL2017 /server2:AristotleSQL2017 /database1:compare1 /database2:compare2 /include:table:mytable /sf:C:UsersSteveDocumentsmytable1.sql
When I do that, I see my script has a table rebuild in it, which is something else I might be concerned about.
With the other posts on the SQL Compare CLI, we can now choose what to compare, get a report, and see the actual scripts being run. This should allow us to choose the way that we want to deploy changes with SQL Compare from the command line.
I don’t know that these are the best way to deploy to production, but when you need to sync something quickly, get a report and script, and then decide if this works for you.
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.