The article provides the basis for a simple, but automated database deployment process that will
- Compare the schemas of two databases
- Automatically generate a deployment script that when executed on the target will makes its schema the same as the source
- 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.
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 /?
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.
Compare schemas of two databases
To compare two databases, we can use the following general syntax:
SQLCompare.exe /s1:SourceServer\SourceInstance /database1:SourceDatabase /s2:TargetServer\TargetInstance /database2:TargetDatabase
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
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
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
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.
To get around this problem, SQL Compare has a /exclude parameter, which we can use to exclude the object that causes the error:
/exclude:<object type>:<regular expression>
Listing 3 excludes the
uspSearchCandidateResume stored procedure.
sqlcompare.exe /s1 . /s2 .\DW /db1 AdventureWorks2016 /db2 AdventureWorks2016PoC /exclude:StoredProcedure: uspSearchCandidateResumes /sync
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
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
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.
Using Project specifications to do command line schema comparisons
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:
- Open SQL Compare UI and start a new project
- Select the source and target databases to compare and hit Compare now
- From the Tools | Project Options menu, check the Ignore Indexes box and hit Compare now
- Click the top check box to include all objects (or selectively pick or exclude objects as required)
- Choose File | Save as to save the project.
We then simply use the project as a command line parameter, as shown in Listing 6.
See Automating Schema Comparisons with SQL Compare Projects and PowerShell for more on this topic.
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.
Was this article helpful?