查询数据库各种历史记录
在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。
一. 数据库启动记录
1. 最近一次启动SQL Server的时间
select sqlserver_start_time from sys.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 from sys.databases
where 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 database DBA
GO
USE DBA
GO
IF OBJECT_ID('login_history','U') is not null
DROP TABLE login_history
GO
CREATE TABLE login_history
(
FACT_ID bigint IDENTITY(1,1) primary key,
LOGIN_NAME nvarchar(1024),
LOGIN_TIME datetime
)
GO
IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
DROP TRIGGER login_history_trigger ON ALL SERVER
GO
CREATE TRIGGER login_history_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
--IF SUSER_NAME() NOT LIKE 'NT AUTHORITY%' AND
-- SUSER_NAME() NOT LIKE 'NT SERVICE%'
IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY%' AND
ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE%'
BEGIN
INSERT INTO DBA..login_history
VALUES(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;