mysql查询数据库修改记录_11.查询数据库各种历史记录在SQL Server数据库中，从登陆开始，然后做了什么操作，以及数据库⾥发⽣了什么，⼤多都是有记录可循的，但是也有⼀些确实⽆从查起。
⼀.数据库启动记录
1.最近⼀次启动SQL Server的时间
select sqlserver_start_time fromsys.dm_os_sys_info;--也可参考系统进程创建的时间，⽐服务启动时间略晚(秒级)
select login_time from sysprocesses where spid = 1
select login_time from sys.dm_exec_sessions where session_id = 1
--也可参考tempdb数据库创建的时间，⽐服务启动时间略晚(秒级)
select create_date fromsys.databaseswhere database_id=2
2.最近⼏次启动SQL Server的时间
--参考error log，系统默认保留6个归档，共7个⽂件
exec xp_readerrorlog 0,1, N'SQL Server is starting'
exec xp_readerrorlog 1,1, N'SQL Server is starting'
exec xp_readerrorlog 2,1, N'SQL Server is starting'
exec xp_readerrorlog 3,1, N'SQL Server is starting'
exec xp_readerrorlog 4,1, N'SQL Server is starting'
exec xp_readerrorlog 5,1, N'SQL Server is starting'
exec xp_readerrorlog 6,1, N'SQL Server is starting'
--之前关键字⽤N'Server process ID is'并不严谨，改为N'SQL Server is starting'
3.历史上更多次启动SQL Server的时间
查看windows event log，SQL语句⽆法直接读取event log，如果想⽤命令⾏，可以试试VBS，Powershell。
Event Viewer/Windows logs下Application 或者 System 事件⾥都有服务启动的记录。
⼆. 登录数据库记录
1.查看error log
默认情况下，只有失败的登录会被记录在error log⾥，如果想登录失败/成功都被记录到error log，需要开启如图选项：
⽤SQL语句修改注册表，也同样可以开启，键值对应关系如下：
0, None
1, Failed
2, Successful
3, Both failed and successful
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO
在error log⾥查看登录记录：
exec xp_readerrorlog 0,1, N'Login', N'for user', null, null, N'DESC'
2.利⽤LOGON触发器进⾏记录
从SQL Server 2005 SP2开始引⼊了LOGON Trigger，可以⽤它在登录时做个记录，实现如下：
--创建LOGON触发器
CREATE databaseDBAGO
USEDBAGO
IF OBJECT_ID('login_history','U') is not null
DROP TABLElogin_historyGO
CREATE TABLElogin_history
(
FACT_IDbigint IDENTITY(1,1) primary key,
LOGIN_NAMEnvarchar(1024),
LOGIN_TIMEdatetime)GO
IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')DROP TRIGGER login_history_trigger ON ALLSERVERGO
CREATE TRIGGERlogin_history_triggerON ALLSERVERFORLOGONAS
BEGIN
--IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND
--SUSER_NAME() NOT LIKE 'NT SERVICE\%'
IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' ANDORIGINAL_LOGIN()NOT LIKE 'NT SERVICE\%'
BEGIN
INSERT INTODBA..login_historyVALUES(ORIGINAL_LOGIN(),GETDATE());END;END;GO
--登录后查看记录
SELECT * FROM login_history
3.实例：查询某login的最后⼀次登录
系统表/试图⾥，并没有这样的字段记录，syslogins⾥accdate也是不对的，如果要查可以通过上⾯2个⽅法⾥的⼀种：
(1) ERROR LOG，得设置记录Login Auditing 的“Both failed and successful” 选项，默认为”Failed”；
(2) Logon Trigger；
三.创建，修改，删除记录(DDL)
1.服务器对象的创建，修改
--创建数据库
select name, create_date fromsys.databases--创建，修改登录
select name, createdate, updatedate fromsysloginsselect name, create_date, modify_date fromsys.server_principals--创建，修改LOGON触发器
select name, create_date, modify_date from sys.server_triggers
2.数据库对象创建，修改
--创建，修改数据库对象
select name, create_date, modify_date fromsys.objects--创建，修改触发器，DDL触发器不在sys.objects⾥
select name, create_date, modify_date iggers
注意：
(1)索引的创建，修改并没有记录
sys.objects --⾥⾯没有0,1 之外的索引
sys.indexes --⾥⾯没有⽇期
objectproperty() --没有⽇期属性
indexproperty() --没有⽇期属性
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats--也都没有
STATS_DATE (table_id, index_id) --是索引的统计信息最后更新时间
(2)关于creator和owner
SQL Server⾥只有owner，数据库⾥对象的owner必须是⼀个有效的database principal (user或者role)，没有creator，很难知道是谁创建了这个对象，因为owner并不准确：
⾸先，数据库对象的owner可以被修改，ALTER AUTHORIZATION或者sp_changeobjectowner都⾏；
其次，就算owner没被修改过，默认情况下数据库对象的owner沿⽤schema的owner，除⾮在创建schema时特意指定了某个owner；
最后，系统表并没有记录creator，如果想要查询，也许得利⽤DDL 触发器来记录。
关于owner简单举例如下：
--⽤sysadmin权限的账号登录后创建
USEmasterGO
CREATE LOGIN test_login WITH PASSWORD=N'123', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
ALTER SERVER ROLE sysadmin ADDMEMBER test_loginGO
CREATE databaseDBAGO
USEDBAGO
CREATE USER test_user FORLOGIN test_loginGO
CREATE SCHEMAtest_schemaGO
--⽤"test_login"登录后建表
if OBJECT_ID('st_owner','U') is not null
drop st_ownerGO
create table st_owner(id int)GO
--表的owner还是⽤了schema的owner
select s.name as schema_name, dp2.name asschema_owner,
o.nameas object_name, coalesce(dp1.name, dp2.name) as object_owner,*
fromsys.objects oinner joinsys.schemas son o.schema_id =s.schema_idleft joinsys.database_principals dp1on o.principal_id =dp1.principal_idleft joinsys.database_principals dp2on s.principal_id =dp2.principal_idwhere o.name = 'test_owner'
--⽤objectproperty也可以查看owner
select name asobject_ownerfromsys.database_principalswhere principal_id =
OBJECTPROPERTY(object_id('st_owner'),'OwnerId')
object owner
3.默认跟踪⾥的创建，修改，删除对象(create, alter, drop)
从sql server 2005开始引⼊了默认跟踪，这是sql server默认开启的跟踪，并定义了事件、⽂件⼤⼩，个数，查看定义如下：
--系统定义好的默认跟踪事件
selectt.eventid, te.namefrom (select distinct eventid from sys.fn_trace_geteventinfo(1)) ace_events teon
t.eventid =te.trace_event_id--最多5个⽂件，每个⽂件20MB，依次滚动覆盖
select * aceswhere id = 1
⽰例，利⽤默认跟踪查看删除数据库记录如下：
DECLARE @path varchar(1024)SELECT @path =acesWHERE id = 1
SELECT *
FROM fn_trace_gettable(@path, default) --default读取当前所有trace⽂件，包括正在⽤的
WHERE DatabaseName = 'DBA'
and EventClass = 47 --46表⽰Create对象，47表⽰Drop对象，164表⽰修改对象
and ObjectType = 16964 --16964表⽰数据库
注意：
(1) 其他对象⽐如表的删除等也都可以查到；
(2) 默认跟踪返回的列值有很多定义，没有系统表记载，需要去翻帮助，⽐如ObjectType列值参考这个列表：
(3) 注意默认跟踪的时效性，5个20MB的⽂件，也许想要看的信息很快就被覆盖了；
(4) truncate table并没有被默认跟踪记录。
四.数据库表的各种记录
汇总⼀下对表的各种历史操作记录的查看：
(1) create table, alter table记录，查看sys.objects 或者默认跟踪；
(2) drop table记录，查看默认跟踪；
(3) truncate table 也许只有去打开数据库log⽂件查看了，最后会简单介绍下；
(4) DML操作表中数据的记录，查看sys.dm_db_index_usage_stats，如下：
SELECT o.name astable_name,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_updatefromsys.indexes ileft joinsys.dm_db_index_usage_stats son s.object_id = i.object_id
ands.index_id=i.index_idinner joinsys.objects oon i.object_id = o.object_id
where i.index_id <= 1
and o.is_ms_shipped = 0
order by o.name
注意：动态管理视图(DMV) 中采集来的信息都是从sql server启动后开始的，也就是说重启后就没了。
五.历史SQL语句记录
有些数据库本⾝，会记录所有历史的SQL命令。⽐如：mysql和pgsql都有专门的log⽂本⽂件来存放所有历史的SQL命令；
也有些数据库在保存log⽂本的同时，还保留最近的N条SQL命令在数据库⾥，以⽅便查询。
SQL Server并没有这样的实现，只有sys.dm_exec_query_stats缓存了⼀部分 (sql server服务开启后执⾏的语句，某些不被缓存执⾏计划的语句并不记录)。
这个视图主要是对执⾏计划的统计，包含消耗成本，运⾏次数等等，并没有session，user，每次被执⾏的时间等信息：
assql_statement,
qs.last_execution_timeasplan_last_executed,
qp.query_planFROMsys.dm_exec_query_stats qsCROSSAPPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSSAPPLY sys.dm_exec_query_plan(qs.plan_handle) qporder by total_elapsed_time/execution_count desc
当然，开启跟踪，审计之类的⽅法，是可以记录所有操作的，但是这个开销有可能会影响系统性能，所以⼀般并不在⽣产环境启⽤。
六.数据库备份还原历史记录
备份还原的记录都在msdb⾥。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论