How to determine if a database has been modified?

Question

I need to determine if the database on MS SQL Server has changed between two different points.

The change may be structural or data-related, and the verification should be general (i.e. independent of the database structure). Preferably, I would like the check to be based on T-SQL or with SMO, rather than file based. I checked MSDN, but so far have not found anything suitable.

Answer 1

A possible solution for the scenario you described is to read the database transaction log (LDF file). Any changes, both in the schema and at the data level that were transferred to the database, are recorded in the database transaction log. Now, how to read the information contained in t-log?

You can use either native SQL Server functions fn_dblog , DBCC PAGE , or fn_dump_dblog or some third-party tool. However, native functions are not documented, and it is very difficult to understand the results they obtained. As for the third-party tool, you can check the Open LDF file and view the contents of the LDF file online article for more details and a deeper analysis of what is required to read the transaction log information.

Disclaimer: I work as an ApexSQL product support engineer

Answer 2

For SQL Server 2005 and above, you can add a DDL trigger, for example:

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() +'; ' [e-mail] ('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)') +'; ' [e-mail] ('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)') +'; ' [e-mail] ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') ) RETURN GO ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE 

Then you need to create triggers (for INSERT / UPDATE / DELETE) for each table in the database that will be inserted 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 

Answer 3

Red Gate offers two products that may interest you:

• SQL Compare
• SQL data comparison

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

Answer 4

For structural changes, you probably want to consider registering DDL events on your server using DDL triggers or Service Broker . However, identifying data changes can be much more difficult if you have nothing to compare with. I can think of a Database Snapshot as a possible solution (Enterprise Edition required).