文章目录
- 查询慢sql的方法
- sqlserver备份
- 全备
- 差异备
- 日志备
- ldf备份
- 事务备份
- 注意事项
- SQL Server 还原
- 全备还原
- 差异备份还原
- 日志备/尾日志还原
- 事务日志还原
- 备份还原中的问题
- 还原失败，需要某些权限
- 重命名sql Server数据库名称失败
- 作业迁移
- 单个迁移
- 批量迁移
- 登陆账号迁移
- 批量迁移连接服务器
- 记录数据库的DDL
- 数据库重命名
- 查询所有数据库的大小
- 查询当前数据库的大小
- 数据库显示“正在还原”的处理方法
- 数据库发生死锁怎么处理
- 查看被锁进程号及被锁表名：
- 解锁：
- 创建杀掉引起死锁进程的存储过程
- sqlserver存储过程杀掉数据库中死锁
查询慢sql的方法
1.whoisactive
安装方法
http://whoisactive.com/downloads/下载地址
将下载好的zip包放到sqlserver服务器中
文件-打开-文件-下载好的zip包-在查询窗口点击执行
新建一个查询窗口，输入sp_whoisactive，获取当前运行的所有sql语句
使用方法
输入sp_whoisactive，获取当前运行的所有sql语句
查看当前所有的sql中，观察运行时间，较长时间的，影响到其他业务的，或者影响这个服务器性能，导致其他的sql堆积的sql，就给停止掉
停止方法
whoisactive查看的sql中，可以看到session_id，记录下慢sql的sessionId
使用命令 kill session_id 的方式将慢sql的进行杀掉
2.自己编写的脚本
脚本命令 当数据库出现性能问题时，这个脚本能够直观的看到数据库中SQL 的运行状态，快速找到执行缓慢的语句。这是我使用最频繁的脚本之一.
SELECTes.session_id,database_name=DB_NAME(er.database_id),er.cpu_time,er.reads,er.writes,er.logical_reads,login_name,er.status,blocking_session_id,wait_type,wait_resource,wait_time,individual_query=SUBSTRING(qt.text,(er.statement_start_offset/2)+1,((CASEWHENer.statement_end_offset=-1 THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE er.statement_end_offsetEND-er.statement_start_offset)/2)+1),parent_query=qt.text,program_name,host_name,nt_domain,start_time,DATEDIFF(MS,er.start_time,GETDATE())as duration,(SELECTquery_planFROMsys.dm_exec_query_plan (er.plan_handle))ASquery_planFROMsys.dm_exec_requests erINNERJOINsys.dm_exec_sessionsesON er.session_id=es.session_idCROSSAPPLYsys.dm_exec_sql_text (er.sql_handle)ASqtWHEREes.session_id> 50 ANDes.session_IdNOTIN(@@SPID)ORDER BY1, 2重要信息
logical_reads:逻辑读，衡量语句的执行开销。如果大于10w，说明此语句开销很大。可以检查下索引是否合理
status：进程的状态。running 表示正在运行，sleeping 表示处于睡眠中，未运行任何语句，suspend 表示等待，runnable 等待cpu 调度
blocking_session_id: 如果不为0,例如 60 。表示52号进程正在被60阻塞。50 进程必须等待60执行完成，才能执行下面的语句
host_name ：发出请求的服务器名
program_name:发出请求的应用程序名
duration: 请求的执行时间
3、sqlserver profile
打开sqlserver profiler
常规选项中，勾选保存到表，自己设置一个表
设置容量大小，最大行数。单位是：千行。大概设置10w条，就设置值为100
事件选择-勾选显示所有事件
Security Audit选项中，反勾选Login，Logout
session中，反勾选退出连接(existingConnection)
stored procedures(存储过程) 勾选RPC completed 和 SP stmtCompleted
TSQL 勾选 SQL：Batchcompleted和SQL：stmtcomple
选择列筛选器-》duration-》大于等于3000(此处单位为毫秒)意思是筛选执行3秒以上的sql记录
点击运行，就可以看到记录的慢sql了。
sqlserver备份
全备
全备代码： 记得修改相关路径 USE [master]GO /****** Object:StoredProcedure [dbo].[sp_full_BackupDB]Script Date: 2020/2/12 18:55:20 ******/SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[sp_full_BackupDB](@DBName varchar(50))asbegindeclare @sCommandText varchar(255)declare @startTimevarchar(255)declare @endTimevarchar(255)declare @tmp_file_namevarchar(255)declare @tmp_file_name_2varchar(255)declare @base_dir varchar(255)declare @base_dir_2 varchar(255)declare @now_dayvarchar(255)declare @rarcmdvarchar(255)declare @rarfile varchar(255)declare @tmp_prefix varchar(255)set @tmp_prefix = 'test_back'set @startTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 开始备份完全备份： ' +@DBName + '' + @startTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText ---- 正式备份 -- 类似 2018-01-02-1407是 年-月-日-时分用于日志备份 -- set @now_day = substring( replace(replace(replace(CONVERT(varchar, getdate() , 120 ),'-','-'),' ','-'),':','') , 1, 15)-- 类似 2018-01-02 是 年月日时分 用于完整、差异备份 set @now_day =replace(CONVERT(varchar,getdate() , 111 ),'/','-') set@base_dir = 'D:\bak_db\quanbei_db\' -- 全备 master，压缩备份， 生成类似TongCheng_B2B_DB_TC_master_full_2018-05-28.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName + '_master_full_' + @now_day + '.bak' BACKUP DATABASE [master] TODISK = @tmp_file_namewith STATS = 1,compression-- 全备 model，压缩备份，生成类似TongCheng_B2B_DB_TC_model_full_2018-05-28.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName +'_model_full_' + @now_day + '.bak' BACKUP DATABASE [model] TODISK = @tmp_file_namewith STATS = 1,compression-- 全备 msdb，压缩备份， 生成类似TongCheng_B2B_DB_TC_msdb_full_2018-05-28.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName +'_msdb_full_' + @now_day + '.bak' BACKUP DATABASE [msdb] TODISK = @tmp_file_namewith STATS = 1,compression -- 全备 该用户数据库 生成类似TongCheng_B2B_DB_TC_full_2018-05-28.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName +'_full_' + @now_day + '.bak'BACKUP DATABASE @DBNameTODISK = @tmp_file_namewith STATS = 1,compression -- 停止1分钟 waitfor delay '00:01:00'----- 结束备份set@endTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 结束备份完全备份：' +@DBName + '' + @endTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandTextset@sCommandText = 'echo------------------->>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText endGO创建过存储过程之后，执行全备命令exec [sp_full_BackupDB]库名等待执行完毕即可差异备
差异备代码：记得修改相关路径和名字 USE [master]GO /****** Object:StoredProcedure [dbo].[sp_differential_BackupDB]Script Date: 2020/2/12 18:57:43 ******/SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[sp_differential_BackupDB](@DBName varchar(50))asbegindeclare @sCommandText varchar(255)declare @startTimevarchar(255)declare @endTimevarchar(255)declare @tmp_file_namevarchar(255)declare @tmp_file_name_2varchar(255)declare @tmp_file_name_3varchar(255)declare @base_dir varchar(255)declare @base_dir_2 varchar(255)declare @base_dir_3 varchar(255)declare @now_dayvarchar(255)declare @rarcmdvarchar(255)declare @rarfile varchar(255) declare @tmp_prefix varchar(255)set @tmp_prefix = 'test_differential_back'set @startTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 开始备份： 差异备份' +@DBName + '' + @startTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText ---- 正式备份 -- 类似 2018-01-02 是 年月日时分 用于完整、差异备份 set @now_day =replace(CONVERT(varchar,getdate() , 111 ),'/','-')set@base_dir = 'D:\bak_db\differential_db\'-- 差异备份 该用户数据库 生成类似TongCheng_B2B_DB_TC_differential_2018-05-28.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName +'_differential_' + @now_day + '.bak'BACKUP DATABASE @DBNameTODISK = @tmp_file_name with differential , STATS = 1,compression -- 停止1分钟 waitfor delay '00:01:00'----- 结束备份set@endTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 结束备份： 差异备份' +@DBName + '' + @endTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText set@sCommandText = 'echo------------------->>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandTextendGO差异备执行方式：exec [sp_differential_BackupDB]库名等待执行完毕即可日志备
日志备代码：记得修改相关路径USE [master]GO /****** Object:StoredProcedure [dbo].[sp_tlog_BackupDB]Script Date: 2020/2/12 18:58:56 ******/SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[sp_tlog_BackupDB](@DBName varchar(50))asbegindeclare @sCommandText varchar(255)declare @startTimevarchar(255)declare @endTimevarchar(255)declare @tmp_file_namevarchar(255)declare @base_dir varchar(255)declare @tlog_timevarchar(255)declare @tmp_prefix varchar(255)set @tmp_prefix = 'test_log_back'set @startTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 开始备份： 日志备份'+ @DBName + '' + @startTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText ---- 正式备份 -- 类似 2017-12-21-1000set @tlog_time = substring( replace(replace(replace(CONVERT(varchar, getdate() , 120 ),'-','-'),' ','-'),':','') , 1, 15)set@base_dir = 'D:\bak_db\tlog_db\' -- 日志备份 该用户数据库 生成类似account_tlog_2017-12-20-1000.bak set@tmp_file_name = @base_dir + @tmp_prefix + @DBName +'_tlog_' + @tlog_time + '.bak' BACKUPlog@DBNameTODISK = @tmp_file_name with STATS = 1,compression----- 结束备份set@endTime = convert(varchar,GETDATE( ) , 120)set@sCommandText = 'echo 结束备份： 日志备份' +@DBName + '' + @endTime + ' >>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandText set@sCommandText = 'echo------------------->>D:\bak_db\backup_detail.txt'exec xp_cmdshell@sCommandTextendGO执行代码：exec [sp_tlog_BackupDB]库名等待执行完毕即可ldf备份
进行尾日志还原的时候，mdf不可用，ldf可用的情况下，需要将ldf备份，然后进行还原。(前提是做过全备等备份，然后进行还原) 尾日志备份代码： 修改相关路径和库名即可 BACKUP LOG 库名 TO DISK=N'D:\bak_db\tlog_db2\tail_log.bak' WITH INIT,NO_TRUNCATE; 等待执行完毕即可事务备份
如果误操作进行节点还原，需要有事务备份，然后进行节点还原。此操作需要一些前置条件支持 前置条件：1、数据库右键属性，在选项中查看数据恢复模式为“完整”2、在数据误操作之前进行过完整数据备份 备份方法：1、命令行操作方式：BACKUP LOG [test] TODISK = N'D:\bak_db\testback.trn' WITH NOFORMAT, NOINIT,NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,STATS = 10 2、图形化操作：a/选择要恢复的数据库，右键-“任务”-“备份”b/在弹出的备份对话框中备份类型选择“事务日志”，并添加备份文件“log.trn”------->备份类型：事务日志，目标：磁盘，删除原有的路径，添加到自定义的路径。c/点击确定完成事务日志备份注意事项
1、sqlserver profile的记录慢sql时间单位问题： 在数据表中记录的微妙。但是设置的那个地方填写的是毫秒
2、数据库属性中，恢复模式问题 一定要选择完整，才能进行全备，和事务备份等。
3、做全备、差异备的时候，一定要停掉所有的连接，关闭job。否则恢复的时候，就会显示恢复中，这是sqlserver的事务未提交问题
4、重启sqlserver服务器的时候，一定要关闭数据库服务，关闭job等。防止数据库事务未提交完毕就重启，会导致数据库在恢复中。
SQL Server 还原
全备还原
还原代码： 修改相应的路径即可USE [master]RESTORE DATABASE [test] FROMDISK = N'D:\bak_db\quanbei_db\test_backtest_full_2020-02-12.bak' WITHFILE = 1, NORECOVERY,NOUNLOAD,STATS = 5--如果此处不是最后一步还原的话，使用NORECOVERY，如果此处是最后一步，就需要使用RECOVERY，否则会出问题GO 等待执行完毕即可完成全备还原差异备份还原
差异备还原代码：修改相对路径即可USE [master]RESTORE DATABASE [test] FROMDISK = N'D:\bak_db\differential_db\test_differential_backtest_differential_2020-02-12.bak' WITHFILE = 1, NORECOVERY,NOUNLOAD,STATS = 5 --如果此处不是最后一步还原的话，使用NORECOVERY，如果此处是最后一步，就需要使用RECOVERY，否则会出问题GO 等待执行完毕即可完成差异备份还原日志备/尾日志还原
日志备份，尾日志备份的还原，都用该命令即可。记得修改相对应的路径 USE [master]RESTORE LOG [test] FROMDISK = N'D:\bak_db\tlog_db2\tail_log.bak' WITHFILE = 1,RECOVERY,NOUNLOAD,STATS = 5 --如果此处不是最后一步还原的话，使用NORECOVERY，如果此处是最后一步，就需要使用RECOVERY，否则会出问题GO --- 如果上面报错，可以尝试将WITH后面的内容都删掉，只保留RECOVERY 等待执行完毕即可完成日志备份还原事务日志还原
1、可视化操作：a.打开数据库还原页面，检查源数据库及目标数据库是否正确，检查完整备份及日志备份是否完整，并点击“时间线”进入时间线选择b.在弹出的时间线对话框中选择“特定日期和时间”，并将时间设置为误删之前时间点，如16:20c.再次检查目标数据库，及备份集信息无误，点击“选项”进入选项卡，勾选“覆盖现有数据库”及“关闭现有连接”复选框，然后确定，直至还原成功 2、命令行操作USE [master]ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE --- 修改数据库为单用户模式-- 还原前进行尾日志备份BACKUP LOG [test] TODISK = N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\test_LogBackup_2020-02-12_19-24-21.bak' WITH NOFORMAT, NOINIT,NAME = N'test_LogBackup_2020-02-12_19-24-21', NOSKIP, NOREWIND, NOUNLOAD,NORECOVERY ,STATS = 5 -- 还原全备备份RESTORE DATABASE [test] FROMDISK = N'D:\bak_db\quanbei_db\test_backtest_full_2020-02-12.bak' WITHFILE = 2,NORECOVERY,NOUNLOAD,REPLACE,STATS = 5 -- 还原事务日志备份，并设置还原到的时间节点RESTORE LOG [test] FROMDISK = N'D:\bak_db\testback.trn' WITHFILE = 1,NOUNLOAD,STATS = 5,STOPAT = N'2020-02-12T18:30:00' ALTER DATABASE [test] SET MULTI_USERGO 等待还原成功即可备份还原中的问题
还原失败，需要某些权限
问题描述：
SQL Server 阻止了对组件 ‘xp_cmdshell’ 的 过程’sys.xp_cmdshell’
的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用
‘xp_cmdshell’。有关启用 ‘xp_cmdshell’ 的详细信息，请参阅 SQL Server 联机丛书中的
“外围应用配置器”。
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;重命名sql Server数据库名称失败
问题描述：
消息 5030，级别 16，状态 2，第 1 行无法用排他锁锁定该数据库，以执行该操作。
视图模式解决：
1、在对象资源管理器中，连接到 SQL Server 数据库引擎实例，然后展开该实例。2、右键单击要更改的数据库，再单击“属性”。3、在“数据库属性”对话框中，单击“选项”页。4、在“限制访问”选项中，选择“单用户”。5、如果其他用户连接到数据库，将出现“打开的连接”消息。若要更改属性并关闭所有其他连接，请单击“是”。6、重命名完了，再限制访问的属性改回原来的就可以啦命令行模式解决:
--1.执行SQL ALTER DATABASE db_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE--修改为单用户模式--2.然后关闭所有的查询窗口，重命名你的数据库名称--3.执行SQL ALTER DATABASE db_database SET MULTI_USER--再修改为多用户模式作业迁移
单个迁移
在ssms中，打开作业文件夹 右键要迁移的作业 编写作业脚本为-create到-新的查询窗口 将生成的脚本全部赋值下来 去目标数据库上面，新建查询窗口执行刚才生成的脚本 有可能迁移过后的脚本是禁用状态，作业文件夹右键-管理计划-根据时间说明将对应的job开启。或者逐个点击开启 问题：1、执行失败：可能原因是源服务器使用的windows账号登陆的，导致无法执行，这个时候需要将登陆账号("@owner_login_name")改为本地的账号本地登陆名-》ssms中，安全性-登录名下所有启用的账户，此处尽量保证源db的用户和目标db的用户一致。批量迁移
在ssms中，打开作业文件夹 选中作业文件夹，然后按F7，出现对象管理器详细信息界面 按着ctrl键，多选要迁移的作业job 右键-编写作业脚本为-create到-新的查询窗口 将生成的脚本全部复制，到迁移目标数据库上面，新建查询窗口，执行刚才生成的脚本，就可以完成批量迁移 有可能迁移过后的脚本是禁用状态，作业文件夹右键-管理计划-根据时间说明将对应的job开启。或者逐个点击开启 问题：1、执行失败：可能原因是源服务器使用的windows账号登陆的，导致无法执行，这个时候需要将登陆账号("@owner_login_name")改为本地的账号本地登陆名-》ssms中，安全性-登录名下所有启用的账户，此处尽量保证源db的用户和目标db的用户一致。登陆账号迁移
批量迁移脚本
---SQL 2005以上版本适用 --找到了解决办法.--1.在A服务器上执行USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULLDROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal@binvalue varbinary(256),@hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF'WHILE (@i <= @length)BEGINDECLARE @tempint intDECLARE @firstint intDECLARE @secondint intSELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))SELECT @firstint = FLOOR(@tempint/16)SELECT @secondint = @tempint - (@firstint*16)SELECT @charvalue = @charvalue +SUBSTRING(@hexstring, @firstint+1, 1) +SUBSTRING(@hexstring, @secondint+1, 1)SELECT @i = @i + 1END SELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULLDROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @PWD_varbinaryvarbinary (256)DECLARE @PWD_stringvarchar (514)DECLARE @SID_varbinary varbinary (85)DECLARE @SID_string varchar (514)DECLARE @tmpstrvarchar (1024)DECLARE @is_policy_checked varchar (3)DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL)DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins lON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name 'sa'ELSEDECLARE login_curs CURSOR FORSELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins lON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_nameOPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGINPRINT 'No login(s) found.'CLOSE login_cursDEALLOCATE login_cursRETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''WHILE (@@fetch_status -1)BEGINIF (@@fetch_status -2)BEGINPRINT ''SET @tmpstr = '-- Login: ' + @namePRINT @tmpstrIF (@type IN ( 'G', 'U'))BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'ENDELSE BEGIN -- SQL Server authentication-- obtain password and sidSET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUTEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy stateSELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @nameSELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL )BEGINSET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checkedENDIF ( @is_expiration_checked IS NOT NULL )BEGINSET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checkedENDENDIF (@denylogin = 1)BEGIN -- login is denied accessSET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )ENDELSE IF (@hasaccess = 0)BEGIN -- login exists but does not have accessSET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )ENDIF (@is_disabled = 1)BEGIN -- login is disabledSET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'ENDPRINT @tmpstrEND FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO批量迁移执行方法
exec master..sp_help_revlogin 执行上面的语句，可以生成创建账号登录名的脚本命令注意，一般情况下，就迁移自己创建的即可，其他的上面登录名可以不用迁移。NT开头的，和windows登陆的两种，可以忽略，这个这次执行命令，可以看到只有一个自建登录名，test_login。
这个脚本无法生成sa，需要自己创建修改密码等
总结
使用这个办法有2个好处。
1。可以批量同步所有需要的登录账号
2.由于产生的SID是相同的，不会有孤立账号的问题。在ALWAY ON环境下，我们就需要采用这种方式。不然没有ALWAYS ON 切换后都需要手动处理 孤立账号的
批量迁移连接服务器
然后把生成的脚本，COPY到新的服务器上面执行就可以了。在建立完成后一定要测试链接服务器是不是可用
总结
批量迁移链接服务器的方法和批量迁移定时作业的方法机会是一样的。
记录数据库的DDL
使用脚本记录下函数，存储过程，触发器，表结构的修改
-- Table to store the dataCREATE TABLE DDLLog(id INT NOT NULL IDENTITY(1,1),event_type sysname,object_id int,object_name sysname,change_date datetime,changed_by sysname);GOCREATE TRIGGER tr_DDLLogON DATABASEFOR DDL_PROCEDURE_EVENTS,DDL_FUNCTION_EVENTS,DDL_VIEW_EVENTS,DDL_TRIGGER_EVENTS,DDL_TABLE_EVENTSASBEGININSERT INTO DDLLog(event_type, object_id, object_name, change_date, changed_by)VALUES (EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(255)'),EVENTDATA().value('(/EVENT_INSTANCE/ObjectId)[1]','INT'),EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(255)'),getdate(),ORIGINAL_LOGIN());END测试
--创建存储过程CREATE PROCEDURE ChangeTest ASPRINT '1';GO--修改存储过程ALTER PROCEDURE ChangeTest ASPRINT '1';GO--删除存储过程DROP PROCEDURE ChangeTest;GO结果
总结
当下次发生问题时，可以自己心中有数。
数据库重命名
问题描述：
在数据库重命名的时候，平时最经常使用的就是sp_renamedb
，或者是在SSMS工具中右键进行重命名。这样操作简单，快捷。但是有一个问题就是对应的mdf,ldf文件名字不会跟着改变.而且数据库的逻辑文件名也不会跟着改变。
最佳实践
修改数据库的逻辑文件名字
ALTER DATABASE Mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE [Mydb] MODIFY FILE (NAME=N'Mydb', NEWNAME=N'MydbNew')GOALTER DATABASE [Mydb] MODIFY FILE (NAME=N'Mydb_log', NEWNAME=N'MydbNewlog')GO修改物理文件名字
分离数据库
USE [master]GOEXEC master.dbo.sp_detach_db @dbname = N'Mydb'GO修改物理文件名字
找到db对应的ldf和mdf的路径，进行重命名
附加数据库
USE [master]GOCREATE DATABASE MydbNew ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MydbNew.mdf' ),( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MydbNew_log.ldf' )FOR ATTACHGO设置为多用户
ALTER DATABASE MydbNew SET MULTI_USER检查
--查看数据库的文件名：修改最后的db名字SELECTname AS [Logical Name],physical_name AS [DB File Path],type_desc AS [File Type],state_desc AS [State]FROM sys.master_filesWHERE database_id = DB_ID(N'MydbNew')总结
修改数据库名字是一个很常见的操作，直接改名很容易，但是可能会以后留下隐患。
查询所有数据库的大小
with fsas(select database_id, type, size * 8.0 / 1024 sizefrom sys.master_files)select name,(select cast(round(sum(size),2) as numeric(15,2))from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,(select cast(round(sum(size),2) as numeric(15,2))from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMBfrom sys.databases dborder by 2 desc;查询当前数据库的大小
EXEC sp_spaceused数据库显示“正在还原”的处理方法
1）新建查询，复制下面代码，修改数据库名后，执行；
--恢复并且回到可访问状态，要执行 RESTORE database 数据库名with recovery2）点击对象资源管理器->数据库->右键->刷新，
再次展开，就OK了。
数据库发生死锁怎么处理
查看被锁进程号及被锁表名：
SELECT request_session_id spid, --进程号 OBJECT_NAME(resource_associated_entity_id) tableName--被锁表名FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'解锁：
KILL [进程号]创建杀掉引起死锁进程的存储过程
create proc dbo.p_killspid @dbname varchar(200)--要关闭进程的数据库名 asdeclare @sqlnvarchar(500)declare @spid nvarchar(20)declare #tb cursor for select spid=cast(spid as varchar(20)) from master.dbo.sysprocesses where dbid=db_id(@dbname) open #tb fetch next from #tb into @spid while @@fetch_status=0 beginexec('kill '+@spid) fetch next from #tb into @spid endclose #tb deallocate #tbgosqlserver存储过程杀掉数据库中死锁
Create proc p_lockinfo@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示as declare @count int,@s nvarchar(1000),@i intselect id=identity(int,1,1),标志,进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,登陆时间=login_time,打开事务数=open_tran, 进程状态=status,工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,域名=nt_domain,网卡地址=net_addressinto #t from(select 标志='死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=a.spid,s2=0from master..sysprocesses a join (select blocked from master..sysprocesses group by blocked)b on a.spid=b.blocked where a.blocked=0union allselect '|_牺牲品_>',spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=blocked,s2=1from master..sysprocesses a where blocked0)a order by s1,s2 select @count=@@rowcount,@i=1 if @count=0 and @show_spid_if_nolock=1begininsert #tselect 标志='正常的进程',spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_addressfrom master..sysprocessesset @count=@@rowcountend if @count>0begincreate table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))if @kill_lock_spid=1begindeclare @spid varchar(10),@标志 varchar(10)while @i<=@countbegin select @spid=进程ID,@标志=标志 from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')') if @标志='死锁的进程' exec('kill '+@spid) set @i=@i+1endendelsewhile @i<=@countbegin select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i insert #t1 exec(@s) set @i=@i+1endselect a.*,进程的SQL语句=b.EventInfofrom #t a join #t1 b on a.id=b.idend使用
非原创在mester中创建存储过程存储过程执行命令exec master.dbo.p_lockinfo 0,0 ---显示死锁的进程,不显示正常的进程exec master.dbo.p_lockinfo 1,0 ---杀死死锁的进程,不显示正常的进程exec master.dbo.p_lockinfo 0,1 ---显示死锁的进程,显示正常的进程exec master.dbo.p_lockinfo 1,1 ---杀死死锁的进程,显示正常的进程