You may need to compare different development environments before pushing out the update script to production. Maybe you have on premise installations and you need to verify database changes will work as expected across your customer base. In any case you need to be sure that your database is in a state where the rest of your software can interact with it.
Do you really try to do this manually?
Whether you are concerned about the structure of the database or the data in the tables, this falls squarely into the land of not a good task for humans. Maybe you could spot check your tables if there are only a couple, but to be thorough you'll need a pretty deep understanding of databases and the changes being applied to your database.
What you need here is a tool, something to scan your database and report the differences. Just like you might diff two text files to highlight the differences only tailored to databases. In my case, I need to compare 2 Microsoft SQL Server databases, enter Open DBDiff.
I'll be honest, this probably isn't the best tool available but it does have a two key benefits:
It's Free Sometimes as testers we need a tool but we don't have the budget to go out and buy one. We may need to show the value of a certain test before we can get buy off from management on spending money on something. Or maybe free does all that you need and you don't need all the bells and whistles. Free means I can try something right now, no trials or sales calls just straight to testing.
It Compares Schema and Data There are lots of tools that can compare the structure (schema) of the databases. Sometimes though you need to go beyond comparing blueprints and compare the contents of tables.
In the future I may try out some other options for comparison, but for now Open DBDiff met immediate need. So lets take a look at actually using it.
Setup
Another nice thing about Open DBDiff is that there is nothing to install. You can just click the big purple download button on the projects codeplex site, extract the files from the zip and drop the resulting folder wherever you want to run it. No install means no bundled adware or spyware to worry about. If you don't like it, just delete it.
Once you've got the files in place launch DBDiff.exe...
Just fill in the details for the source and destination host servers and the necessary credentials. Once those are entered the database drop-down will automatically populate with the available databases.
Options
Before you get started comparing the databases you have the opportunity to customize the compare by clicking the Options button on the right side of the main screen.
The Filter By Type tab allows you to include or exclude what will be compared. So if you only want to compare tables and don't care if the column order is different, you can uncheck everything but the options you want in the tables section. It would be nice if there was an option to check/uncheck all though...
The Comparison tab has options for how the comparison will be handled.
Comparing
Once your settings are all the way you want, go ahead and click the Compare button. The app will then go out and gather the data from both databases. You'll see a progress bar as you wait, but it's reasonably fast, however I only used it on medium-sized databases. The more tables and data the longer the wait.
Once it is complete the schema information will be populated in the tree view on the lower left.
Schema
Schema differences can be seen for the entire database by looking at the Synchronized Script tab. Should there be any differences Open DBDiff generates a SQL script that can be used to make the databases match. By reading through the script you can quickly see the scope of the differences. It's not as easy as the nice highlighted view you often get from a diff view but it's nice to not have to go to each table individually to compare. It's important to keep in mind which server is the source and destination because it effects how the differences are presented. A missing table or trigger on a database could be listed as a create statement or drop statement depending on the source / destination configured.
Data
Data can only be compared on a table by table basis. You need to navigate the schema tree to find the table you want to compare. Once its selected the Compare Table Data button will be enabled. Once clicked you will see a side-by-side view of the table data.
For large data sets, across multiple tables this process is cumbersome. It's not perfect, but for a few tables you can quickly visually compare in a nice view.
