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

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