MS SQL Server数据库的活动日志

Question

我有一个拥有100多个表的数据库。我会持续地向已有的表中添加列（如果需要的话），也会添加一些新表。

现在，我想要检查我在过去三个月里做了哪些更改。MS SQL Server 2012 中是否有针对该特定数据库的活动日志来跟踪更改。

- Abdul

---

SQL源代码控制？ - ManOnAMission

@ManOnAMission 怎么做？ - Abdul

最好在dba.stackexchange.com上发布此内容。 - Tab Alleman

Answer 1

也许这可以帮助您一部分。 sys.objects 有创建和修改日期，但不幸的是，sys.columns 没有。然而，最新添加的列将具有更高的列 ID。我不知道您是否能够轻松地挑选出已删除的列。请注意，除列更改外的其他更改可能会反映在修改日期中。

select  s.name [schema], o.name [table], o.modify_date [table_modify_date], c.column_id, c.name
from    sys.schemas s
join    sys.objects o on o.schema_id = s.schema_id
left    join sys.columns c on c.object_id = o.object_id
where   o.type = 'U'    --user tables only
and     o.modify_date >= dateadd(M,-3, getdate())
order   by s.name, o.name, column_id;

为了使未来的审核更加容易，您可以创建一个DDL触发器，将所有模式更改记录到表中，或者在源代码控制中记录，如果您使用像SSDT数据项目这样的东西来管理您的更改。

- SMM

Answer 2

目前，您的选择有限，今后您可以尝试以下操作，并检查它们是否有帮助..

1.如果您已启用审计，则可以跟踪更改。

要检查是否启用了审计，请使用下面的查询。

select * from sys.dm_server_audit_status

如果您还没有启用审核，可以按照以下步骤启用：SQL Server审核介绍。除非您需要捕获问题中未提及的内容，否则我不建议启用审核。

2.默认跟踪器也会捕获创建的表，但是当空间满时，它会使用回滚文件机制覆盖最后一个文件，所以您可能会运气不佳（因为您要求跨越三个月的范围），但请尝试使用SQL Server默认跟踪器提供的所有内容，了解所有默认跟踪器提供的信息。

我建议选择这个选项并尝试备份这些文件，具体取决于它们何时回滚（因为您只需要检查表更改）。

3.最后一个选择是查询Tlog。

select * from fn_dblog(null,null) where [transaction name]='CREATE TABLE'

上述的Tlog选项仅适用于您拥有超过三个月的Tlog备份并且您需要进行恢复的情况。

- TheGameiswar

Answer 3

如果您想查看过去的所有活动，可以使用MSSQL审计。这是跟踪任何时间的更改的最佳方式。请查看https://msdn.microsoft.com/en-us/library/cc280386.aspx

- Ranjana Ghimire

---

我怎样才能知道MSSQL审计是否已启用？或者这会在不启用它的情况下给我所有记录。 - Abdul

您需要启用审计以记录活动。您可以在对象资源管理器上检查是否已启用审计。 - Ranjana Ghimire

在对象资源管理器下创建了一个新的安全审计，然后启用它。接着我创建了一个演示数据库，并在其中创建了一张表。右键点击新创建的审计，查看日志...但是什么也没有，只显示了一个条目 AUDIT SESSION CHANGED。 - Abdul

您需要启用审计规范以定义应记录哪些组件的审计记录。请查看： http://solutioncenter.apexsql.com/sql-server-database-auditing-techniques/ - Ranjana Ghimire

Answer 4

您可以通过在数据库上右键单击菜单来运行报告：

在这个下拉菜单中，有几份可能会引起您兴趣的报告。或者您可以创建一个自定义报告，只包含您需要的信息。

我的架构报告只能回溯到2016年9月3日，但我有1000多张表格，60多列，每天都有很多更新。您的报告可能会更早。

- Mike

Answer 5

如果您有任何备份，例如在磁带上保存了三个月的数据，您可以将备份还原为不同的名称或另一个服务器，然后通过第三方工具（如Visual Studio、Devart Schema Compare等）运行模式比较来进行比较。

否则，像Gameiswar和其他人所描述的那样，提前设置机制是唯一的方法。

- Steve Mangiameli

Answer 6

您可以使用DDL触发器：

CREATE TRIGGER ColumnChanges  
ON DATABASE   
FOR ALTER_TABLE  
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT alter_table_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(nvarchar(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;  
GO  

- Steve Ford

Answer 7

通过 SQL Server Management Studio 中的 "生成脚本..." 任务选项，对元数据定义进行快照。

将生成的脚本文件存储在一个以当前日期命名的文件夹中。一旦这样做了不止一次，就可以使用WinDiff来突出显示在任何两个快照之间所做的数据库更改。仔细并始终选择“生成脚本”选项，以便基于时间的比较更有益。

- JohnH

Answer 8

这个查询会给出存储过程的创建和修改日期：

select name,create_date,modify_date
from sys.procedures
order by modify_date desc

- Usman Jalil