Open DBDiff for SQL Server 2008

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]

saving the file to a folder

Fig 1.0

saving the file to a folder

Figure 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]

extract the setup.rar

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]

start the installation of the Open DB Diff utility

Fig 1.3

start the installation of the Open DB Diff utility

Fig 1.4

start the installation of the Open DB Diff utility

Fig 1.5

start the installation of the Open DB Diff utility

Fig 1.6

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

Click on the close button to finish the installation

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]

locate the program files

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’)

create some databases and database objects

Fig 1.9

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

launch the open DBDiff utility

Fig 2.0

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

DBDiff utility launches

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]

Type the Servername and instance name
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

click on the

Fig 2.4

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]

click on the options button

Fig 2.5

click on the options button

Fig 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”

execute the database comparison steps

Fig 2.7

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

Previous article
Next article

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles