How to test for database schema differences

Nearly every minor and major release of our product ends up with changes to the database.  Sometimes to the schema, sometimes to the data, sometimes both. Schema changes are obviously important to be aware of from a testing perspective, so a tool to catch these changes is needed in the testing toolbox. When working with with products that use SQL 2000, I use two tools to detect the changes: SQL Enterprise Manager and a file differencing tool (I use Beyond Compare). Detecting and analyzing changes using the two tools is quite simple and only takes a few minutes. Here’s how:

The magic starts with Enterprise Manager’s feature “Generate SQL Script”. Using this feature outputs the selected database to a SQL file in DDL. The first step in the process is to take a snapshot of your existing database using this feature. To do so:

In Enterprise Manager right-click the database, select item “Generate SQL Script”:


The Generate SQL Script window will open, on the first tab labeled “General” check the Script all objects checkbox:

On the 2nd tab labeled “Formatting”, select the following checkboxes:

  • Generate the CREATE <object> command for each object.
  • Generate scripts for all dependant objects.
  • Include extended properties.

On the 3rd tab labeled “Options”, select the following checkboxes:

  • Script database

  • Script object-level permissions

  • Script indexes

  • Script full-text indexes

  • Script triggers

  • Script PRIMARY keys, FOREIGN keys, default and check constraints

You’re all configured to dump the important stuff now (you deem what its important for your product though, feel free to check something that you think applies to your database change). Now that you’re configured, click the “OK” button and you’ll be prompted to save the file. Save the file. You now have a snapshot of your current schema!

With the snapshot of your schema in place, you can now conduct your database upgrade or whatever database altering voodoo that the developer has asked you to validate. Once the upgrade is complete, run the above set of instructions again to create a DDL dump of your newly modified database. Once the .sql file is saved, it’s time to view the differences, do this using your file differencing tool. The differences in the report that your diff tool creates are obviously the differences between database schemas. Create test cases accordingly…

Unfortunately, SQL 2005 has ruined this feature as far as I can tell. The feature exists but the output is wildly different. Differences in schemas create DDL files that are far different and hard to line up. This appears to be due to some new ordering of how things are created for the DDL. Needless to say, I’m in the process of finding a good tool or work-around trick for this issue.

One Response to How to test for database schema differences

  1. Tom Pester says:

    Check out

    It scritps schema and data. Maybe the ouput is usefull when using beyond compare


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.