SQL Server Nightly Change Logs

I’ve been slowly working on getting my current employer up to "enterprise"-level development. Requirements specs, repeatable processes, redundancy, failover, flow documentation, etc. A version control server (svn) has been set up and is being populated. But one of the things that has been bugging me is that the main product (SMART) is being deployed internationally, but there’s no revision control for the database itself. I wasn’t thinking about some magical nebulous system — I really just wanted to be able to see a list of database changes at the end of each day. That way, if Mexico or China suddenly stopped working, and no code had been changed, I could look and see if the database had changed.

Today, I solved that problem with a 10-line batch file:

@echo off
set d=%DATE%
set dd=%d:~10,4%%d:~4,2%%d:~7,2%
if exist smart-yesterday.sql del smart-yesterday.sql
if exist smart-latest.sql move smart-latest.sql smart-yesterday.sql
scptxfr.exe /s localhost /d smart /i /H /f smart-latest.sql
diff -U 2 smart-yesterday.sql smart-latest.sql > smart-%dd%-diff.txt
for %%f in (*-diff.txt) do if %%~zf == 0 del %%f
if exist smart-%dd%-diff.txt copy smart-latest.sql smart-%dd%.sql
if exist smart-%dd%-diff.txt svn commit -m "automated nightly diff"

The gist is something like this:

  1. Generate a file that contains all of the SQL needed to (re)build the database.
  2. Compare that file to yesterday’s version of the file.
  3. If anything has changed, save it and commit it to the Subversion repository.

From the top … I wanted to be able to quickly see when changes occurred, so I set an environment variable (dd) to the current date in YYYYMMDD format. If your server’s date format is different than mine, you’ll need to do a bit of tweaking. However, I don’t want a ton of unchanged files cluttering up my server, so I only want to save those files if something has actually changed. The if exist statements rotate yesterday’s file into the correct place.

The scptxfr program comes with SQL Server and does the same thing as right-clicking on an object in the Enterprise Manager and selecting "Generate SQL Script". You give it a server (/s) and a database (/d) and it will put the script into a file (/f). The /H switch tells it to eschew the comments, which are timestamped and would mean that there would be a difference every day, which would be useless. I know mySQL has a similar SQL-dump facility, and presume that other DBMSes have their own.

I then diff today and yesterday into a dated text file. The for statement removes any diff files that are empty (no changes). The if commands, which could be combined, will copy the latest version into a dated file and commit the changes to the repository. Notice that I am not adding the dated file to the repository — the repository automagically handles tracking changes, so there’s no deed. I just want a dated one so that I don’t need to fire up the SVN client to see what the changes are.

Set up the batch file to be run every night via the scheduler (or at, if you are into pain).

Et voila! Automated change tracking for your database!

By Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.