SQL Server 2012 数据库备份还原「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

1.数据库备份

    数据库备份，即从SQL Server数据库或其事务日志中将数据或日志记录复制到相应的设备，以创建数据副本或事务日志副本。数据还原用于将指定SQL Server备份中的所有数据和日志复制到指定数据库，然后通过应用记录的更改使该数据在时间上向前移动，以回滚备份中记录的所有事物。
    设计一个好的备份和还原策略需考虑多方面的因素，包括备份内容、备份计划、备份介质、备份设备、备份类型和恢复模式。在SQL Server 2012系统中，常见的备份类型有完整备份、差异备份、事务日志备份、文件和文件组备份。
    “恢复模式”是一种数据库属性，它控制如何记录事务、事务日志是否需要或允许备份，以及可以使用哪些类型的还原操作。有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。通常情况下，数据库使用简单恢复模式或完整恢复模式。
    ① 简单恢复模式：数据库记录大多数事务，并不会记录所有的事务，数据库在备份之后，自动截断事务日志，即把不活动的事务日志删除。因此，不支持事务日志备份，也不能恢复到出现故障的时间点，具有较高的安全风险，建议只有对数据安全性要求不高的数据库使用该模式。
    ② 完整恢复模式：数据库完整地记录了所有的事务，并保留所有事务的详细日志。支持恢复到出现故障的时间点。该模式可在最大范围内防止出现故障时丢失数据，为数据安全提供了全面的保护。建议对数据安全性、可靠性要求高的数据库使用该恢复模式。
    ③ 大容量日志恢复模式：数据库不会对所有事务做完整详细的记录，只对大容量操作做最少的记录。通常情况下，只有在要进行大容量操作之前，才改用该恢复模式，大容量操作结束之后，再设置回原来的恢复模式。

2.创建备份设备

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

使用SSMS工具创建备份设备

1. 展开数据库实例，打开“服务器对象”，右击“备份设备”，选择“新建备份设备”
   
2. 在备份设备窗口中，输入备份设备名称，选择备份设备路径和文件名，点击“确定”，完成备份设备的创建
   
3. 备份设备创建成功
   

使用SQL方式创建备份设备

语法格式：

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

使用示例：

use master
go
EXEC sp_addumpdevice 'disk' 'schoolDB_backup' 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\schoolDB_backup.bak'

3.完整备份与还原

使用SSMS工具完整备份与还原

1. 右击需要备份的数据库，选择“任务”，再选择“备份”
   
2. 在“备份数据库”窗口中选择备份类型为“完整”，点击“删除”按钮，将目标下的原路径删除
   
3. 点击“添加”按钮，弹出选择备份目标对话框，选择备份设备并选中创建的备份设备，点击“确定”按钮返回
   
4. 在选择页中选择“选项”，备份介质中选择“覆盖所有现有备份集”，点击确定完成数据库完整备份
   
5. 删除源数据库，要恢复完整备份的数据库，右击“数据库”，选择“还原数据库”
   
6. 在“还原数据库”窗口中，选择源为“设备”，点击“…”按钮
   
7. 弹出“选择备份设备”窗口，点击“添加”按钮，选择对应的备份设备，返回“还原数据库”窗口
   
8. 选择需要还原的数据库，勾选需要还原的备份文件，点击“确定”进行还原
   
9. 数据库还原成功
   

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

语法格式：

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

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

使用示例：

--备份数据库
USE master
GO
BACKUP DATABASE schoolDB TO schoolDB_backup

--还原数据库
USE master
GO
RESTORE DATABASE schoolDB FROM schoolDB_backup

4.差异备份与还原

使用SSMS工具差异备份与还原

1. 创建一份完整数据库备份
2. 修改数据库中的数据或结构，本处将student表中的数据复制到student_new表中
   
3. 右击需要备份的数据库，选择“任务”，再选择“备份”
   
4. 在“备份数据库”窗口中选择备份类型为“差异”，如果目标不是备份设备，则参照完整备份处理
   
5. 在选择页中选择“选项”，备份介质中选择“追加到现有备份集”，点击确定完成数据库差异备份
   
6. 删除源数据库，要恢复备份的数据库，右击“数据库”，选择“还原数据库”
   
7. 在“还原数据库”窗口中，选择源为“设备”，点击“…”按钮
   
8. 弹出“选择备份设备”窗口，点击“添加”按钮，选择对应的备份设备，返回“还原数据库”窗口
   
9. 选择需要还原的数据库，勾选需要还原的备份文件，点击“确定”进行还原
   
10. 数据库还原成功
    

使用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 schoolDB TO schoolDB_backup
WITH DIFFERENTIAL

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

5.事务日志备份与还原

使用SSMS工具事务日志备份与还原

1. 创建一份完整数据库备份
2. 修改数据库中的数据或结构，本处将student表中的数据复制到student_new表中
   
3. 右击需要备份的数据库，选择“任务”，再选择“备份”
   
4. 在“备份数据库”窗口中选择备份类型为“事务日志”，如果目标不是备份设备，则参照完整备份处理
   
5. 在选择页中选择“选项”，备份介质中选择“追加到现有备份集”，点击确定完成数据库事务日志备份
   
6. 删除新创建的数据表，使用事务日志备份恢复数据库，右击“数据库”，选择“还原数据库”
   
7. 在“还原数据库”窗口中，选择源为“设备”，点击“…”按钮
   
8. 弹出“选择备份设备”窗口，点击“添加”按钮，选择对应的备份设备，返回“还原数据库”窗口
   
9. 选择需要还原的数据库，勾选需要还原的备份文件，点击“确定”进行还原
   
10. 在选择页中选择“选项”，在还原选项中勾选“覆盖现有数据库”，并在服务器连接中勾选“关闭到目标数据库的现有连接”，点击“确定”进行数据库还原
    
11. 数据库还原成功
    

使用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 schoolDB TO schoolDB_backup

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