How to determine whether a database has been changed or not?

Question

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.

Answer 1

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

Answer 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()
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '[email protected]('(/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