备份与恢复介绍

一.备份类型

备份就是对数据库结构和数据对象的复制，以便在数据库遭到破坏时能够及时修复数据库，数据备份是数据库管理员非常重要的工作。系统意外崩溃或者硬件损坏都可能导致数据丢失。数据备份后，一旦系统崩溃或者执行了错误的数据库操作，就可以从备份文件中恢复数据库。
SQL Server中有4种不同的备份类型：分别是完整数据库备份、差异备份、文件和文件组备份、事务日志备份。
1.完整数据库备份
完整数据库备份将备份整个数据库，包括所有对象、系统表、数据以及部分事务日志。由于是对整个数据库的备份，因此这种备份类型速度较慢，并且将占用大量的磁盘空间。这种方法可以快速备份小数据库。
2.差异备份
差异备份基于所包含数据的前一次最新完整备份。差异备份仅捕获自该次完整备份后发生更改的数据。因为只备份改变的内容，所以这种类型的备份速度比较块，可以频繁执行。
3.文件和文件组备份
对数据库中的部分文件和文件组进行备份。当一个数据库很大时，数据库的完整备份会花很多时间，这时可以采用文件和文件组备份。
4.事务日志备份
创建第一个日志备份之前，必须先创建完整备份，事务日志备份所有数据库修改的记录，用来在还原操作期间提交完成的事务以及回滚未完成的事务。事务日志备份比完整备份节省时间和空间，利用事务日志进行恢复时，可以指定恢复到某一个时间，而完整备份和差异备份做不到这一点。

二.恢复模式

恢复模式可以保证在数据库发生故障的时候恢复相关的数据库，SQL Server中包含了3种恢复模式，分别是简单恢复模式、完整恢复模式和大容量日志恢复模式。
1.简单恢复模式
简单恢复模式是可以将数据库恢复到上一次备份，这种模式的备份策略由完整备份和差异备份组成。简单恢复模式能够提高磁盘的可用空间，但该模式无法将数据库还原到故障点或特定时间点。
2.完整恢复模式
完整恢复模式可以将数据库恢复到故障点或时间点。这种模式下，所有操作被写入日志，所有可以将数据库还原到特定时间点。这种模式下可以使用的备份策略包括完整备份、差异备份及事务日志备份。
3.大容量日志恢复模式
与完整恢复模式类似，大容量日志恢复模式使用数据库和日志备份来恢复数据库。使用这种模式可以在大容量操作和大批量数据装载时提供最佳性能和最少的日志使用空间。

三.备份设备

1.创建备份设备

--disk备份介质，mydiskdump备份设备名称
EXEC sp_addumpdevice 'disk','mydiskdump','E:\database\my_db1.back'

2.查看备份设备

sp_helpdevice

3.删除备份设备

--删除备份设备mydiskdump
EXEC sp_dropdevice mydiskdump
--服务器创建了备份文件，要同时删除物理文件
EXEC sp_dropdevice mydiskdump,delfile

四.使用T-SQL备份数据库

1.创建完整数据库备份

BACKUP DATABASE my_db
TO mydiskdump --mydiskdump是备份设备的名称，这里可以填写为备份地址
WITH INIT,--覆盖原有
NAME='my_db数据库备份',
DESCRIPTION='该文件为my_db数据库的完整备份'

2.创建差异数据库备份

--创建差异备份时使用NOINIT选项，该选项表示备份数据追加到现有备份集，避免覆盖----已经存在的完整备份。
BACKUP DATABASE my_db
TO mydiskdump
WITH DIFFERENTIAL,NOINIT,
NAME='my_db数据库差异备份',
DESCRIPTION='该文件为my_db数据库的差异备份'

3.文件和文件组备份

BACKUP DATABASE my_db
FILEGROUP='SecondFileGroup'
TO mydiskdump
WITH NAME='my_db文件组备份',
DESCRIPTION='数据库的文件组备份'

4.事务日志备份
使用事务日志备份，除了运行还原备份事务外，还可以将数据库恢复到故障点或特定时间点。

BACKUP LOG my_db
TO mydiskdump
WITH NOINIT,
NAME='my_db数据库事务日志备份',
DESCRIPTION='数据库事务日志备份'

五.还原数据库

还原数据库的方式，在还原时也可以使用4种方式，分别是完整备份还原、差异备份还原、事务日志还原，以及文件和文件组备份还原。
1.检查设备是否有误

RESTORE VERIFYONLY FROM mydiskdump
--检查mydiskdump备份设备中第二个备份集是否正确，可指定FILE值为2。
RESTORE VERIFYONLY FROM mydiskdump备份 WITH FILE=2

2.完整备份还原还原数据库

--REPLACE参数，表示还原my_db数据库时，覆盖当前数据库。
--根据备份设备还原
RESTORE DATABASE my_db FROM mydiskdump
WITH REPLACE
--根据备份文件地址还原
RESTORE DATABASE my_db FROM DISK='E:\database\my_db.bak'
WITH REPLACE

3.差异备份还原
差异备份还原与完整还原的语法基本一样，只是在还原差异备份时，必须先还原完整备份，再还原差异备份。

RESTORE DATABASE my_db FROM mydiskdump
WITH FILE = 1,NORECOVERY,REPLACE
GO
RESTORE DATABASE my_db FROM mydiskdump
WITH FILE = 2

4.事务日志备份还原
与差异备份还原类似，事务日志备份还原是只要知道它在备份设备中的位置即可，还原事务日志备份之前，必须先还原在其之前的完整备份，除了最后一个还原操作，其他所有操作必须加上NORECOVERY或STANDBY参数。

RESTORE DATABASE my_db FROM mydiskdump
WITH FILE=1,NORECOVERY,REPLACE
RESTORE DATABASE my_db FROM mydiskdump
WITH FILE=2
--RESTORE LOG可以还原事务日志备份
RESTORE DATABASE my_db FROM mydiskdump
WITH FILE=1,NORECOVERY,REPLACE
RESTORE LOG my_db FROM mydiskdump
WITH FILE=1

5.文件和文件组备份还原
在RESTORE DATABASE语句中加上FILE或者FILEGROUP参数之后可以还原文件和文件组备份，在还原文件和文件组之后，还可以还原其他备份来获得最近的数据库状态。

--还原文件和文件组时，同时使用第个备份集来还原事务日志备份
RESTORE DATABASE my_db 
FILEGROUP ='PRIMARY'
FROM mydiskdump
WITH REPLACE,NORECOVERY
GO
RESTORE LOG my_db FROM mydiskdump
WITH FILE=6
GO

6.将文件还原到新位置上

--sample_db和sample_log是原数据库的逻辑文件名称
RESTORE DATABASE new_mydb --新数据库名称
FROM mydiskdump
WITH FILE = 1,
MOVE 'sample_db' TO 'D:\SQL Server 2016\DATA\mydb.mdf',
MOVE 'sample_log' TO 'D:\SQL Server 2016\DATA\mydb_log.ldf'