如何确定数据库是否已更改?

财务日志 , DBCC页面 fn_dump_dblog文件 Open LDF file and view LDF file content 在线文章获取更多详细信息,并深入分析读取事务日志信息所需的内容

对于SQL Server 2005及更高版本,可以添加DDL触发器,例如:

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

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

红门有两种产品可能会让你感兴趣:

• SQL Compare
• SQL Data Compare

它们可以将数据库的当前版本与备份副本进行比较,并分别查找架构或数据中的更改。

DDL triggers Service Broker . 但是,除非您有可比较的东西,否则识别数据更改可能要困难得多。我能想到 Database Snapshot