July 11, 2014

How to compare SQL Server schema from a command prompt

If you have multiple SQL Servers and need to keep the schema for those servers synchronized this article will show you an easy way to do it. You will need to download and install xSQL Schema Compare. The application comes with a 2 week trial. This application is very handy when comparing databases. Besides the Windows application it also has a command line program. Using this command line program you can create scripts to automate database comparison. But the command line program requires the creation of an XML file and you are limited to one database comparison per XML file. If you have multiple servers with multiple databases you will need to create multiple XML files. Using the following script the XML files will be created for you automatically.

What is required:
  1. xSQL Schema Compare (tested on version 5) – You can find it here.
  2. The batch script REPL.bat from Dave Benham. You can find the script here.
  3. My script SchemaCompare.bat. You can find it here.
  4. My XML template file. You can find it here.
Steps to do the comparison:
  1. Install xSQL Schema Compare.
  2. Copy the files from #2, #3 and #4 from the previous section into the same folder.
  3. In a Command prompt type:

SchemaCompare.bat [Source Server] [Target Server] [Database List]

Example:

SchemaCompare.bat SrcServer TargetServer “DBName1,DBName2,DBName3”

After the program finish you will see a new folder with the name “SrcServer-TargetServer”. It will have the results from the comparison. For every database you will see four files. One is a “log” file that has a summary of the comparison. This is probably the first file you should check to see what differences if any were found. Then there is a “sql” script file. This file has the SQL commands to apply to the TargetServer to synchronize the schema. There is also a “warnings” file that will show any schema warnings. If the program found any errors then you will also see and “error” file.

Warning about replicated tables:
xSQL Schema Compare by default will not compare replicated tables. But I wanted to compare all tables. In my XML template you will see the option “CompareReplicatedTables” under the section “SelectedComparisonOptions”. If you don’t want to compare replicated tables you will have to remove that option. Be aware that you may encounter errors if you try to modify the schema for a table that is replicated.

I hope this program is helpful for you. Let me know if you have any suggestions on how to improve it.