Compare SQL Server Databases with sp_CompareDB


H. The behavior of the SQL variant data type is quite specific when you compare data of different data type families. However, when using my SP, we don’t suppose to compare different data types to determine what is bigger (and get eyebrow-raising results when 1 is bigger then ’9999′), we need only two- state replies: “equal/not equal”. Our concern is:

1. not to get “equal” when data are different in fact
2. not to get “different” when data are equal in fact

The first mistake would be possible if a SQL_variant is converted during the comparison. However, the SQL_variant data type families are always strictly ranked in comparison operations, and as a result, for example, binary 0×61 will never equal char(1) ‘A’. Excellent!

Moreover:

CREATE TABLE ChkSQLv (CharSQLvCol SQL_variant, BinarySQLvCol SQL_variant) 
INSERT INTO ChkSQLv VALUES (CONVERT(char(1),’A'),CONVERT(Binary(1),0×61)) 
SELECT * FROM ChkSQLv WHERE ISNULL(CharSQLvCol,’!#null$’)=ISNULL(BinarySQLvCol,’!#null$’)


will return nothing as well as:

SELECT * FROM ChkSQLv WHERE BinarySQLvCol=CharSQLvCol

While:

CREATE TABLE ChkBinChar (CharCol char(1), BinaryCol binary(1))
INSERT INTO ChkBinChar VALUES (‘A’,0×61)
SELECT * FROM ChkBinChar WHERE BinaryCol=CharCol


returns the row!

A SQL_Variant null does not have a basic data type, therefore being a subject of ISNULL, it never produces “Converting error”:

DECLARE @SQLvCol SQL_Variant
SET @SQLvCol = CONVERT(int,null)
SELECT ISNULL(@SQLvCol,’!#null$’)


While this code produces the “Converting error” message:

DECLARE @IntCol int
SET @IntCol = null
SELECT ISNULL(@IntCol,’!#null$’)

  • A last item of that (incomplete) list of “Oops!” issues. A PK cannot contain column(s) with a null property, however UNIQUE constraint and unique index can. It’s rare, but nevertheless probable. Therefore, heavy constructions must be used to build key comparison strings as well.
  • How to Run sp_CompareDB Stored Procedure

    To make a long story short, it was quite a challenge not just to create a script comparing databases, but also to make it free of excessive restrictions, fast, and easy-to-run. All you need to install the sp_CompareDB stored procedure is to execute the script against the Master database. Then set the Query Analyzer option “Results in Text”) to get the result sets and comments on the same page).

    Once the stored procedure has been created, simply run:

    sp_CompareDB ‘<dbName1>’,’<dbName2>’

    And enjoy seeing the differences.

    If you are comparing a database from a linked server, then the command would be:

    sp_CompareDB ‘<LinkedServer1>.<dbName1>’,’<dbName2>’

    You can use the following parameters for the SP, besides the obvious db1 name (parameter name @db1) and db2 name (parameter name @db2).

    @TabList – comma separated list of tables to compare. If empty – all tables in the databases should be compared

    @NumbToShow – number of rows with differences to show. Default – 10. Be cautious increasing it. For tables with a unique key just the keys of rows with differences should be shown. But for tables without a unique key, all data for rows with differences are shown. After all it’s unlikely that somebody needs a boundless report to get lost in the ocean of digits and letters. If databases are completely different then there is no sense comparing them.

    @OnlyStructure – flag being set to 1 allows you to avoid data comparing. Only structures should be compared. Default 0.

    @NoTimestamp – flag being set to 1 allows you to avoid comparing of columns with timestamp data type. Default 0.

    @VerboseLevel – flag being set to 1 lets you see queries used for data comparison. They can be used easily to synchronize data in the databases if you need it. Default 0.

    A final note: This SP is absolutely safe. It only gets data from databases while not making any changes. The databases can even be in read-only mode. However, if you have big tables without a unique key, leave enough free disk space before running the SP. The space available must be at least twice bigger then the space consumed by the largest table in the databases.

    Published with the explicit written permission of the author. Copyright 2001.

    Pages: 1 2 3 4




    Related Articles :

    • No Related Articles Found

    One Response to “Compare SQL Server Databases with sp_CompareDB”

    1. So how do I create a @TabList to limit the comparison to the specified tables? I tried ‘table1, table2′, ‘dbo.table1,dbo.table2′. Nothing worked. The message I get is “Msg 8114, Level 16, State 1, Procedure sp_CompareDB, Line 0
      Error converting data type varchar to bit.”

    Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |