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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 18, 2001

SQL Compare Review

By Robin Abramson

Red Gate Technologies

SQL Compare by Red Gate Technologies is a handy little program to compare Microsoft SQL 7 Databases. It shows the structural differences in tables, views, stored procedures, triggers, users, rules, roles and user defined types and allows you to generate scripts to synchronize the databases.

Installation of SQL Compare was easy. I installed it on a Pentium III 600 with 256MB of RAM, but the requirements are much less, with only a Pentium 166, 32 MB of RAM (64 MB recommended) and 10 MB of free hard drive space required.

SQL Compare doesn't use ODBC, so you'll have to have the SQL Server Client Tools installed on any PC you wish to run it on. Comparing databases is simple. When you open the program, a pop-up window asks you if you wish to open a previously saved workspace or compare two databases. You can then register both databases at once of you wish to run a comparison, or just one if you want to save a snapshot to work with at a later time.

Once the database is registered, you can save the definition to a workspace file, which is named by date and time. This is useful because you can make changes to database schema and then look at your saved definitions over time and create change scripts as necessary. To compare two databases, you click on the "compare" toolbar and a window opens which allows you to choose your databases from a list of saved workspaces.

Scroll down to the saved workspace definitions you want to compare and click Ok. SQL Compare runs scripts to compare the two databases and opens another window to show you the differences between the two.

The arrows show you which objects either exist in one database but not the other or are different from each other but exist in both databases. It couldn't be simpler to see if all your database objects are equal in both databases. To see the actual SQL behind the objects, just right click and choose view SQL. A screen pops up which gives you the option of viewing the variances or creating change scripts to synchronize the databases.

In the first version of SQL Compare, options were limited. With this latest version, you can include or exclude several options in your comparisons- including triggers and permissions. If you wish to generate change scripts to make the two databases match, you select the migration tab you must return to the compare screen and choose which objects you wish to script and then hit the run button. The scripts are generated for you automatically. This is a little awkward and it would be nice if future versions allowed for a way to just click on the migration window (perhaps a split screen?) and choose objects from there. You then can copy and paste them into Query Analyzer to bring your databases into sync.

SQL Compare also gives you several options in deciding what to compare between databases. You can:

Ignore comments.
Ignore CR/LF.
Ignore tabs.
Ignore spaces.
Ignore object permissions.
Ignore script case.
Ignore set statements.
Include indexes.
Include fill factors.
Include keys.
Include checks.
Include triggers.
Ignore object order.

For a simple, inexpensive tool for quick comparison of databases, SQL Compare is a powerful utility. A single user license is only $50, with a site license available for $300. (Red Gate offers a discount for PASS members; see the PASS Member Promotions Page for details.) Red Gate may include the ability to compare data as well as schema in future releases -- which would make this tool a must have. Red Gate is now also offering SQL Compare as part of a bundle (optionally) which includes SQL Data Compare (to compare the data in database tables) and COMpare (to compare ActiveX interfaces). A free 14-day demo version of SQL Compare is available from Red Gate Technologies.

MS SQL Archives

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