Delta Blues

I don't know what your DBA life is like, but at my place at any given time only one gigantic database garners the majority of the development activity. I'm not directly involved in the development -- they don't fetch me into the design meetings nor copy me on design specs -- but I would still like to be aware of what changes are transpiring in the database (if anything to have some idea where to look when something suddenly goes awry). The quiet way to do this is to "delta" the database, comparing the current version to the database from say a week ago. To pull this off neatly (without having another copy of the gigantic database sitting around glomping up disk space) you need a couple of tricks and a good comparison tool.

First you should make a baseline "empty" copy of your production database -- a full copy without any data in the tables. From SSMS right click on your database, go to tasks, and then script database. When you get to the wizard page that specifies an output file location, after you type in a savefile name click on the Advanced button. Scroll down to where it has Create USE Statement and uncheck it. Also make sure the Script Triggers box is checked.

Now that you've got the whole thing scripted, create a new database with rather small file allocations (yeah you need to mimic the filegroup structures too) and while attached to your new database open up that sql query you just saved. Double check to assure you're attached to your new database and run your script. Boom, a new empty baseline database. Note though sometimes the sequence SSMS originally creates the script violates some dependencies between objects: you may have to run the same script over again so it actually creates everything.

A week of work goes by and now it's time to run a comparison. If you don't already have a commercial product then you will need to download a database comparison tool; several exist but I recommend Open dBdiff. It's especially nice since it comes with the source code to allow you to easily make small tweaks to change colors, button locations, and whatnot.

Once you run the comparison you will need to examine the change in each of the elements that differ. By clicking the checkbox in front of those that are significant, the tool builds the script to "true up" the versions. When you finish your review click on the Synchronized Script tab, and then the saveAs button to store the delta script to a file folder.

The next week attach to your previous-version database, run the script that was the delta from last week, and then open your comparison tool and delta your production database again. Now you have the changes for this week. If you do this every week, then after a while you have a whole history of changes. Working from the original fully scripted baseline database you can execute successive deltas to bring the empty database up to any particular point in time (well, within a week or so).