Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 7, 2009

Open DBDiff for SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM