The DBDiff utility compares two databases and generates a script to synchronize the two databases. In this article, MAK illustrates the various uses of the Open Diff utility.

Open DBDiff 1.0 is the utility provided by Microsoft via CodePlex. You may have used the Open DBDiff utility for SQL Server 2005 before. DBDiff utility compares two databases and generates a script that could be used to synchronize two databases. The Open DBDiff utility is in beta version 8. This article is going to illustrate the various use of the Open Diff utility.

Download

Let's download the utility from the codeplex website. Please download the Beta 8 version of Open DBDiff utility.

When the "File Download" popup window appears, click the save button and save it to a folder. In this illustration, I am saving the file to the folder "opendbdiff" under Windows desktop. [Refer Figure 1.0, 1.1]

Now, extract the setup.rar file using any decompression utility like Winzip, Winrar, etc. This will extract two files--Setup.exe and Setup.msi. [Refer Fig 1.2]

Installation

Double click on the setup.exe. Now, click on the "Next" button for the next three popup windows. That will start the installation of Open DB Diff utility. [Refer Fig 1.3, 1.4, 1.5 and 1.6]

Click on the "close" button to finish the installation. [Refer Fig 1.7]

If you have a 64 bit OS like Vista, the program files for this Open DBDiff utility can be located under C:\Program Files (x86)\Open DBDiff. If it is a 32 bit, OS then the program files will be located under C:\Program Files\Open DBDiff. [Refer Fig 1.8]

Prep-Work

In order to illustrate all the functionality of this utility, let's create some databases and database objects as shown below. Execute the following scripts in SQL Server Management Studio. [Refer Fig 1.9]

Create Database TestDB1
go
Create Database TestDB2
go
Use TestDB1
go
Create Schema TestSchema
go
Create table TestSchema.TestTable(id int, name varchar(100))
go
insert into TestSchema.TestTable values (100, 'TestA')
insert into TestSchema.TestTable values (100, 'TestB')
insert into TestSchema.TestTable values (100, 'TestB')
go
Use TestDB2
go
Create Schema SalesSchema
go
Create table SalesSchema.TestTable(id int, name varchar(100))
go
insert into SalesSchema.TestTable values (100, 'TestC')
insert into SalesSchema.TestTable values (100, 'TestD')
insert into SalesSchema.TestTable values (100, 'TestF')

The above script created two databases, TestDB1 and TestDB2, with TestSchema and SalesSchema respectively. It also created a table with a few rows on each database.

Launch

Now let's launch the open DBDiff utility. Navigate to C:\Program Files (x86)\Open DBDiff and double click DBDiff.exe as shown in Fig 2.0

This will launch the DBDiff utility as shown below. [Refer Fig 2.1]

Type the Servername and instance name on the "Server Host" text box and select the database names for the Source and the Destination database as shown below. [Refer Fig 2.2]

Note: I am using Windows authentication to connect to SQL Server. You could use Windows authentication as well. Click on the "Test Connection" button before comparing databases.

Now click the button "Compare". You could see that DBDiff utility started comparing the database object by object. Now, click on the "Synchronized script" tab to see the generated script. This script will drop the schema and its objects in TestDB2 and create Schema and object related to TestDB1. Refer Fig 2.3

USE TestDB2
GO
 
DROP TABLE [SalesSchema].[TestTable]
GO
CREATE SCHEMA [TestSchema] AUTHORIZATION [dbo]
GO
CREATE TABLE [TestSchema].[TestTable]
(
      [id] [int] NULL,
      [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
 
DROP SCHEMA [SalesSchema]
GO

There are other options available in this utility. Click on the button "Options". This would launch a window with a few tabs where you could filter certain objects and handle case sensitivity. [Refer figure 2.5, 2.6]

You can save the script by clicking the "Save as" button or if you want to copy the content of the script to clipboard, click the "Copy Clipboard" button.

Note: This is the beta version of the DBDiff utility. There may be bugs or there may be more enhancements at a later date. This beta version was released on Aug 22, 2009. The official release date is not known.

The open DBDiff utility comes with a small command line utility that you can use at the command prompt.

The syntax for calling the OCDB.exe is:

OCDB CN1="Connection String 1" CN2="Connection String 2" F=FileDestination.sql

Execute the illustrated Database comparison steps by executing the following command. [Refer Figure 2.7]

C:\
CD "C:\Program Files (x86)\Open DBDiff"
OCDB CN1="Data Source=PowerPC\SQL2008;Initial Catalog=TestDB1;Integrated Security=SSPI;" 
  CN2="Data Source=PowerPC\SQL2008;Initial Catalog=TestDB2;Integrated Security=SSPI;" F="C:\test.sql"

Conclusion

This article illustrated the various uses of the Open DBDiff utility. It also illustrated the use of command line OCDB.exe.

» See All Articles by Columnist MAK