Product articles SQL Compare Database Builds and Deployments
Using the SQL Compare command line with…

1 October 2019

2 Comments

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

1 October 2019

2 Comments

Using the SQL Compare command line with Argfiles

Phil Factor demonstrates how using XML argfiles to pass parameters to SQL Compare cuts out a lot of the tedious scripting involved in modifying a database schema comparison and deployment process, as required for each target database.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

There are many ways of automating the many regular development chores you need to perform with SQL Compare, using PowerShell. The method that you choose depends on your requirements. This article aims to point out some of the advantages of using XML to pass the detailed instructions to SQL Compare, when you run it from the command line interface (CLI).

SQL Compare XML argfile

Passing parameters to SQL Compare CLI

SQL Compare CLI allows you to pass parameters from an XML argument file (argfile), or using a project file, as well as the more usual approach of using switches in the tail of the command line. This is useful if you require a lot of parameters but can’t generate the project file from the GUI.

There are several reasons for using XML argfiles to pass in parameters. It is not a good idea to generate a project file automatically, whereas the XML argfile is supported. This means that you can create an XML argfile for each task, from scratch, and execute them all on a schedule, maybe. You could also make a generic task with all the many details that SQL Compare sometimes requires and just fill in the details that change, such as the server, database or credentials, leaving all the other options, switches, and so on, intact. I give a run-through of all these in my article Comparing and Deploying SQL Server Databases using the SQL Compare Command Line on Linux or Windows.

We can summarize some of the advantages and disadvantages of these approaches.

Method Advantages Disadvantages
Project file
  • Good integration with the GUI
  • Passwords encrypted
  • Simply edited within the GUI
  • Supports options not documented in the other techniques
  • Requires a file for each database for each operation.
  • Can’t change parameters
  • Proprietary undocumented format
XML File
  • Easily created and modified in PowerShell
  • Saves a lot of typing at the CLI
  • Allows use of all characters in regex expressions.
  • Easily transformed into other formats using XSLT
  • No integration with the GUI
  • Passwords stored in plain text
Switches in the command tail
  • Plenty of examples around, versatile
  • Can get long and complicated, difficult to type in
  • Passwords in plain text.
  • Some characters used in Regex expressions such as pipe (|) cannot be used.
‘Splatted’ (in PowerShell)
  • Easy to see your parameters
  • SQL Compare only accepts splatting if the switch has a parameter

Getting started with the XML approach, using PowerShell

Using an XML argfile to specify the arguments for the command line interface is especially useful when there is a lot of repetition, or if you are using a lot of command line options. Here is a simple XML argument file (argfile) for SQL Compare:

<?xml version="1.0"?>
<commandline>
  <database1>FirstDatabase</database1>
  <server1>SecondHostname</server1>
  <database2>SecondDatabase</database2>
  <server2>SecondHostname</server2>
</commandline>

And here is a more complex one:

<?xml version="1.0" encoding="UTF-8"?>
<commandline>
   <sourcecontrol1 />
   <version1>HEAD</version1>
   <server2>TargetDBServer</server2>
   <database2>TargetDB</database2>
   <scriptsfolderxml>MyScriptFolderXml.sfx</scriptsfolderxml>
   <migrationfolderxml>MyMigrationFolderXml.mfx</migrationfolderxml>
   <include>Assembly</include>
   <include>FullTextCatalog</include>
   <include>Function</include>
   <include>Schema</include>
   <include>StoredProcedure</include>
   <include>Table</include>
   <include>View</include>
   <include>identical</include>
   <Synchronize />
</commandline>

Having saved all these settings in an XML argfile, you can then execute it from Bash, PowerShell or the command prompt:

sqlcompare /Argfile:MyXMLfilename.xml

If you do this, the only other command-line switches you can add are /verbose or /quiet. The rest must be in the XML argfile. This gives us problems with sensitive information such as passwords which we can’t store in the XML argfile and which we can’t then pass to the CLI separately. I’ll show you how to get around this in PowerShell, shortly, but it remains a problem if you use the command prompt or Bash.

Specifying switches

Switches can be included with their switch name, in long or short form, as the name, and their value as the XML value. If you have several values, such as allowed in the /options switch, you would separate them with commas:

<options>n,oc,t</options>

The easiest way of creating the more complex argfile keys that represent switches is to check out the equivalent SQL Compare project file. I have no idea why the XML structure and keys are rather different between the two, as they do an almost parallel job. That said, most of the interesting and complicated keys are the same and you can cut and paste between them. This allows you to do the heavy lifting in the GUI, save a project file and pick out the bits you need to represent the switches that you require.

Creating Argfiles from a parameter list in a hashtable

For me, the easiest way of creating these XML argfiles in PowerShell is by putting the parameters into a hash table and passing it to a helper function to translate it to the correct XML format. It cuts out a lot of the irritating mistakes of crafting XML by hand.

<# Unless you like writing XML, the easiest way of creating these files in PowerShell is probably this. We just put our parameters into a hash table and pass it to a function. 
 #>
$MyParameters = @{
    'Database1' = 'Sigrid';
    'Server1' = 'MyFirstServer';
    'Database2' = 'Sigrid'
    'Server2' = 'MySecondServer';
    'userName1' = 'MyUsername1';
    'password1' = 'MyPassword1';
    'userName2' = 'MyUsername2';
    'password2' = 'MyPassword2';
    'report' = "${env:temp}\MyReport.html";
       'force' = $null;
    'reportType' = 'HTML';
    'loglevel' = 'Warning';
    'options' = 'default'
}
# just a helper function to translate the hash table to the XML format expected
# neither of the built-in cmdlets can cut it because they wrap the hash table up
Function SaveHashTableAsSQLCompareXML([hashtable]$TheHashTableParameters,[string]$WhereToStoreIt)
{
    $xmlDoc = [System.Xml.XmlDocument]'<?xml version="1.0"?>';
    $commandline = $xmlDoc.AppendChild($xmlDoc.CreateElement('commandline'));
    $TheHashTableParameters.GetEnumerator() | foreach {
        $Element = $commandline.AppendChild($xmlDoc.CreateElement($_.Name));
        if ($_.Value -ne $null) # if it is a switch with a parameter
        {
            $textNode = $Element.AppendChild($xmlDoc.CreateTextNode($_.Value));
        }
    }
    $xmlDoc.Save("$WhereToStoreIt"); #save it to the user temp area
}
SaveHashTableAsSQLCompareXML $MyParameters "${$env:temp}\Paramfile.xml"
sqlCompare /Argfile:"${env:temp}\Paramfile.xml" >"${env:temp}\summary.txt"
start "${env:temp}\MyReport.html" #display the HTML result report

Simple Argfiles for common comparison tasks

Here are a few example argfiles, for various tasks. There will be some raised eyebrows among readers at the unencrypted passwords, but we’ll deal with that a little later.

Script out a new version of the script

<?xml version="1.0"?>
<commandline> <!-- Script out a new version of the script folder -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <makescripts>MyPathTo\Tryout</makescripts>
  <force/>
</commandline>

Generate a build script

<?xml version="1.0"?>
<commandline> <!-- generate a build script -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
   <empty2/> 
  <scriptFile>MyPathTo\MyScriptFile.sql</scriptFile>
  <force/>
</commandline>

Report on any differences between two databases

<?xml version="1.0"?>
<commandline> <!--report on any differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <Database2>Sigrid</Database2>
  <Database1>Sigrid</Database1>
  <report>MyPathTo\MyReport.html</report>
</commandline>

Script out, and report on, just the differences between two databases

<?xml version="1.0"?>
<commandline> 
<!-- Script out, and report on, the differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <database2>Sigrid</database2>
  <database1>Sigrid</database1>
  <exclude>Identical</exclude>  
  <scriptfile>MyPathTo\MyDatabaseBuild.sql</scriptfile>
  <report>MyPathTo\MyReport.html</report>
</commandline>

Create a snapshot from a database

<?xml version="1.0"?>
<commandline> 
<!-- make a snapshot of a database -->
  <Server1>MyFirstServer</Server1>
  <database1>Sigrid</database1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>MyPathTo\MyDatabase.snp</makesnapshot>
  <options>default</options>
</commandline>

Executing SQL Compare CLI with XML argfiles

The following 1-liner will execute all the XML argfiles in an ArgFile directory:

<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter '*.xml'| 
   foreach{SQLCompare  "/Argfile:$($_.fullname)"}

Generating argfiles on-the-fly

What if you haven’t got any argfiles? We can generate them on the fly! Once an argfile is there, you can use the one-liner above every time you want to re-execute it.

Here is our first, simple version, which generates, on-the-fly, the argfile for making a snapshot of each supplied database. Although it is fine, it has the unencrypted password problem, if you are using SQL Server authentication rather than Windows authentication, which is likely if you are using Linux or MacOS.

<# We just have a list of servers, databases and (in this case userids and passwords) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer';
          'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Abnego'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{ 'Database' = 'Adeliza'; 'Server' = 'MyFirstServer' },
    @{ 'Database' = 'Sigrid'; 'Server' = 'MyFirstServer' }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}
<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter 'Snap*.xml' |
foreach{ SQLCompare  "/Argfile:$($_.fullname)" }

Suddenly, we now have lots of snapshots, and a way of comparing live databases with snapshots to allow us to determine what’s changed and to maybe then allow use to save these changes.

The problem of storing passwords

As mentioned earlier, many readers will be raising eyebrows about putting unencrypted passwords in files; this is always a bad idea. OK, you save these in your user area so that you have the measure of protection afforded by NTFS access control. However, passwords must also be encrypted.

In fact, if you use SQL Server authentication at all, then your SQL Compare project files should also be stored your user area (in PowerShell, “${env:temp}” refers to the temporary directory within your user area). This is because, although the passwords are encrypted in project files, it is done in a way that the encrypted password can be used by anyone, whatever their Windows/Linux identity, and they can paste into their project files to access the database with SQL Compare.

To store credentials, in PowerShell, Microsoft recommends that you use Import-CliXml and Export-CliXml. The Export-Clixml cmdlet encrypts credential objects by using the Windows Data Protection API. The encryption ensures that the contents of the credential object can be decrypted only by your user account and on only that computer. The exported CLIXML argfile can’t be used on a different computer or by a different user on that computer. See Securely Store Credentials on Disk and Secure Password with PowerShell: Encrypting Credentials.

Here is an example of how to store passwords on disk. I’m just wanting to list all the details about my databases on a server and I want a routine that will do this safely, regardless of whether I am using Windows Authentication or SQL Server authentication:

import-Module sqlserver #import all the libraries for SMO
$SQLserver = 'MyFirstServer'
$SqlUserName = 'MyUsername'
if ($SqlUserName -ne $null)
{
$SqlEncryptedPasswordFile = `
"$env:USERPROFILE\$($SqlUserName)-$($SQLserver).xml"
# test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $ServerConnection = new-object `
        "Microsoft.SqlServer.Management.Common.ServerConnection" `
        ('MyFirstServer', $SqlCredentials.UserName, $SqlCredentials.Password)
}
else
{
    $ServerConnection =
    new-object "Microsoft.SqlServer.Management.Common.ServerConnection" `
    ($csb.server)
}
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerConnection
$s.Databases

The problem with SQL Compare argfiles is that the command line interface reads them directly from disk by the CLI, and we aren’t allowed to pass sensitive information such as passwords separately, as parameters. This means that the problem of storing passwords in plain text in the argfile would have to be fixed within the product.

The solution for storing passwords in Argfiles

For a quick fix to work around this, and to provide a solution for existing users of SQL Compare, we have the awkwardness of adding the password ‘on the fly’.

Let’s start again with those argfiles. We now aren’t going to put passwords into them. If they have a username, then we need to add an appropriate password on the fly, to create a temporary version of the XML argfile that we can then pass on to SQL Compare. It could be for either username1 or username2, in which it is either password1 or password2.

<# We just have a list of servers, databases and (in this case Userids and logins) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Abednego'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Adeliza'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_.userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}

Now, we must create a helper function to get the password. The first time you run this function with any user and server, it will elicit from you the password and you’ll have to type it in. From then on, it will get it from the secure store.

function SavedPassword ($SqlUserName, $server)
{
    $SqlEncryptedPasswordFile = `
    "$env:USERPROFILE\$($SqlUserName)-$($server).xml"
    # test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $SqlCredentials.GetNetworkCredential().password
}

Now we are back in business. Sadly, it is a bit more complicated in the code but that doesn’t slow things down at all. We pick up each argfile in turn, peer into it to see if it requires passwords and if so, insert them. We copy each to a new temporary file and pass it to SQL Compare. Once it has used it, we hurriedly delete it!

Get-ChildItem -Path "${env:temp}" -Filter 'Snap-*.xml' |
foreach{
    $content = [System.IO.File]::ReadAllText($_.fullname);
    $xmlContent = [xml]$content
    write-output "$($xmlContent.'#comment')"
    $server1 = $xmlContent.commandline.server1
    $server2 = $xmlContent.commandline.server2
    $username1 = $xmlContent.commandline.username1
    $username2 = $xmlContent.commandline.username2
    
    if ($username1 -ne $null)
    {
        $xmlContent.commandline.password1 = (SavedPassword  $username1 $server1)
    }
    if ($username2 -ne $null)
    {
        $xmlContent.commandline.password2 = (SavedPassword  $username2 $server2)
    }
    $Tempfile="${env:temp}\Temp_$($_.basename).xml"
    $xmlContent.Save($Tempfile)
    SQLCompare  "/Argfile:$Tempfile" 
    Remove-Item -Path "$Tempfile"
}

Conclusion

What I like most about using argfiles to pass parameters to SQL Compare is that you can do so much by gathering into a directory all the argfiles you need for a particular task, and then pass each argfile to SQL Compare, in turn. It cuts out a lot of scripting and means that you can modify the overall task by adding, modifying or deleting the argfiles. Unlike project files, these XML argfiles are easily modified by a script or in a text editor.

If you choose to use argfiles, and use PowerShell, you will need to get around the problem of unencrypted passwords, as I’ve demonstrated.

Share this post.

  • not the river I stand in

    IIRC, there is a glaring omission in the SQL Compare (and data compare) command utility: There is no way to specify a schema for a table. This can be a problem as you might imagine. For example, one app we have has a schema per security portfolio (I work for a large financial concern) and within the schema, identically named tables for different classes of data (raw, clean, etc.) The limitation in SQL Compare means we cannot use it to compare tables either between two schemas or between two databases (whether on the same server or another one) because the table names appear to be ambiguous. In reality though, the schema-qualified table names are, of course, unique.

    Now I admit I haven’t tried this lately! So, if it is now possible to schema-qualify table names as I described above, there is much to celebrate!

    • Phil Factor

      Thanks for the interesting comment. In a sense you are right in that the way that you do this with /include and /exclude isn’t an entirely straightforward. However, the use of the Argfiles make it rather more tolerable because you can set up the comparison and once it is working just reference it when necessary. If you don’t like using regex expressions you can use the /Filter argument instead but filters need to be set up in the GUI and then referenced as a file.
      Early on in the life of SQL Compare, the decision was taken to make the parameters for the /Include and /Exclude arguments Regex expressions rather than ordinary strings. At the time, it was a good idea. Unfortunately, this was before Schemas were introduced with SQL 2005.
      In order to schema-qualify a table, you must escape the square brackets and dot because the parameter you pass is treated as a .NET standard regular expression. Because of this. If you need to fully-qualify the names of objects for either the /Include and /Exclude arguments then you have to ‘escape’ all parts of the string that would be interpreted as a special character by the regex engine. This applies to all database objects, not just tables.
      In an Argfile, if you just wanted to include the table dbo.publishers, you would need

      Pubsv2
      Pubs

      Table
      Table:[dbo] .[Publishers]

      You can, of course, take advantage of this regex to make there two parameters more versatile

      Pubsv2
      Pubs

      Table
      Table:[Authors]|[Discounts]|[Jobs]|[Stores]

      There is a further complication. Unless you include the brackets ( [ ] ) in the string /Include:table:Customer, then CustomerAddress, CustomerCard and CustomerTerritory tables will be included because, in Regex parlance, you are asking for any object of the type you specify (a table in this example) beginning with the word ‘Customer’. You can get around this in SQL Compare by qualifying everything with square brackets like this …
      [dbo].[Customer]
      and then escaping the two characters that the Regex engine will otherwise misinterpret.
      [dbo].[Customer]
      You will notice in the Argfile example I’ve given that you also need to escape the dot delimiter between the parts of the object name.
      /Exclude:View:[dbo] .[CustomerPurchases]
      The use of the argFile also means you avoid the potential problem of the ‘pipe’ character in a filename. This is not a legal character in a SQL Server object, but it is tolerated along with all sorts of whitespace characters in a delimited filename. It has to be delimited, of course because the regular expression engine interprets it as a logical OR. In the command line, the character must be escaped by the caret character ( ^ ), to prevent the operating system shell from interpreting it as the pipe operator.
      For further details see
      Command Line: Example Selecting Single Tables for Comparison
      Command Line: Example selecting tables with unrelated names

You may also like