Scanning a set of snapshots for a particular change

weswinkler

I have established a routine daily task which takes SQL Compare 9 snapshots of a database, and stores them nicely in a folder. E.g., MyDB_20110601.snp, MyDB_20110602.snp, ..., MyDB_20110629.snp.

Occasionally, I manually check the two most recent (to find daily changes) or the most recent with the oldest (to find cumulative changes).

I miss my daily check sometimes (weekend, vacations, too busy, etc.). And I now find that sometime between June 14th and June 29th, an important object has changed. (A view named MyFavoriteView was somehow dropped.)

Is there a way to programmatically work through a sequence of snapshot (*.snp) files and find the first one where a particular changed occurred?

The use case would be: given a known named object, find the two adjacent snapshots where the known object differs. I'd like to see when the object first appears, when it is changed, or when it first disappears.

Any suggestions would be appreciated.

David Atkinson

The trouble with using snapshots for this use case is that they're hard to search for changes. Using the SQL Comparison SDK this should be possible, but I'd urge you to consider building a history of changes in source control instead of as snapshots. All you need for this is a source control system. We can provide you with an example batch file that detects changes and commits them to source control.

This means you can benefit from all the auditing functionality that already exists in source control tools.

David Atkinson
Red Gate Software

weswinkler

We already have a source control system, and I can easily identify when and if changes were made to the overall definition of the database. I'm trying to determine when a particular object change was actually made to my database, not necessarily when changes were made to definitions in source control.

I'm working in an test integration environment. Not all changes made in source control are immediately applied to this database. Developers work on a series of incremental changes on their own copies of the database, and when they are ready, we deploy the group of changes to this test database all at once.

Also, some changes may be made on an ad hoc basis. In this case, the snapshots would help uncover an unintended change. A database user (tester or developer) may have unintentionally changed an object. Whoops!

I'll look deeper into the SQL Comparison SDK.

Thanks for your response.

David Atkinson

Hi,

I've emailed you a batch file that should help you. Let me know if you don't get it (it might get send to 'junk').

Let me know how you get on.

David