5

I have a need to determine if a database on a MS SQL Server has changed between two distinct moments.

The change can be structural or data-related and the check should be generic (i.e. independant of the structure of the database). Preferably, I'd like the check to be T-SQL based or with SMOs, not file based. I checked on MSDN but I haven't found anything relevant so far.

Charles Sprayberry
  • 7,741
  • 3
  • 41
  • 50
Locksfree
  • 2,682
  • 23
  • 19
  • Change like ANY change? You could check transaction number. – TomTom Mar 24 '10 at 12:10
  • Yes like in any change. An insert/update/delete in any table, or an alter table, changed index etc. Kind of a timestamp / rowid but for the whole database. – Locksfree Mar 24 '10 at 12:13
  • Check question http://stackoverflow.com/questions/2423909/is-there-meta-data-i-can-read-from-sql-server-to-know-the-last-changed-row-table/2424035#2424035 – Andrew Mar 24 '10 at 12:28
  • That could cover one side of it alright, by not restricting to a perticular table. But I think the usage statistics are lost upon server restart, so that may not work... – Locksfree Mar 24 '10 at 12:32
  • Please see my answer to this: Not sure if it will help: http://stackoverflow.com/questions/12055061/how-to-determine-if-a-database-has-been-altered-for-access-sql-oracle-or-file – StarLordBlair Sep 20 '12 at 13:51

4 Answers4

4

A possible solution for the scenario you described is to read the database transaction log (an LDF file). Any changes, both on schema or data level that were committed against the database are recorded in the database transaction log. Now, how to read the information that's in the t-log?

You can use either native SQL Server functions fn_dblog, DBCC PAGE or fn_dump_dblog or some 3rd party tool. However, the native functions are not documented and it's very hard to understand the results they provide. As for a 3rd party tool, you can check the Open LDF file and view LDF file content online article for more details and deeper analysis of what it takes to read the transaction log information

Disclaimer: I work as a Product Support Engineer at ApexSQL

Ivan Stankovic
  • 1,602
  • 18
  • 13
2

For SQL Server 2005 and up you can add a DDL trigger, like:

CREATE TRIGGER [YourDatabaseTrigger]
ON DATABASE
FOR DDL_EVENTS
AS

DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    VALUES (GETDATE(),SUSER_NAME()
                     +'; '+@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '+@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '+@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
           )
RETURN
GO

ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE

You would then need to create an triggers (for INSERT/UPDATE/DELETE) on each table in the database that would insert into the same table:

CREATE TRIGGER YourTableTrigger On YourTable
FOR INSERT
AS

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    SELECT GETDATE(),SUSER_NAME()
                     +'; INSERT YourTable'+
                     +'; data='+...your column data here...
    FROM INSERTED
GO
KM.
  • 101,727
  • 34
  • 178
  • 212
1

Red Gate make two products that might interest you:

They can compare the current version of the database with a backup copy and find changes in the schema or data respectively.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • We actually use SQL Compare and it's a very nice product. But here I meant programatically, either in T-SQL or from the application server using SMO. – Locksfree Mar 24 '10 at 12:15
1

For structural changes, you probably might want to consider logging DDL events on your server by using DDL triggers or Service Broker. However, identifying data changes might be much more difficult to achieve unless you have something to compare to. I can think of Database Snapshot as a possible solution (requires Enterprise Edition).

Darnell
  • 824
  • 5
  • 10