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.
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.
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).
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