Bill Wunder's DDL Archive Utility

Using the Utility's Data

There is a significant amount of useful information available in the Archive Utility and in the output produced by the utility.

All changes are logged

Every item that is added, changed or destroyed by the utility in SourceSafe is logged to the table admin.dbo.ArchUtilChanges. This table can be used to identify:
  • whether an item has changed
  • which day(s) an item changed
  • the number of items that changed on a particular day or over a date range.
  • the number of items of a particular type (stored procedures, tables, jobs, etc.) that have changed/
  • if and when the Archive Utility Configuration options were changed

Change detail history stored in SourceSafe

To view the details of a particular change use the SourceSafe GUI to view the history of an interesting file and then display the differences between selected check in versions of that file. Please see the SouceSafe documentation for complete information regarding the use of the User Interface.

Any Archive Utility configuration changes are also stored as files in SourceSafe. These files are always located in the project folder for the SQL Server.

  • The Archive.log file contains a complete listing of all activity for a particular execution of an Archive Operation. There is a new version of this file generated in SourceSafe each time the Utility is successfully executed.
  • The Archive.xml file contains the configuration data used by the Utility. Each time the configuration is changed a new version of this file is generated in SourceSafe.

Writing queries to report on logged changes

Queries against the log data stored on the SQL Server can be easily grouped and/or sorted by object type (stored procedures, tables, jobs, etc.) and database as well as by datetime using standard T-SQL queries.

A few example stored procedures are installed with the Archive Utility:

  • admin.dbo.ArchUtilChangesByDate - produces a complete listing of all changes that have occurred. The most recent changes are at the top of the listing.
  • admin.dbo.ArchUtilChangeActionsByDate - produces a count of the number of changes that occurred in SourceSafe by action type where action type is any of "Add", "Change", "Delete". The most recent changes are at the top of the listing.
  • admin.dbo.ArchUtilDailyChangeReport - produces an email of all changes that occurred between daily runs of the Archive Utility. (Note that this procedure uses an alternative email process rather than xp_sendmail. It's an easy modification to convert it to SQL Mail and perhaps a wiser though equally easy system change to convert your server to the alternative smtp based email system I use. Please contact me for complete details on the latter option.)