Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 244 of 361 Next >

Comparing Two SQL Server Databases

Last updated Mar 28, 2003.

The technical professionals working in Database Management have multiple disciplines such as T-SQL coding, database design, security and platform issues, disaster recovery and others. You can look at the tasks asked of these professionals from two vantages: data tasks and management tasks. Data tasks involve things like programming and Business Intelligence, and management tasks involve things like backups and security.

No matter what the discipline, however, there is one need that most every database professional deals with at some time in their career: comparing two SQL Server databases to find changes or differences. There are many reasons that this task keeps coming up. In some cases the technical professional works in a development shop, either internally or commercially. The need here is pretty obvious — you have to be able to track the changes between product releases to upgrade one software version to the next. Making these change scripts and keeping the order and precedence straight can be very difficult.

Even if you don't develop in your shop, you may have to keep a copy of the schema lying around in case you need to write reports against the data in the commercial products you have that use SQL Server as a back end. When you get an upgrade to that product, it might break the reports you've written if the schema changes. Having a way to compare the schemas against each other gives you ability to track and fix your reports if that happens.

You also might be in a support or consulting role. If you've dealt with users much, you already know that the first question you ask when you hear about a problem is, "Was it working before? What changed?" and you also know the answer is "Yes. And nothing has changed. It just quit working." I've visited many sites where "nothing" has changed, and yet I find massive alterations to a commercially "sealed" database.

So it would seem with all these obvious needs to track database changes that it would be a common practice. Development professionals have a multitude of change management tools they use to track and control their code. It's even built-in to several design studio tools to integrate source-control into the development process. But it is not as common to see the schema of a database checked into or out of that source-control system. Many people, even technical professionals, believe that the database is a sealed product, something that can't or won't be altered by the end user. But it happens all the time.

Relational database systems aren't off the hook in this process either. Most don't offer any native way to track changes to the structure of the database, in a comprehensive way. Most of the time you don't "version" a database. You can enter a number into a table to do so, but the users are often free to edit that table at will, if they have DBA rights, so you really can't trust that number. You're still left with a need to compare two databases for differences.

There are commercial products that you can use to compare databases. The two I've used are Red-Gate's SQL Compare, and Idera's SQLConfig. Both work great, and the Red-Gate solution only costs a few hundred dollars (US) to buy. These are ideal because you just make a few selections and click a "Go" button. The scripts that each tool creates work almost every time, and they even keep the precedence and order correct automatically. Both will even implement the change with the click of a button. If you decide to purchase a solution, read on to find out the basic way they work and how you should use them.

If you decide to write your own comparison tool, you have several choices. In any case the basic process follows the same outline:

Read the database parameters and database objects
Script the Data Definition Language (DDL) statements such as CREATE, DROP and so forth for each object to a file or memory location
Repeat the process for the second database
Order the objects to line up with each other
Run a text comparison tool to find any differences
Create DDL scripts to implement the changes
Order the DDL scripts

It looks difficult, but up until the last two steps it's really quite easy to do. You don't have to buy anything, and in some cases you don't even have to code anything. Let's take a look a few methods you have to follow the outline.

First, you need to read the database objects. You can do that with Enterprise Manager in SQL Server 2000. You just right-click each object and then select All Tasks, then Generate SQL Script:

Figure 214

Of course, right-clicking each object and creating scripts on the screen is a bit too much work to do if you have more than a few objects. Fortunately, there's a much easier solution. In Enterprise Manager, just right-click the name of the database and click All Tasks and then Generate SQL Script:

Figure 215

You have multiple options that you can set in each tab. In the second tab you set formatting options to include things like descriptive headers and CREATE and DROP options:

Figure 216

In the last tab you can select options to create permissions and indexes:

Figure 217

SQL Server 2005 works in much the same way, but the same selection (All Tasks and then Generate SQL Script) brings up a full Wizard-driven interface with even more options:

Figure 218

Even though there is a wizard that will generate scripts for you, there's an easier way. In a previous series I explained how to use the Data Management Objects (DMO) engine and some simple scripts to call the same process that Enterprise Manager uses. You can use those scripts to create any kind of file you want and call any or all of your database objects. After all, why compare user-created tables to your commercial product? You can use DMO or the newer, more powerful Server Management Objects (SMO) in SQL Server 2005 to do the same thing. Whether you call the scripting engine manually or by using a little coding you can create your scripts.

So that takes care of steps one and two — getting the objects and saving them out to a script. For step three you just repeat the process on the other database, or change your scripts to point to the other database name.

For step four, if you use the scripting method you have more control over the process and can line up the objects automatically by calling the objects to be scripted in the same order. If you use the graphical tools, you'll need to ensure that the order of each object is the same, otherwise the next step won't work properly.

After you've created your scripts, you need to compare each line to see if there are any differences. I normally use a free text-comparison tool such as SED, but I'm an old UNIX guy and I like the command line. If you're more comfortable with a graphical tool, there are several out there, some for free and some not. There are a couple of potential issues here. For one, if you got your objects out of sequence, you'll get all sorts of false-positives. Lots of lines won't match if you're off by just one. The other problem is in the robustness of the comparison. You need to make sure that case doesn't matter, that it doesn't check for white space and that sort of thing.

When you get your differences file, which hopefully isn't very long, you can create your ALTER, DROP or CREATE statements to bring the second in line with the first. You'll have to be careful here, because changing a column might affect the data it contains or it may violate a constraint or relationship.