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
31.赠送一条,关于值为null的运算统计,感受下,嘻嘻....
32.如何调整SSMS工具字体的大小和颜色?
打开SSMS,选择"工具"按钮,然后选择“选项”-->“环境”>"字体和颜色",在对应的界面中进行个性化设置。
33.查询当前数据库的兼容级别
SELECT NAME as 当前数据库,cmptlevel as 兼容级别 From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)
34.SQL Server 只安装客户端,在安装过程中怎么进行功能选择。
在安装的时候只要选择安装管理工具就可以了,具体安装过程如下图所示:
前面的步骤都一样,而安装到功能选择时进行设置。
35.sql server 账号权限处更新与更改的区别
简单来说就是update与alter的区别
假设存在表MatchInfo,则各自权限体现如下:
update MatchInfo SET MatchNO=111 --更新权限
alter table MatchInfo add idd int --更改权限
实际就是DDL、DML的区别。
36. 有时候我们需要把多行数据,合并成一行显示,并用逗号或者其他方式分隔显示,这时候我们可以使用FOR XML PATH
SELECT AlarmAddr+';' FROM Alarm_Group WHERE AlarmType='Mail' FOR XML PATH('')
将DBA_Alarm_Group表中AlarmAddr字段,合成一行,分割符为“;”。
37.链接服务器(LinkServer)知识小结
(1) 查询本SQL Server 已创建的所有实例
select srvname as '链接服务器' from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername')
(2)删除已建立的链接服务器(LinkServer)
EXEC master.dbo.sp_dropserver @server=N'172.XXX.XXX.XXX', @droplogins='droplogins'
(3)创建链接服务器(LinkServer),这个属性比较多,代码也比较多,我们后面单独讲解。
38.数据库Log 文件 Size 过大,如何收缩?
(1)可以尝试数据库完整备份;(2)可以尝试进行log备份;(3)还可以 尝试执行收缩命令;(4)以上操作都不可以,还可以在 数据库属性界面中,将【恢复模式】由 完整模式 调整为 简单模式。注意这种操作 会影响 既有的辅助副本。
39.excel 文件某一列 批量加上单引号
有时我们会接受到根据excel所提供的某一列数据进行查询的需求,很多时候不会有单引号的,数据量大的话就应该批量了。
操作步骤如下:
【选中需要操作的列】-->【鼠标右键】-->[设置单元格格式]-->[自定义]-->[类型选择 @ 操作]
在@前后加上单引号,截图如下
40 .BCP 导入导出
主要语法
bcp {[[database_name.][schema].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-mmax_errors] [-fformat_file] [-x] [-eerr_file] [-Ffirst_row] [-Llast_row] [-bbatch_size] [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size] [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword] [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
将BI Hive库中的数据导出成csv的文件,然后再导入到SQL Server数据库中,通过BCP命令时,遇到了一些错误。
例如:
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file
原因是:Linux 和Windows 系统的默认换行符是不一样的,hive数据库是安装在Linux系统上的,通过notepad++ 查看导出的CSV文件其换行符为 LF。而SQL Server安装在Windows系统 上。
解决方案是 添加参数及值;-r"LF"。
其他的要点:如果第一行是列名,从第二行开始,需增加参数及值:-F2;如果字段分割符是‘,’(逗号),需增加参数及值 -t","。
41 修改数据库名字
Step 1 关闭此数据库的用户连接,如果有用户连接到数据库的话会造成数据库重命名失败。生成 Kill 连接的命令:
select 'kill ' + cast (spid as varchar(10)) from master.dbo.sysprocesses where dbid=db_id('OldDbName') --替换数据库名字
Step 2 执行生成的命令,Kill 用户连接
Step 3 修改数据库名字,建议在master数据库中执行
EXEC sp_dboption 'OldDbName', 'Single User', 'TRUE' EXEC sp_renamedb 'OldDbName', 'NewDbName' EXEC sp_dboption 'NewDbName', 'Single User', 'FALSE'
Step 4 检查重命名效果