Keeping track of database schema changes

Posted on March 25, 2008 20:43 by mcollins

While I'm developing, I'm always making changes to code and data, and not necessarily keeping very good or accurate records in the heat of the moment about what I'm changing.  Source code is easy to resolve using modern version control tools, because I can always run a diff to see the changes that I made.  But what do I do about databases?  If I ran some ALTER statements in the database, what happens if I accidentally closed the database session and lost my changes, or if I want to go back and review what I did at the end of an iteration?  What do I do then?

Fortunately, using a trick with triggers in SQL server, I can let SQL Server keep track of the changes that I make in my database.  In my databases, the first thing that I usually create is a table for storing DDL changes to my databases and a trigger that automatically populates the table when DDL statements are executed.  By using the table and trigger, I can go back at any point and review all of the changes that were made to the database.

The table that I usually define is below:

   1: CREATE TABLE dbo.DatabaseModification (
   2:     DatabaseModificationId INT NOT NULL IDENTITY(1,1) CONSTRAINT
   3:         PKNC_DatabaseModification PRIMARY KEY NONCLUSTERED WITH
   4:         FILLFACTOR = 90 ON "default",
   5:     EventType NVARCHAR(256) NOT NULL,
   6:     PostTime DATETIME NOT NULL,
   7:     Spid INT NOT NULL,
   8:     ServerName NVARCHAR(256) NOT NULL,
   9:     LoginName NVARCHAR(256) NOT NULL,
  10:     UserName NVARCHAR(256) NOT NULL,
  11:     DatabaseName NVARCHAR(256) NOT NULL,
  12:     SchemaName NVARCHAR(256) NULL,
  13:     ObjectName NVARCHAR(256) NULL,
  14:     ObjectType NVARCHAR(256) NULL,
  15:     TSqlCommand NVARCHAR(MAX) NOT NULL,
  16:     Note NVARCHAR(MAX) NOT NULL DEFAULT N''
  17: ) ON "default";

 

Most of these fields are populated automatically from information gathered by the trigger.  The Note field was added by me.  This field allows me to annotate the changes in the database while I'm reviewing the database changes.  More and more often, I find that I'm adding this table to my database definitions and including the table in the production databases.  On my applications, I usually also create an administrator user interface for this table because I think that it's handy for a system administrator of an application to be able to look at what changes occurred to a database if something breaks or goes wrong for no apparent reason.  The administrative UI also allows the administrator to edit the contents of the Note field.

Here's the source code for the trigger that populates this table:

   1: CREATE TRIGGER trgDatabaseModificationInsert ON DATABASE FOR
   2:     DDL_DATABASE_LEVEL_EVENTS
   3: AS
   4:     SET NOCOUNT ON;
   5:     
   6:     DECLARE @data AS XML;
   7:     SET @data = EVENTDATA();
   8:     INSERT INTO dbo._zsDatabaseModification (EventType, PostTime, 
   9:         Spid, ServerName, LoginName, UserName, DatabaseName, 
  10:         SchemaName, ObjectName, ObjectType, TSqlCommand) VALUES (
  11:         @data.value('(/EVENT_INSTANCE/EventType)[1]', 
  12:             'NVARCHAR(256)'),
  13:         @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME'),
  14:         @data.value('(/EVENT_INSTANCE/SPID)[1]', 'INT'),
  15:         @data.value('(/EVENT_INSTANCE/ServerName)[1]', 
  16:             'NVARCHAR(256)'),
  17:         @data.value('(/EVENT_INSTANCE/LoginName)[1]', 
  18:             'NVARCHAR(256)'),
  19:         @data.value('(/EVENT_INSTANCE/UserName)[1]', 
  20:             'NVARCHAR(256)'),
  21:         @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 
  22:             'NVARCHAR(256)'),
  23:         @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 
  24:             'NVARCHAR(256)'),
  25:         @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
  26:             'NVARCHAR(256)'),
  27:         @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
  28:             'NVARCHAR(256)'),
  29:         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
  30:             'NVARCHAR(MAX)'));
  31:     
  32:     SET NOCOUNT OFF;
  33: GO

 

By creating this trigger for DDL_DATABASE_LEVEL_EVENTS, any DDL statements that would modify the database by creating, altering, or deleting database objects will get logged in the DatabaseModification table.

Technorati tags: , , , ,


Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: , , , ,
Actions: E-mail | Comments (2) |dzone it!

Related posts

Comments

March 26. 2008 00:48

Very nice tip.

First of all I learned something new about triggers that it can be created for the whole database. I was under the impression that triggers are for tables only. Is this a new feature in SQL Server 2005?

I normally put my ddl statements in source control. But I can see how this tip could help even if you source control your ddl statements. It gives you a exactly what changes were made to the db. It will help you to debug production issues due to db schema mismatch with what the application is expecting.
|

britto

March 27. 2008 07:38

@ britto:

This could be a new feature as of SQL Server 2005. I don't remember it in earlier versions.

I agree with you about version control. I'm a firm believer of keeping the SQL schemas in version control. I like this technique too because, should you be doing things adhoc while you're tinkering with some new code, you can go back and review the information in the DatabaseModification table to make sure that your schema definition file is updated properly.
|

mcollins

Add comment


 

[b][/b] - [i][/i] - [u][/u]- [quote][/quote]



Live preview

November 20. 2008 20:56

|