在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 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權限的賬號登錄后創建

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('test_schema.test_owner','U') is not null

drop tabletest_schema.test_ownerGO

create table test_schema.test_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('test_schema.test_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)) tinner joinsys.trace_events teon t.eventid =te.trace_event_id--最多5個文件，每個文件20MB，依次滾動覆蓋

select * fromsys.traceswhere id = 1

示例，利用默認跟蹤查看刪除數據庫記錄如下：

DECLARE @path varchar(1024)SELECT @path =pathFROMsys.tracesWHERE 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，每次被執行的時間等信息：

SELECT st.text assql_statement,

qs.creation_timeasplan_last_compiled,

qs.last_execution_timeasplan_last_executed,

qs.execution_countasplan_executed_count,

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里。

1.備份記錄

SELECTbs.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 ASBackupType,

bmf.physical_device_name,CAST(bs.first_lsn AS VARCHAR(50)) ASfirst_lsn,CAST(bs.last_lsn AS VARCHAR(50)) ASlast_lsn,

bs.server_name,

bs.recovery_modelFROMmsdb.dbo.backupset bsINNER JOINmsdb.dbo.backupmediafamily bmfON bs.media_set_id =bmf.media_set_idORDER BYbs.server_name,bs.database_name,bs.backup_start_date;GO

如果server_name是本機，那么備份是在本機生成的；

如果server_name是別的主機名，那么備份是被拿到本機做過數據庫還原；

2.還原紀錄

SELECTrs.[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 ASRestoreType,

rs.[replace],

rs.[recovery],

rs.[restart],

rs.[stop_at],

rs.[device_count],

rs.[stop_at_mark_name],

rs.[stop_before]

FROM [msdb].[dbo].[restorehistory]rsINNER JOIN [msdb].[dbo].[backupset]bs--on rs.backup_set_id = bs.media_set_id

ON rs.backup_set_id =bs.backup_set_idINNER JOINmsdb.dbo.backupmediafamily bmfON bs.media_set_id =bmf.media_set_idGO

還原數據庫的時候是會寫backupset和backupmediafamily系統表的，用來記錄還原所用到的備份文件信息。

七.作業，維護計劃，數據庫郵件歷史記錄

作業，維護計劃，數據庫郵件的歷史記錄，也都在msdb里。

1.作業歷史記錄

if OBJECT_ID('tempdb..#tmp_job') is not null

drop table#tmp_job--只取最后一次結果

selectjob_id,

run_status,CONVERT(varchar(20),run_date) run_date,CONVERT(varchar(20),run_time) run_time,CONVERT(varchar(20),run_duration) run_durationinto#tmp_jobfrommsdb.dbo.sysjobhistory jh1where jh1.step_id = 0

and (select COUNT(1) frommsdb.dbo.sysjobhistory jh2where jh2.step_id = 0

and (jh1.job_id =jh2.job_id)and (jh1.instance_id <= jh2.instance_id))=1

--排除syspolicy_purge_history這個系統作業

selecta.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 asjob_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) asjob_started_time,+LEFT(RIGHT(1000000+run_duration,6),2)+':'

+SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'

+RIGHT(RIGHT(1000000+run_duration,6),2) asjob_durationfrommsdb.dbo.sysjobs aleft join#tmp_job bon a.job_id=b.job_idwhere 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 * frommsdb..sysdbmaintplan_history--新的系統表也可以

select * frommsdb..sysmaintplan_logselect * from msdb..sysmaintplan_logdetail

維護計劃最終是作為作業在運行的，也可以直接查看同名作業的歷史記錄。

3.數據庫郵件歷史記錄

--直接查系統表

select * frommsdb..sysmail_mailitemsselect * frommsdb..sysmail_log--也可查看基于這2個系統表的系統視圖

select * frommsdb..sysmail_allitemsselect * frommsdb..sysmail_sentitemsselect * frommsdb..sysmail_unsentitemsselect * frommsdb..sysmail_faileditemsselect * frommsdb..sysmail_event_log--更多系統表和視圖

usemsdbGO

select * fromsys.objectswhere 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 TABLEtest_truncateGO

CREATE TABLE test_truncate(ID int)INSERT INTO test_truncate values(1)TRUNCATE TABLEtest_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 *

FROMfn_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個函數返回的信息量很大，如果有興趣，不妨多做點測試。

總結

以上是生活随笔為你收集整理的mysql查询数据库修改记录_11. 查询数据库各种历史记录的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。