Product articles
SQL Toolbelt
Database Builds and Deployments
SQL Deployment Automation using SQL…

SQL Deployment Automation using SQL Compare Command Line

SQL Compare provides a command line interface (CLI) for running database schema comparisons and generating a deployment script, as part of a scripted process. I'll demonstrate how to do this with a 1-click process, using a batch (.bat) file.

Guest post

This is a guest post from Feodor Georgiev. Feodor has worked with SQL Server for over 15 years, specializing in database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years, he has expanded his focus to coding in R for assignments relating to data analytics and data science. Outside of his day-to-day schedule, he blogs, shares tips on forums and writes articles on his Data Messenger blog.

The article provides the basis for a simple, but automated database deployment process that will

  1. Compare the schemas of two databases
  2. Automatically generate a deployment script that when executed on the target will makes its schema the same as the source
  3. Generate an HTML report of the changes that were made.

There are CLIs for Windows and for Linux. I’ll use the former. This article is not intended as a tutorial on the various command line switches, options and filters, but I do cover some of those I found useful when using the command line for the first time. My example compares the schema of two live databases, but you can equally well run the comparisons using scripts folders, or SQL Compare Snapshots.

Getting started with automating database schema comparisons

SQL Compare’s command line interface allows access to all the functionality of the UI tool, and allows us to introduce automation and scheduling, for example by invoking the command line from a batch file or PowerShell script, and then perhaps executing the script as a scheduled Agent job. I’ll be focusing on simple command line execution with a batch script, but see also Automating Schema Comparisons with SQL Compare Projects and PowerShell.

Command line licensing

Automations that use SQL Compare command line on a machine that is the ‘server’ (such as a Build or CI Server) in a deployment pipeline need Redgate Deploy or SQL Toolbelt licenses. For full details, see the Changes to distribution of command line page of the documentation.

Where to find the command line executable

You can find the executable file, SQLCompare.exe, in the installation directory of SQL Compare. Simply open a command line and navigate to that directory, in my case as follows:

C:\> cd\Program Files (x86)\Red Gate\SQL Compare 12

Reviewing command line parameters and documentation

From the installation directory, a good starting point is to bring up the sqlcompare help screen:

> sqlcompare /?

SQL compare command line help

Figure 1

A couple of interesting switches are /verbose and /html. The former enables more detailed information about the sqlcompare.exe options, and the latter outputs the Help documentation to HTML (although it would be nice if this HTML file were already in the installation directory of SQL Compare).

> sqlcompare /? /v /html

Executing the above command produces a very long string of HTML text in the console window, but if we use the /out switch, we can write the file directly to the disk system, as follows:

> sqlcompare /? /v /html /out "c:\temp\SCHelp.html"

Open the file in a browser to see very nice documentation of the command line tool of SQL Compare.

SQL compare error codes

Figure 2

Compare schemas of two databases

To compare two databases, we can use the following general syntax:

This command will simply compare the source and target databases. However, all we need to do is add the /sync parameter to the end of this command, and then SQL Compare will attempt to synchronize the schema of the TargetDatabase so that it matches the schema of the SourceDatabase. For any database object that exists in both databases but with differences, the object’s definition in the target will be altered to match its definition in the source. Any objects that exist in source but not in target will be created and any that exist in target but not in source will be dropped.

In my case, I have two instances of SQL Server on my local machine; one default instance and one named instance called DW. Let’s say that the default instance hosts my reference database, AdventureWorks2016, representing the current version of the database that is in production, and the DW instance hosts my local development database, AdventureWorks2016PoC, where I’m doing some proof-of-concept development work.

Listing 1 will generate compare the two schemas, with my PoC database as the target and the reference database as the source and will automatically deploy any differences it detects to the target.

sqlcompare.exe /s1 . /s2 .\DW /db1 AdventureWorks2016 /db2 AdventureWorks2016PoC /sync

Listing 1

If you prefer to run the script from PowerShell, you can use the one in Listing 2.

Set-Alias SQLCompare 'C:\Program Files (x86)\Red Gate\SQL Compare 12\SQLCompare.exe' -Scope Script
    sqlcompare /s1 . /s2 .\DW /db1 AdventureWorks2016 /db2 AdventureWorks2016PoC /sync

Listing 2

However, in either case, you will get the following error:

Deploying changes (from DB1 to DB2)
Error: Synchronization of '..AdventureWorks2016' and '.\DW.AdventureWorks2016PoC' failed: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view
'HumanResources.JobCandidate' because it is not full-text indexed. Error executing the following SQL: --A stored procedure which demonstrates integrated full text search CREATE
PROCEDURE [dbo].[uspSearchCandidateResumes] @searchString [nvarchar](1000), @useInflectional [bit]=0, ...

SQL Compare autogenerates a deployment script, which it wraps within a transaction, so that it can roll back all changes should something go wrong. However, SQL Server will not allow creation of a full text index within a transaction, so SQL Compare will deploy the full text index changes after the transaction completes. However, if the initial transaction includes changes to objects that reference that index then the synchronization script may fail. In this case, the uspSearchCandidateResumes stored procedure uses the full text index on this table to search within the candidate resumes.

Activate logging to capture errors in a log file

SQL Compare’s /LogLevel parameter allows us to capture errors and warnings in a log file. By default, logging is disabled, but it might be a great idea to turn it on.

To get around this problem, SQL Compare has a /exclude parameter, which we can use to exclude the object that causes the error:

Listing 3 excludes the uspSearchCandidateResume stored procedure.

sqlcompare.exe /s1 . /s2 .\DW /db1 AdventureWorks2016 /db2 AdventureWorks2016PoC /exclude:StoredProcedure: uspSearchCandidateResumes /sync

Listing 3

This will complete successfully, then you can simply re-run Listing 1 or 2 and SQL Compare will create the uspSearchCandidateResumes stored procedure in the target database, to bring the source and target fully into synchronization.

Create a batch file for a 1-click database deployment

I would like to automate this simple SQL deployment process so that I can run it ‘on demand’, whenever I need to start over with development of my Proof of Concept. After all, this is what PoCs are for: to try out an idea and retreat quickly, if it doesn’t work. Of course, a good idea might be to setup a compare script which “backs up” the PoCs before the iteration. This can be done by scripting the PoC database to a new folder before running the deployment (see How to create a directory of object-level scripts using SQL Compare).

All you need to do to accomplish this is to create a .bat file containing the synchronization command, as shown in Listing 4.

"C:\Program Files (x86)\Red Gate\SQL Compare 12\sqlcompare.exe" /s1 . /s2 .\DW /db1 AdventureWorks2016 /db2 AdventureWorks2016PoC /exclude:StoredProcedure: uspSearchCandidateResumes /sync

Listing 4

If you perform an initial deployment that includes only the uspSearchCandidateResumes stored procedure, as described in the previous section, then you won’t need to exclude it from Listing 4, or subsequent listings. Note that the path to the executable file is in quotation marks, since it contains spaces. Also, make sure that the path is correct in your case. Simply create the file with the .bat extension and double-click on the file anytime you want a fresh copy of the database schema.

Generating a schema deployment script for review

Let’s suppose that the POC works out well, and now I would like to generate a deployment script that, when executed, would update the reference database with the required changes. Note that this auto-generated script will need to be twisted very carefully if you’ve made schema changes that might affect existing data in the target. SQL Compare will try to preserve data and issue warnings if it can’t. See Handling Tricky Data Migrations during State-based Database Deployments.

Again, this is a simple task. We simply reverse the order of the source and target databases, remove the /sync parameter, and use the /scriptfile parameter to specify where the script file has to be saved, for later review.

sqlcompare.exe /s1 .\DW /s2 . /db1 AdventureWorks2016PoC /db2 AdventureWorks2016 /exclude:StoredProcedure:[dbo].[uspSearchCandidateResumes] /scriptfile:c:\temp\comparePoC.sql

Listing 5

Of course, I can then create another bat file from this script, as described previously, so that I can invoke the compare and generate the deployment script at any time.

Generate a HTML report outlining the deployment changes

A great way to obtain a report of the production database objects affected by the synchronization task is to use the /report (or /r) and /ReportType (or /rt) parameters for specifying the report location and report type, respectively.

For example, to generate an interactive report for the synchronization task in Listing 5, simply add the following parameters to the command:

/r:c:\temp\Report1.html /rt Interactive

It will generate a report that looks as shown in Figure 3.

Database deployment report

Figure 3

Using Project specifications to do command line schema comparisons

Sometimes, I need to set up comparison projects that require, for example, more complex object filtering rules, and makes use of other advanced project comparison options that SQL Compare offers.

In such cases, I find it much easier to set up the comparison using the SQL Compare UI, save the project, and then use that a SQL Compare project as a parameter from the command line. Again, once I have it working, I can save it as a bat file and run it on demand.

One reason to do this is to take advantage of the UI for more complex configurations, but also to overcome the fact that Options are not stored within the project, but they can be configured via the command line and saved in the bat file.

Let’s suppose that I would like to use a project to compare the AdventureWorks2016 database to the AdventureWorks2016PoC database, but I want to ignore the indexes (this will ignore indexes unless they are unique or primary keys). Just follow these steps:

  1. Open SQL Compare UI and start a new project
  2. Select the source and target databases to compare and hit Compare now
  3. From the Tools | Project Options menu, check the Ignore Indexes box and hit Compare now
  4. Click the top check box to include all objects (or selectively pick or exclude objects as required)
  5. Choose File | Save as to save the project.

We then simply use the project as a command line parameter, as shown in Listing 6.

sqlcompare.exe /project:c:\temp\AdventureWorks2016v_DW.AdventureWorks2016PoC.scp

Listing 6

See Automating Schema Comparisons with SQL Compare Projects and PowerShell for more on this topic.

Summary

This article described how easy it is to automate the creation of database schema deployment scripts, via the SQL Compare Pro command line. The rich toolset lets us specify logging, reporting options and lets us control many other aspects of the database schema comparison.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more