SQL Server 資料庫部分常用語句小結（三）

21.SQL運行Log的讀取

.EXEC xp_readerrorlog 0,1,null,null,'開始時間','結束時間' 

22. Alwayson 狀況及傳輸情況監控

SELECT  ar.replica_server_name AS [副本名稱] ,
       ar.availability_mode_desc as [同步模式],
        DB_NAME(dbr.database_id) AS [資料庫名稱] ,
        dbr.database_state_desc AS [資料庫狀態],
        dbr.synchronization_state_desc AS [同步狀態],
        dbr.synchronization_health_desc AS [同步健康狀態],
        ISNULL(CASE dbr.redo_rate
                 WHEN 0 THEN -1
                 ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
               END, -1) AS [Redo延遲(秒)] ,
        ISNULL(CASE dbr.log_send_rate
                 WHEN 0 THEN -1
                 ELSE CAST(dbr.log_send_queue_size AS FLOAT)
                      / dbr.log_send_rate
               END, -1) AS [Log傳送延遲(秒)] ,
        dbr.redo_queue_size AS [Redo等待隊列(KB)] ,
        dbr.redo_rate AS [Redo速率(KB/S)] ,
        dbr.log_send_queue_size AS [Log傳送等待隊列(KB)] ,
        dbr.log_send_rate AS [Log傳送速率(KB\S)],
         case when dbr.log_send_rate = 0 then 1 else dbr.log_send_queue_size/dbr.log_send_rate end [LOG隊列預估傳輸時間（秒）]
FROM    [master].sys.availability_replicas AS AR
        INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr 
        ON ar.replica_id = dbr.replica_id
WHERE   dbr.redo_queue_size IS NOT NULL

23. （1）列出高級配置選項

Step 1， 先將 show advanced option 設為 1

USE master;  
GO  
EXEC sp_configure 'show advanced option', '1'; 

Step 2， 運行 RECONFIGURE 並顯示全部配置選項：

RECONFIGURE;  
EXEC sp_configure;  

（2）更改指定配置選項，例如xp_cmdshell，則代碼如下：

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

24. 資料庫常用的備份命令如下：

----完整備份
Declare @FullFileName Varchar(200)
Declare @FileFlag varchar(20)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @FullFileName='文檔路徑\資料庫名字_FULL'+@FileFlag+'.bak'
BackUp DataBase 資料庫名字 To Disk=@FullFileName with init

----差異備份
Declare @DiffFileName varchar(200)
Declare @FileFlag varchar(200)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @DiffFileName='文檔路徑\資料庫名字_Diff_'+@FileFlag+'.bak'
BackUp DataBase 資料庫名字 To Disk=@DiffFileName with init,differential 

----事務日誌備份
Declare @FileName Varchar(200)
Declare @FileFlag varchar(20)
Set @FileFlag=REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-','')+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '')
Set @FileName='文檔路徑\資料庫名字_Trn_'+@FileFlag+'.trn'
BackUp Log 資料庫名字 To Disk=@FileName with init

【如果文檔路徑是遠程Server，建議在with init 選項 後面，添加COMPRESSION，即為 with init,COMPRESSION，實現壓縮式備份。經過測試可以縮短一半左右】

25.資料庫備份文件屬性的查看

RESTORE FILELISTONLY  from disk='D:\BACKUP\XXXXX.BAK'  --查看邏輯文件
restore headeronly from disk='D:\BACKUP\XXXXX.BAK'  --查看屬性     

26 資料庫還原命令如下：

----完整備份還原
RESTORE DATABASE 資料庫名字 FROM 
DISK = '完整備份的文件'---'TTTTTTT.BAK' 
WITH NORECOVERY, MOVE '資料庫名字_Data' TO 'D:\指定路徑\資料庫名字_Data.mdf',
MOVE '資料庫名字_Log' TO 'D:\指定路徑\資料庫名字_Log.ldf'

----差異備份還原
RESTORE DATABASE 資料庫名字 FROM 
DISK = '差異備份的文件'------'SSSSSSSSS.BAK' 
WITH NORECOVERY, MOVE '資料庫名字_Data' TO 'D:\指定路徑\資料庫名字_Data.mdf',
MOVE '資料庫名字_Log' TO 'D:\指定路徑\資料庫名字_Log.ldf'

----log備份還原
RESTORE Log 資料庫名字 
FROM DISK ='事務日誌備份的文件' -----'XXXXXXXX.trn'
WITH NORECOVERY

27 通過 sp_send_dbmail  配置發送郵件，參數 @profile_name的獲取，可通過以下SQL實現。

select name FROM msdb.dbo.sysmail_profile

28.捕捉資料庫請求的連接關閉記錄，包括每一個對話異常中斷或者登入失敗的事件。（最多能記錄1000行數據）

  select cast( record as xml),*  from sys.dm_os_ring_buffers
  where ring_buffer_type='RING_BUFFER_CONNECTIVITY'

 29.將資料庫狀態由 “正在還原” 更新為正常狀態（可訪問）

RESTORE DATABASE 資料庫名字 WITH RECOVERY

30.關於SQL JOB 管理的一些內置SP（存儲過程）

sp_add_job
sp_add_jobschedule 
sp_add_jobserver 
sp_add_jobstep 
sp_delete_job 
sp_delete_jobschedule 
sp_delete_jobserver 
sp_delete_jobstep
sp_delete_jobsteplog 
sp_update_job 
sp_update_jobschedule 
sp_update_jobstep