SQL Server 数据库还原

数据库恢复大多是让人很头痛的部分。比如我,经常需要把生成环境的数据备份下来还原到测试环境中进行新功能的测试。

带我的前辈告诉我的方式是在SSMS中直接右键还原源选择恢复数据的来源数据库,目标选择要还原的数据库。

SQL Server 数据库还原

这种方式的成功率很低!经常会数据库使用中的错误提示:Exclusive access could not be obtained because the database is in use

后来找了两种方式来保证还原时数据库没被使用:

方法一:变成单用户模式

--变成单用户模式
USE master
GO
ALTER DATABASE test3 --换成你的数据库名
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

--恢复多用户模式
USE master
GO
ALTER DATABASE test3 --换成你的数据库名
SET MULTI_USER
Go

方法二:脱机

---开始脱机
ALTER DATABASE test --你的数据库名
SET OFFLINE WITH ROLLBACK IMMEDIATE;

--恢复联机
ALTER DATABASE test --你的数据库名
SET ONLINE WITH ROLLBACK IMMEDIATE;

这样恢复成功的概率增加了很多,不过依然有一些莫名奇妙的状况导致死活恢复不了。

后来,经过多种查找和尝试,终于找到了比较稳定的方式:

--返回由备份集内包含的数据库和日志文件列表组成的结果集。
--获得逻辑文件名
USE master
RESTORE FILELISTONLY
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'

GO

--test和test_log是上面查到的逻辑名称
----注意:当还原数据库时,可以改变数据库的名称以及它的物理文件名称,但是不可以改变它的逻辑名称,所以,当我们将一个数据库还原成为另外一个数据库时,首先要利用RESTORE FILELISTONLY命令得到它的原始逻辑名称
USE master
RESTORE DATABASE test2
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'

   WITH MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2.mdf',

              MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log2.ldf',
   STATS = 10, --每完成10%显示一条记录,实时显示在Messages里面
   REPLACE ----加上 REPLACE 参数,则在现有数据库基础上强制还原
GO

效果展示:

总结,从备份到还原完整流程整理如下:

/*
       从备份生成数据库bak文件到还原到另一个数据库的完整流程
*/
USE master
BACKUP DATABASE backDB
  TO DISK = 'D:\Backup\test.bak'

--
RESTORE FILELISTONLY
  FROM DISK = 'D:\Backup\test.bak'

--
RESTORE DATABASE restoreDB
  FROM DISK = 'D:\Backup\test.bak'
  WITH MOVE 'DBTest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2.mdf',
  MOVE 'DBTest_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log2.ldf',
  STATS = 10,REPLACE
GO

差异备份还原:

如果要还原差异备份,需要先还原在差异备份时间点之前的一个完整备份,在还原完整备份时要加上NORECOVERY参数。

还原差异备份时,如果只有一个差异备份文件需要还原,无需使用NORECOVERY参数,如果有多个差异备份文件需要还原,除了最后一个差异备份文件,其他都需要加上NORECOVERY参数。

--Datebase_backup_2016_08_29_000002_6047304 --完整备份文件
--Datebase_backup_2016_08_31_000002_2090321 --差异备份文件

--查看备份文件的逻辑名称
RESTORE FILELISTONLY
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_31_000002_2090321.bak' 

---开始脱机
ALTER DATABASE RestoreDB
SET OFFLINE WITH ROLLBACK IMMEDIATE;

--恢复联机
ALTER DATABASE RestoreDB
SET ONLINE WITH ROLLBACK IMMEDIATE;

USE master
RESTORE DATABASE RestoreDB
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_29_000002_6047304.bak'
  WITH MOVE 'SKTMES_BPS_1_0_0' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0.mdf',
  MOVE 'SKTMES_BPS_1_0_0_log' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0_log.LDF',
  STATS = 10,REPLACE, --使用强制还原
  NORECOVERY 
GO

RESTORE DATABASE RestoreDB
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_31_000002_2090321.bak'
  WITH MOVE 'SKTMES_BPS_1_0_0' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0.mdf',
  MOVE 'SKTMES_BPS_1_0_0_log' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0_log.LDF',
  STATS = 10   --最后一个差异备份文件不需要添加 NORECOVERY
GO

参考:

  1. 使用bak文件还原SQL Server
  2. SQL Server 数据库备份和还原认识和总结(一)
  3. SQL Server 数据库备份和还原认识和总结(二)
  4. RESTORE DATABASE命令还原SQLServer 2005 数据库
  5. SQL Server的差异备份还原

开启SQL Server 2012 远程调试功能

入职这个公司以来,每天写的最多的就是 SQL Server 存储过程。作为一个非专业开发出身的人员,几乎每天都在摸索着前进。

写庞大存储过程的痛苦,逼得我经常去尝试一些SSMS的替代工具。昨天在尝试一款替代工具时发现 SQL 语句竟然也可以逐步调试。于是回想起SSMS也有调试菜单啊,说不定微软就给我们提供了一大杀器呢!

调试工具:

在未使用工具的蛮荒时代,我们调试存储过程都是使用 Print 语句把各种变量打印出来。

本文使用工具的是:SQL Server 2016 Management Studio 同时在 SQL Server 2012 Management Studio 测试正常。

连接的数据库版本是:SQL Server 2012

本机调试:

如果SQL Server 和SSMS工具安装在同一台服务器上,那么不用进行多余的配置直接可以使用Windows 账户或sa账户进行调试。

远程调试:

远程调试开启的前提:

①、SQL Server 服务器端配置防火墙规则。
②、SSMS运行的电脑 Windows 账户必须是SQL Server 服务器上的一个域成员,并且此成员要拥有 sysadmin 角色。

开启的步骤(参考微软官方文档 设置成功,备注地址):

①、服务器端打开“高级安全Windows防火墙”

快捷键 Win+R 输入 wf.msc 打开

开启SQL Server 2012 远程调试功能

②、建立sqlserver.exe的入站规则

开启SQL Server 2012 远程调试功能

* 在 “规则类型” 对话框中,选择 “程序”,然后单击 “下一步”。
* 在 “程序” 对话框中,选择 “此程序路径:” ,然后输入指向此实例的 sqlservr.exe 的完整路径。 默认情况下,sqlservr.exe 安装在 C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Binn 中,其中,InstanceName 对于默认实例为 MSSQLSERVER,对于任何指定的实例则为相应实例的名称。
* 在 “操作” 对话框中,选择 “允许连接”,然后单击 “下一步”。
* 在 “配置文件” 对话框中,选择在您想要打开针对该实例的调试会话时描述计算机连接环境的任何配置文件,然后单击 “下一步”。
* 在 “名称” 对话框中,键入针对此规则的名称和说明,然后单击 “完成”。
* 在 “入站规则” 列表中,右键单击您创建的规则,然后在操作窗格中选择 “属性” 。
* 选择 “协议和端口” 选项卡。
* 在 “协议类型:” 框中选择 “TCP” ,在 “本地端口:” 框中选择 “RPC 动态端口” ,单击 “应用”,然后单击 “确定”。

③、建立svchost.exe的入站规则

* 在“高级安全 Windows 防火墙”的左窗格中,右键单击“入站规则”,然后在操作窗格中选择“新建规则”。
* 在 “规则类型” 对话框中,选择 “程序”,然后单击 “下一步”。
* 在 “程序” 对话框中,选择 “此程序路径:” ,然后输入指向 svchost.exe 的完整路径。 默认情况下,svchost.exe 安装在 %systemroot%\System32\svchost.exe 中。
* 在 “操作” 对话框中,选择 “允许连接”,然后单击 “下一步”。
* 在 “配置文件” 对话框中,选择在您想要打开针对该实例的调试会话时描述计算机连接环境的任何配置文件,然后单击 “下一步”。
* 在 “名称” 对话框中,键入针对此规则的名称和说明,然后单击 “完成”。
* 在 “入站规则” 列表中,右键单击您创建的规则,然后在操作窗格中选择 “属性” 。
* 选择 “协议和端口” 选项卡。
* 在 “协议类型:” 框中选择 “TCP” ,在 “本地端口:” 框中选择 “RPC 端点映射程序” ,单击“应用”,然后单击 “确定”。

④、把要使用的登陆用户添加到sysadmin角色中

开启SQL Server 2012 远程调试功能

这样在客户端就可以使用 Windows 身份验证或者 sa 账户等属于sysadmin角色的账户登录SSMS进行调试了。

调试步骤:

SSMS调试的方法和Visual Studio中的用法极为相似。

大体分为:设置断点、逐步或逐过程运行

开启SQL Server 2012 远程调试功能


参考:

  1. 运行 TSQL 调试器之前配置防火墙规则
  2. 运行 Transact-SQL 调试器