2001—SQL Server运维随笔

一、知识点 

   1、TSQL基础

   【1】 在脚本中经常出现N''的语句，以大写字母N开头的字符串。说明将其内容作为Unicode字符常量（双字节)进行处理，而没有N开头的字符串，是将其作为字符常量（单字节）。

   2、字符串

-- 搜索指定子字符串
charindex(substring,string,[,start_location])
-- substring 搜索的目标串，长度限制为8000
-- string 被搜索的字符串
-- start_location 搜索的起始位置


--从字符串指定位置取指定长度的字符串
substring(input_string,start,length)
-- intput_string 可以是字符，二进制，文本
-- start是一个整数，起始位置为1
-- length是获取的长度

二、安装、日常运维

    1、WIN2012安装SQL2016

        【1】 安装SQL2016的前置条件

需要安装KB2919355-x64补丁。 而KB2919355又需要前置安装KB2919442-X64补丁。

        【2】 安装KB2919355补丁，报错：更新失败

第一步。移除失败更新（对所有更新补丁可用），OS下命令行使用

dism /online /remove-package /packagename:Package_for_KB2919355~31bf3856ad364e35~amd64~~6.3.1.14

第二步。清除Windows Update缓存

dism /online /cleanup-image /startcomponentcleanup

第三步。重启计算机后，再次进行安装。

   2、Sql Server2017备份数据到脚本sql文件

在数据库名称的右键菜单中选择“生成脚本”

下一画面根据需求，选择全部，或是部分表或视图 。

 点击右上角的“高级”按钮，打开如下对话框，进行操作

 上面那个选项，以设定恢复该脚本的sql server版本，以此为进行向上兼容。

下面那个选项，以设定备份的内容：架构（表结构、函数/存储过程、视图）、数据。

三、功能语句

   1—查询环境

--查询当前数据库
Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)

--生成UUID
declare @uuid  varchar(32)
set @uuid=cast(REPLACE(newid(),'-','') as varchar(32))

   2—功能语句

declare @tablename varchar(400)

	declare tmpCur CURSOR FOR
		SELECT name FROM sys.objects WHERE type='v'
		OPEN tmpCur
		
	FETCH next FROM tmpcur INTO @tablename
	while @@fetch_status=0
			begin
				declare @sql varchar(300)
				--SELECT @sql='drop table '+@tablename
				SELECT @sql='drop view '+@tablename
				
				EXEC(@sql)
				FETCH next FROM tmpcur INTO @tablename
			end
	CLOSE tmpCur
	DEALLOCATE tmpCur

   3—表操作

--增加列
ALTER TABLE table01
    ADD  column01  varchar(32) DEFAULT('xxx')  NOT NULL
--修改列
ALTER TABLE table01
    ALTER COLUMN   column01  int  NOT NULL
--删除列
ALTER TABLE table01   DROP COLUMN column01

--建立主键（组合字段）
ALTER TABLE table01
    ADD CONSTRAINT PK_table01_PK PRIMARY KEY CLUSTERED(col01,col02)
--建立外键/新表
CREATE TABLE table01
(
   Fid varchar(32) PRIMARY KEY ,
   personId int ,
   genderId  int ,
   age     int ,
   CONSTRAINT  FK_table01_person FORGIEN KEY(genderId)
          REFERENCES T_Bas_Gender(Fid)
          ON DELETE CASCADE
          ON UPDATE CASCADE
)
--建立外键/现有表
ALTER TABLE table01
    ADD CONSTRAINT FK_table01_person FORGIEN KEY(genderId)
         REFERENCES T_Bas_Gender(Fid)
          ON DELETE CASCADE
          ON UPDATE CASCADE
-- ON DELETE CASCADE / ON UPDATE CASCADE 用于确保对t_bas_gender表的更改传播到table01

    4—表关系/约束

    5—大容量脚本恢复数据库

    均为网络搜集参考而来。

    例如：sqlcmd -S .\SQL2008  -U sa -P sql2008 -d backAmsData_nd -r -i C:\Users\LYD\Documents\script.sql

    sqlcmd命令参数清单：

 1    [-U 登录 ID]             [-P 密码]
 2    [-S 服务器]                [-H 主机名]           [-E 可信连接]
 3  [-d 使用数据库名称]  [-l 登录超时值]      [-t 查询超时值]
 4  [-h 标题]                    [-s 列分隔符]         [-w 列宽]
 5  [-a 数据包大小]         [-e 回显输入]         [-I 允许带引号的标识符]
 6  [-L 列出服务器]         [-c 命令结束]         [-D ODBC DSN 名称]
 7  [-q "命令行查询"]       [-Q "命令行查询" 并退出]
 8  [-n 删除编号方式]     [-m 错误级别]
 9  [-r 发送到 stderr 的消息]  [-V 严重级别]
10  [-i 输入文件]            [-o 输出文件]
11  [-p 打印统计信息]    [-b 出错时中止批处理]
12  [-X[1] 禁用命令，[退出的同时显示警告]]
13  [-O 使用旧 ISQL 行为禁用下列项]
14      <EOF> 批处理
15      自动调整控制台宽度
16      宽消息
17      默认错误级别为 -1 和 1

  注意，如果脚本太大，可以关闭stderr消息(不添加-r参数)，以加快执行速度。

    6—脚本内执行语句

在查询管理器中，执行SQL字符串

DECLARE @sql NVARCHAR(MAX)
DECLARE @itemtsid INT = 42
DECLARE @orderuid VARCHAR(50) = 'your_order_uid'

SET @sql = N'UPDATE exportDAta SET itemtsid = @itemtsid WHERE fuid = @orderuid'

EXEC sp_executesql @sql, N'@itemtsid INT, @orderuid VARCHAR(50)', @itemtsid, @orderuid

四、故障处置

   1—错误21

    操作系统已经向 SQL Server 返回了错误 21(设备未就绪。)。SQL Server 错误日志和操作系统错误日志中的其他消息可能会提供更多详细信息。这是一个威胁数据库完整性的严重系统级错误条件，必须立即纠正。请执行完整的数据库一致性检查(DBCC CHECKDB)。

--Tsql语句
use master
declare @databasename varchar(255)
--设定实例名
set @databasename='shts_db'
 --单用户模式
 ALTER DATABASE [shts_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb(@databasename,REPAIR_REBUILD)
--多用户模式
ALTER DATABASE [shts_db] SET MULTI_USER WITH ROLLBACK IMMEDIATE

五、数据库快照

用于保存数据库的指定时间节点的状态，可查看、可恢复

   1—建立快照

USE master

CREATE DATABASE short20230505 ON( name='目标库',filename='c:\sqldb\short20230505.mdf')
AS   SNAPSHOT OF [目标库]

 3—从快照恢复

USE master

RESTORE DATABASE [目标库] FROM database_snapshot='short20230505'
GO

 六、设置工作模式

   1—单用户模式    

ALTER DATABASE [db_name]
SET single_user  WITH ROLLBACK IMMEDIATE
GO

   2—多用户模式

ALTER DATABASE  [数据库名]
SET  MULTI_USER
GO

   3—只读模式

ALTER DATABASE [数据库名]
SET read_only