SQL Server 2012 备份与还原详解

本文将演示使用SSMS数据库管理工具进行数据库备份及还原的详细步骤，希望对你有帮助。

预备知识

数据库备份

   数据库备份，即从SQL Server 数据库或其事务日志中将数据或日志记录复制到相应的设备，以创建数据副本或事务日志副本。数据还原用于将指定 SQL Server 备份中的所有数据和日志复制到指定数据库，然后通过应用记录的更改使该数据在时间上向前移动，以回滚备份中记录的所有事物。
    
    设计一个好的备份和还原策略需考虑多方面的因素，包括备份内容、备份计划、备份介质、备份设备、备份类型和恢复模式。在 SQL Server 2012 系统中，常见的备份类型有完整备份、差异备份、事务日志备份、文件和文件组备份。

• 完整备份：将数据库的数据全部备份。

• 差异备份：将上一次备份后到现在对数据的修改进行备份。因此差异备份不能单独使用,只能先还原上一次的完整备份后,才能还原差异备份,当有多个差异备份时,只需要选择指定日期的差异备份文件即可,不需要逐个还原在此期间的差异备份。
     
   “恢复模式（Recovery model）”是一种数据库属性，它控制如何记录事务、事务日志是否需要或允许备份，以及可以使用哪些类型的还原操作。有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。通常情况下，数据库使用简单恢复模式或完整恢复模式。

• 简单恢复模式：数据库记录大多数事务，并不会记录所有的事务，数据库在备份之后，自动截断事务日志，即把不活动的事务日志删除。因此，不支持事务日志备份，也不能恢复到出现故障的时间点，具有较高的安全风险，建议只有对数据安全性要求不高的数据库使用该模式。

• 完整恢复模式：数据库完整地记录了所有的事务，并保留所有事务的详细日志。支持恢复到出现故障的时间点。该模式可在最大范围内防止出现故障时丢失数据，为数据安全提供了全面的保护。建议对数据安全性、可靠性要求高的数据库使用该恢复模式。

• 大容量日志恢复模式：数据库不会对所有事务做完整详细的记录，只对大容量操作做最少的记录。通常情况下，只有在要进行大容量操作之前，才改用该恢复模式，大容量操作结束之后，再设置回原来的恢复模式。

创建备份设备

  在 SQL Server 2012 中，数据库备份设备分为物理备份设备和逻辑备份设备。

• 物理备份设备是指保存备份数据的操作系统所识别的磁带或磁盘文件。
• 逻辑备份设备是指数据库系统所识别的逻辑对象，是指向特定物理设备的可选用户定义名称，是物理备份设备的一个逻辑别名，别名名称必须唯一。

使用SSMS工具创建备份设备

展开数据库实例，打开“Server Objects”，右击“Backup Devices”，选择“New Backup Devices” 。

在备份设备窗口中，输入备份设备名称，选择备份设备路径和文件名，点击OK，完成备份设备的创建。

备份设备创建成功

使用SQL方式创建备份设备

语法格式：

EXEC sp_addumpdevice <存储类型> ,<备份设备逻辑名>, <物理文件路径>

使用示例：

use master
go
EXEC sp_addumpdevice 'disk' 'studyDB_backup' 'F:\Program Files\Microsoft SQL Server\MSSQL11.STUDR\MSSQL\Backup\studyDB_backup.bak'

 

---

使用SSMS工具备份与还原

完整备份与还原

本文将对新建数据库TEST01 进行备份演示。

建表

为了便于还原效果的验证，现在数据库中新建一张表 dbo.classs 。

查询数据如下：

select * from dbo.class

完整备份

使用SSMS工具完整备份数据库TEST01，步骤如下：

1）右击数据库 TEST01 -》选择 Tasks -》选择 Back Up 。

2）在备份数据库弹出框中选择 Backup type为 Full -》点击 Remove 删除系统生成的目标文件。

3）点击 Add按钮弹出选择备份目标对话框

4）在选择备份目标弹出框中点击选择备份路径 -》输入数据备份文件名称 -》点击OK。

5）Options 保留默认配置 -》点击OK。

6）在备份数据库弹出框点击OK。

至此，已完成数据库 TEST01 的完整备份。

删除数据库

7）右击数据库 TEST01 -》选择 Delete 。

8）点击 OK 删除数据库。

注意：在删库之前需要断开其他连接，否则会报如下错误：

9）数据库 TEST01 已删除

完整备份还原

使用SSMS工具利用完整备份还原数据库TEST01，步骤如下：

10）右击Databases -》选择 Restore Database 。

11）Source 选择 Device -》点击 ... -》在弹出的窗口中 点击 Add 添加用来恢复的备份文件。

12）选择备份文件 -》点击OK 。

13）可看到备份文件已添加成功 -》点击OK。

14）General 页配置完成后如下图所示

15）Files 页显示如下图

16）在 Options 页，注意Recovery state 选择 “RESTORE WITH RECOVERY” （默认选项） -》点击OK。

17）在还原数据库弹出框点击确定。

至此，数据库 TEST01 还原完成。

数据检查

select * from dbo.class

差异备份

建表

现复制dbo.class 表，命名为 dbo.classbak

select * into dbo.classbak from dbo.class

差异备份

使用SSMS工具差异备份数据库TEST01，步骤如下：

18）右击数据库 TEST01 -》选择 Tasks -》选择 Back Up 。

19）在备份数据库弹出框中选择 Backup type为 Differential -》点击 Remove 删除系统生成的目标文件。

20）点击 Add按钮弹出选择备份目标对话框

21）在选择备份目标弹出框中点击选择备份路径 -》输入数据备份文件名称 -》点击OK。

配置完成后，Genetal 页如下图所示：

22）Options 保留默认配置 -》点击OK。

23）在备份数据库弹出框点击OK。

删除数据库

说明：为了做一次完整的恢复过程演示，先删除刚才还原的数据库 TEST01。
步骤见 7）~ 9）

差异备份还原

说明：在做差异备份前需先做完整备份的还原，否则会报如下错误：

下面做正确还原步骤的演示

完整备份还原

24）右击Databases -》选择 Restore Database 。

25）Source 选择 Device -》点击 ... -》在弹出的窗口中 点击 Add 添加用来恢复的备份文件。

26）选择备份文件 -》点击OK 。

27）可看到备份文件已添加成功 -》点击OK。

28）General 页配置完成后如下图所示

29）Files 页显示如下图

30）在 Options 页，注意Recovery state 选择 “NORESTORE WITH RECOVERY” -》点击OK。

31）在还原数据库弹出框点击确定。

此时，数据库处于 Restoring 状态，不可用，等待着下一步还原操作。

差异备份还原

32）右击数据库 TEST01 -》选择 Tasks -》选择 Restore -》选择 Database 。

33）Source 选择 Device -》点击 ... -》在弹出的窗口中 点击 Add 添加用来恢复的备份文件。

34）选择备份文件 -》点击OK 。

35）可看到备份文件已添加成功 -》点击OK。

36）General 页配置完成后如下图所示

37）Files 页显示如下图

38）在 Options 页，注意Recovery state 选择 “RESTORE WITH RECOVERY” （默认选项） -》点击OK。

39）在还原数据库弹出框点击确定

至此，数据库 TEST01 还原完成。

数据检查

select * from dbo.class

select * from dbo.classbak

结果均如下：

数据无误！

事务日志备份

现添加表格 dbo.classbak02

select * into dbo.classbak02 from dbo.class

使用SSMS工具对数据库TEST01做事务日志备份，步骤如下：

40）右击数据库 TEST01 -》选择 Tasks -》选择 Back Up 。

41）在备份数据库弹出框中选择 Backup type为 Transaction Log -》点击 Remove 删除系统生成的目标文件。

42）点击 Add按钮弹出选择备份目标对话框

43）在选择备份目标弹出框中点击选择备份路径 -》输入数据备份文件名称 -》点击OK。

44）General 页配置完成后如下图所示

45）Options 保留默认配置 -》点击OK。

46）在备份数据库弹出框点击OK。

事务日志备份还原

需要先做完整备份还原和差异备份还原后，再做事务日志还原

全备份还原

步骤同 24）~ 31），这里不做赘述。

差异备份还原

47）右击数据库 TEST01 -》选择 Tasks -》选择 Restore -》选择 Database 。

48）Source 选择 Device -》点击 ... -》在弹出的窗口中 点击 Add 添加用来恢复的备份文件。

49）选择备份文件 -》点击OK 。

50）可看到备份文件已添加成功 -》点击OK。

51）General 页配置完成后如下图所示

52）Files 页显示如下图

53）在 Options 页，注意Recovery state 选择 “RESTORE WITH NORECOVERY” （默认选项） -》点击OK。

54）在还原数据库弹出框点击确定。

此时，数据库处于 Restoring 状态，不可用，等待着下一步还原操作。

事务日志还原

55）右击数据库 TEST01 -》选择 Tasks -》选择 Restore -》选择 Database 。

56）Source 选择 Device -》点击 ... -》在弹出的窗口中 点击 Add 添加用来恢复的备份文件。

57）选择备份文件 -》点击OK 。

58）可看到备份文件已添加成功 -》点击OK。

59）General 页配置完成后如下图所示

60）在 Options 页，注意Recovery state 选择第一个选项 “RESTORE WITH NORECOVERY” （默认选项） -》点击OK。

61）在还原数据库弹出框点击确定。

至此，数据库 TEST01 还原完成！

验证数据

select * from dbo.class

select * from dbo.classbak

select * from dbo.classbak02

结果均如下：

数据无误！

补充：以上示例中，备份文件的路径可选择文章前面创建的备份设备。
   如 4）中，Destinations on disk 可选择 Backup device 。

---

使用 SQL 备份与还原

使用SQL方式完整备份与还原

语法格式：

--备份数据库
USE master
GO
BACKUP DATABASE <数据库> TO <备份设备>

--还原数据库
USE master
GO
RESTORE DATABASE <数据库> FROM <备份设备>

或

--备份数据库
USE master
GO
BACKUP DATABASE <数据库> TO disk='目录'

--还原数据库
USE master
GO
RESTORE DATABASE <数据库> FROM disk='目录'

使用示例：

--备份数据库
USE master
GO
BACKUP DATABASE TEST01 TO studyDB_backup

--还原数据库
USE master
GO
RESTORE DATABASE TEST01 FROM studyDB_backup

或

--备份数据库
USE master
GO
BACKUP DATABASE TEST01 TO disk='F:\backup\test01_full.bak'

--还原数据库
USE master
GO
RESTORE DATABASE TEST01 FROM disk='F:\backup\test01_full.bak'

使用SQL方式差异备份与还原

语法格式：

--数据库差异备份
USE master
GO
BACKUP DATABASE <数据库> TO <备份设备>
WITH DIFFERENTIAL

--数据库还原
--备份集编号可在对应的备份设备右击选择属性，查看备份介质即可找到备份集的编号
USE master
GO
RESTORE DATABASE <数据库> FROM <备份设备>
WITH FILE=<备份集标号>,NORECOVERY  --NORECOVERY  表示还原未恢复
GO
...
GO
RESTORE DATABASE  <数据库> FROM <备份设备>
WITH FILE=<备份集标号>,RECOVERY --RECOVERY  表示还原已恢复

或

--数据库差异备份
USE master
GO
BACKUP DATABASE <数据库> TO disk='目录' WITH DIFFERENTIAL

--数据库还原
USE master
GO
RESTORE DATABASE <数据库> FROM disk='目录' WITH NORECOVERY  --NORECOVERY  表示还原未恢复
GO
...
GO
RESTORE DATABASE  <数据库> FROM disk='目录' WITH RECOVERY --RECOVERY  表示还原已恢复

使用示例：

--数据库差异备份
USE master
GO
BACKUP DATABASE TEST01 TO studyDB_backup WITH DIFFERENTIAL

--数据库还原
USE master
GO
RESTORE DATABASE TEST01 FROM studyDB_backup WITH FILE=1,NORECOVERY 
GO
RESTORE DATABASE TEST01 FROM studyDB_backup WITH FILE=2,RECOVERY

或

--数据库差异备份
USE master
GO
BACKUP DATABASE TEST01 TO disk='F:\backup\test01_diff.bak' WITH DIFFERENTIAL

--数据库还原
USE master
GO
RESTORE DATABASE TEST01 FROM disk='F:\backup\test01_full.bak' WITH NORECOVERY 
GO
RESTORE DATABASE TEST01 FROM disk='F:\backup\test01_diff.bak' WITH RECOVERY

使用SQL方式事务日志备份与还原

语法格式：

--备份事务日志
USE master
GO
BACKUP LOG <数据库> TO <备份设备>

--恢复数据库备份
--备份集编号可在对应的备份设备右击选择属性，查看备份介质即可找到备份集的编号
USE master
GO
RESTORE DATABASE <数据库> FROM <备份设备>
WITH REPLACE,FILE=<备份集编号>,NORECOVERY   --NORECOVERY  表示还原未恢复
GO
...
GO
RESTORE LOG <数据库> FROM <备份设备>
WITH FILE=<备份集编号>,RECOVERY   --RECOVERY  表示还原已恢复

或

--备份事务日志
USE master
GO
BACKUP LOG <数据库> TO disk='目录'

--恢复数据库备份
USE master
GO
RESTORE DATABASE <数据库> FROM disk='目录' WITH NORECOVERY   --NORECOVERY  表示还原未恢复
GO
...
GO
RESTORE LOG <数据库> FROM disk='目录' WITH RECOVERY   --RECOVERY  表示还原已恢复

使用示例：

--备份事务日志
USE master
GO
BACKUP LOG TEST01 TO studyDB_backup

--恢复数据库备份
USE master
GO
RESTORE DATABASE  TEST01 FROM studyDB_backup
WITH REPLACE,FILE=1,NORECOVERY   --NORECOVERY  表示还原未恢复
GO
RESTORE DATABASE  TEST01 FROM studyDB_backup
WITH REPLACE,FILE=2,NORECOVERY   --NORECOVERY  表示还原未恢复
GO
RESTORE LOG  TEST01 FROM studyDB_backup
WITH FILE=3,RECOVERY   --RECOVERY  表示还原已恢复

或

--备份事务日志
USE master
GO
BACKUP LOG TEST01 TO disk='F:\backup\test01_tran.trn' 

--恢复数据库备份
USE master
GO
RESTORE DATABASE  TEST01 FROM disk='F:\backup\test01_full.bak' WITH REPLACE,FILE=1,NORECOVERY   --NORECOVERY  表示还原未恢复
GO
RESTORE DATABASE  TEST01 FROM disk='F:\backup\test01_diff.bak' WITH NORECOVERY   --NORECOVERY  表示还原未恢复
GO
RESTORE LOG  TEST01 FROM 'F:\backup\test01_tran.trn' WITH RECOVERY   --RECOVERY  表示还原已恢复

---

参考链接：https://blog.csdn.net/gengkui9897/article/details/95186692