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
来源: http://www.bubuko.com/infodetail-3034125.html