一. 資料庫啟動記錄；
二. 登陸資料庫記錄；
三. 建立，修改，删除記錄 (DDL)；
四. 資料庫表的各種記錄；
五. 曆史SQL語句記錄；
六. 資料庫備份還原曆史記錄；
七. 作業，維護計劃，資料庫郵件曆史記錄；
八. 檢視資料庫日志檔案；
在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；
三. 建立，修改，删除記錄 (DDL)
1. 伺服器對象的建立，修改
--建立資料庫
select name, create_date from sys.databases
--建立，修改登入
select name, createdate, updatedate from syslogins
select name, create_date, modify_date from sys.server_principals
--建立，修改LOGON觸發器
select name, create_date, modify_date from sys.server_triggers 2. 資料庫對象建立，修改
--建立，修改資料庫對象
select name, create_date, modify_date from sys.objects
--建立，修改觸發器，DDL觸發器不在sys.objects裡
select name, create_date, modify_date from sys.triggers 注意：
(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權限的賬号登入後建立
USE master
GO
CREATE LOGIN test_login WITH PASSWORD=N'123', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER SERVER ROLE sysadmin ADD MEMBER test_login
GO
CREATE database DBA
GO
USE DBA
GO
CREATE USER test_user FOR LOGIN test_login
GO
CREATE SCHEMA test_schema
GO
--用"test_login"登入後建表
if OBJECT_ID('test_schema.test_owner','U') is not null
drop table test_schema.test_owner
GO
create table test_schema.test_owner(id int)
GO
--表的owner還是用了schema的owner
select s.name as schema_name, dp2.name as schema_owner,
o.name as object_name, coalesce(dp1.name, dp2.name) as object_owner,*
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
left join sys.database_principals dp1
on o.principal_id = dp1.principal_id
left join sys.database_principals dp2
on s.principal_id = dp2.principal_id
where o.name = 'test_owner'
--用objectproperty也可以檢視owner
select name as object_owner
from sys.database_principals
where principal_id = OBJECTPROPERTY(object_id('test_schema.test_owner'),'OwnerId') object owner
3. 預設跟蹤裡的建立，修改，删除對象 (create, alter, drop)
從sql server 2005開始引入了預設跟蹤，這是sql server預設開啟的跟蹤，并定義了事件、檔案大小，個數，檢視定義如下：
--系統定義好的預設跟蹤事件
select t.eventid, te.name
from (select distinct eventid from sys.fn_trace_geteventinfo(1)) t
inner join sys.trace_events te
on t.eventid = te.trace_event_id
--最多5個檔案，每個檔案20MB，依次滾動覆寫
select * from sys.traces
where id = 1
示例，利用預設跟蹤檢視删除資料庫記錄如下：
DECLARE @path varchar(1024)
SELECT @path = path
FROM sys.traces
WHERE 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列值參考這個清單：
https://msdn.microsoft.com/en-us/library/ms180953.aspx
(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 as table_name,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and
s.index_id = i.index_id
inner join sys.objects o
on 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，每次被執行的時間等資訊：
SELECT st.text as sql_statement,
qs.creation_time as plan_last_compiled,
qs.last_execution_time as plan_last_executed,
qs.execution_count as plan_executed_count,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
order by total_elapsed_time/execution_count desc 當然，開啟跟蹤，審計之類的方法，是可以記錄所有操作的，但是這個開銷有可能會影響系統性能，是以一般并不在生産環境啟用。
六. 資料庫備份還原曆史記錄
備份還原的記錄都在msdb裡。
1. 備份記錄
SELECT
bs.backup_set_id,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
CAST(DATEDIFF(second, bs.backup_start_date,
bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
CASE bs.[type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'TLog Backup'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS BackupType,
bmf.physical_device_name,
CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
bs.server_name,
bs.recovery_model
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
GO 如果server_name是本機，那麼備份是在本機生成的；
如果server_name是别的主機名，那麼備份是被拿到本機做過資料庫還原；
2. 還原紀錄
SELECT
rs.[restore_history_id],
rs.[restore_date],
rs.[destination_database_name],
bmf.physical_device_name,
rs.[user_name],
rs.[backup_set_id],
CASE rs.[restore_type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'V' THEN 'Verifyonly'
END AS RestoreType,
rs.[replace],
rs.[recovery],
rs.[restart],
rs.[stop_at],
rs.[device_count],
rs.[stop_at_mark_name],
rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
GO 還原資料庫的時候是會寫backupset和backupmediafamily系統表的，用來記錄還原所用到的備份檔案資訊。
七. 作業，維護計劃，資料庫郵件曆史記錄
作業，維護計劃，資料庫郵件的曆史記錄，也都在msdb裡。
1. 作業曆史記錄
if OBJECT_ID('tempdb..#tmp_job') is not null
drop table #tmp_job
--隻取最後一次結果
select job_id,
run_status,
CONVERT(varchar(20),run_date) run_date,
CONVERT(varchar(20),run_time) run_time,
CONVERT(varchar(20),run_duration) run_duration
into #tmp_job
from msdb.dbo.sysjobhistory jh1
where jh1.step_id = 0
and (select COUNT(1) from msdb.dbo.sysjobhistory jh2
where jh2.step_id = 0
and (jh1.job_id = jh2.job_id)
and (jh1.instance_id <= jh2.instance_id))=1
--排除syspolicy_purge_history這個系統作業
select a.name job_name,
case b.run_status when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
else 'Unknown'
end as job_status,
LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
+SPACE(1)
+LEFT(RIGHT(1000000+run_time,6),2)+':'
+SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
+RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
+LEFT(RIGHT(1000000+run_duration,6),2)+':'
+SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
+RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
from msdb.dbo.sysjobs a
left join #tmp_job b
on a.job_id=b.job_id
where a.name not in ('syspolicy_purge_history')
and a.enabled = 1
order by b.run_status asc,a.name,b.run_duration desc 2. 維護計劃曆史記錄
select * from msdb..sysdbmaintplan_history
--新的系統表也可以
select * from msdb..sysmaintplan_log
select * from msdb..sysmaintplan_logdetail 維護計劃最終是作為作業在運作的，也可以直接檢視同名作業的曆史記錄。
3. 資料庫郵件曆史記錄
--直接查系統表
select * from msdb..sysmail_mailitems
select * from msdb..sysmail_log
--也可檢視基于這2個系統表的系統視圖
select * from msdb..sysmail_allitems
select * from msdb..sysmail_sentitems
select * from msdb..sysmail_unsentitems
select * from msdb..sysmail_faileditems
select * from msdb..sysmail_event_log
--更多系統表和視圖
use msdb
GO
select * from sys.objects
where name like '%sysmail%'
and type in('U','V')
order by type,name 八. 檢視資料庫日志檔案
資料庫日志檔案裡對于DDL，DML操作肯定是有記錄的，有2個内置函數可以用來解析，但是并不那麼輕松，簡單介紹如下：
1. fn_dblog 讀取目前線上的日志
select * from fn_dblog(null,null) --2個null代表起始的日志LSN 傳回的結果集中字段定義：
(1) AllocUnitName: 對象名
(2) Operation: 操作類型，常見的有 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW'
(3) [RowLog Contents 0], [RowLog Contents 1], 2,3,4,5: 字段内容，但是是二進制的，和dbcc page看到的類似
試着檢視truncate table記錄如下：
IF OBJECT_ID('test_truncate','U') is not null
DROP TABLE test_truncate
GO
CREATE TABLE test_truncate(ID int)
INSERT INTO test_truncate values(1)
TRUNCATE TABLE test_truncate
--檢視truncate table記錄
select * from fn_dblog(null,null)
where AllocUnitName like '%test_truncate%'
and Description like 'Deallocated%' 2. fn_dump_dblog 讀取資料庫備份裡的日志
參數介紹：前面兩2個NULL和fn_dblog一樣代表起始的日志LSN，DISK表示裝置類型，1表示備份檔案個數，最多64個，這裡以1個檔案為例：
backup database DBA to disk = 'C:\backup\dba.bak'
SELECT *
FROM
fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'C:\backup\dba.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GO 這2個函數傳回的資訊量很大，如果有興趣，不妨多做點測試。