SQL -- 备份及恢复

为了最大限度地降低灾难性数据丢失的风险，需要定期备份数据库以保留对数据所做的修改。

一、备份

1-1 概述

什么是备份

备份是指数据库管理员定期或不定期地将数据库部分或全部内容复制到磁带或磁盘上进行保存的过程。

为什么用备份

当遇到介质故障、用户错误（例如：误删库或表）、硬件故障（例如，磁盘驱动器损坏或服务器报废）、自然灾害等造成灾难性数据丢失时，可以利用备份进行数据库的恢复。备份数据库是可靠保护 SQL 数据库的唯一方法。

1-2 类型

备份的四种类型
（1）. 完整备份：

• 完整备份指备份数据库中所有数据，包括事务日志。
• 完整备份与差异备份与事务日志备份相比，完整备份占用的存储空间多，备份时间长。所以完整数据库备份的创建频率通常比差异备份或事务日志备份低。
• 完整备份适用于备份容量小或数据库中数据的修改较小的数据库。
• 完整备份是差异备份和事务日志备份的基准。

（2）差异备份：

• 差异备份是完整备份的补充，只备份上次完整备份之后更改的数据。常用的数据库备份方式。
• 差异备份相对与完整备份，差异备份的数据量较少、速度较快。
• 差异备份适用于修改频繁的数据库。
• 数据库还原最后一次差异备份的数据，先让数据库还原前一次的完整备份，再还原最后一次差异备份。

（3） 事务日志备份：

• 事务日志备份事务日志里的内容。事务日志记录了上一次完整备份、差异备份或事务日志备份后数据库的所有变动过程。
• 每个事务日志备份都包括创建备份时处于活动状态的部分事务日志，以有先前事务日志备份中未备份的所有日志记录。
• 使用事务日志备份将数据库恢复到特定的即时点或恢复到故障。
• 事务备份生成的文件较小、占用时间较短、创建频率较频繁。

（4）文件和文件组备份：

• 创建数据库时，创建了多个文件或文件组，可以使用该备份方式。
• 使用文件或文件组备份只备份数据库中的某些文件，当数据库文件非常庞大时，只备份一个或几个文件或文件组，可以分多次备份数据库，避免备份时间过长。
• 当数据库中某个或某些文件损坏，只恢复损坏的文件或文件组备份即可。

二、恢复

1-1 概述

什么是恢复

恢复模式用于控制数据库备份和还原的基本行为。

1-2 类型

恢复的三种类型
（1）. 简单恢复模式：

• 简单恢复模式，数据库会自动把不活动的日志删除，因此减少事务的管理开销，在此模式下不能进行事务日志备份，因此，使用简单恢复模式只能将数据库恢复到最后一次备份时的状态，不能恢复到故障点或特定的即时点。
• 此模式只用于对数据库数据安全要求不高的数据库，并且在该模式下，数据库只能做完整和差异备份。

（2）完整恢复模式：

• 默认恢复模式。完整记录下操作数据库的每一个步骤。
• 完整恢复模式可以将数据库恢复到一个特定的时间点，这个时间可以是最近一次可用的备份、一个特定的日期和时间或者是标记的事务。

（3） 大容量日志恢复模式：

• 对大容量操作进行最小日志记录，以节省日志文件的空间
• 一般只有在需要大量数据操作时才将恢复模式设置为大容量日志恢复模式，数据处理完毕之后，马上将恢复模式改回完整恢复模式。

三、 T-SQL语句

1-1 备份设备

1-1-1 创建备份设备

基本语法格式

sp_addumpdevice{'device_type'}
[,'logical_name'][,'physical_name'][,{{controller_type | 'device_status'}}]

• device_type：设备类型
• logical_name：设备的逻辑名称
• physical_name：设备的实际名称

---

举例：
要求：创建一个名为 jxgldisk 的磁盘备份设备，其物理名称为 “e:\jxgltdisk ”.

T-SQL代码如下：

EXEC sp_addumpdevice 'disk','jxgldisk','e:\jxgldisk.bak'

1-1-2 删除备份设备

基本语法格式

sp_dropdevice[,'logical_name'][,'']

• device_type：设备类型
• delfile：是否删除物理备份文件，如果指定 delfile，则删除物理备份文件。

---

举例：
要求：使用存储过程 sp_dropdevice 删除名称为 jxgldisk 的备份设备，同时删除物理文件。

T-SQL代码如下：

exec sp_dropdevice jxgldisk,delfile

1-2 备份数据库

1-1-1 完整备份

基本语法格式

BACKUP DATEBASE database_name
TO<backup_device>[  n]
WITH
 [[,]NAME=backup_set_name]
 [[,]DESCRIPITION='TEXT']
 [[,]{INIT|NOINIT}]
 [[,]{COMPRESSION|NO_COMRESSION}
 ]

• database_name：数据库名字
• backup_device：备份的目标设备
• WITH：指定备份选项。如果省略则为完整备份
• NAME：指定设备名称
• DESCRIPITION：指定设备的描述
• INIT|NOINIT：表示覆盖|追加方式
• COMPRESSION|NO_COMRESSION：表示启用|不启用备份压缩功能

---

举例：
要求：把“StuInfo”数据库完整备份到 jxgldisk 的上。

T-SQL代码如下：

BACKUP DATEBASE StuInfo to jxgldisk 

1-1-2 差异备份

基本语法格式

BACKUP DATEBASE database_name
TO<backup_device>[  n]
WITH
 DIFFERENTIAL
 [[,]NAME=backup_set_name]
 [[,]DESCRIPITION='TEXT']
 [[,]{INIT|NOINIT}]
 [[,]{COMPRESSION|NO_COMRESSION}
 ]

• DIFFERENTIAL：指定差异备份
• 其他参数与完整备份一致。

---

举例：
要求：把“StuInfo”数据库的差异备份到 jxgldisk 的上。

T-SQL代码如下：

BACKUP DATEBASE StuInfo to jxgldisk 
WITH DIFFERENTIAL

1-1-3 事务日志备份

基本语法格式

BACKUP LOG database_name
TO<backup_device>[  n]
WITH
 [[,]NAME=backup_set_name]
 [[,]DESCRIPITION='TEXT']
 [[,]{INIT|NOINIT}]
 [[,]{COMPRESSION|NO_COMRESSION}
 ]

• LOG：备份事务日期。必须创建完整备份，才能创建第一个事务日志备份。
• 其他参数与完整备份一致。

---

举例：
要求：把“StuInfo”数据库的日志备份到 jxgldisk 的上。

T-SQL代码如下：

BACKUP LOG StuInfo to jxgldisk 

1-3 还原数据库

1-1-1 完整备份还原

基本语法格式

RESTOE DATEBASE database_name
FROM<backup_device>[  n]
WITH
[FILE=file_number]
,[[,]MOVE 'logical_file_name'TO
'operating_system_file_name']
[,....n]
[[,]{RECOVERY|NORECOVERY|STANDBY=
{standby_file_name}}]
[[,]REPLACE]
 ]

其中

<backup_device>::=
{
 {logical_backup_device_name}
 |
 {DISK|TYPE}={'physical_backup_device_name'}
}

---

举例：
要求：把“StuInfo”数据库完整备份还原。

T-SQL代码如下：

RESTORE DATEBASE StuInfo FROM jxgldisk 
WITH FILE=1,norecovery

1-1-2 差异备份还原

差异备份还原与完整备份还原的语法基本一致，必须先还原完整备份，才能进行差异备份还原。
T-SQL代码如下：

-- 先完整还原
RESTORE DATEBASE StuInfo FROM jxgldisk 
WITH FILE=1,norecovery

-- 再差异还原
RESTORE DATEBASE StuInfo FROM jxgldisk 
WITH FILE=2,norecovery

举例：
要求：把“StuInfo”数据库完整备份还原。

T-SQL代码如下：

RESTORE DATEBASE StuInfo FROM jxgldisk 
WITH FILE=1,norecovery

1-1-3 事务日志备份还原

基本语法格式

RESTOE LOG database_name
FROM<backup_device>[  n]
WITH
[FILE=file_number]
,[[,]MOVE 'logical_file_name'TO
'operating_system_file_name']
[,....n]
[[,]{RECOVERY|NORECOVERY|STANDBY=
{standby_file_name}}]
[[,]REPLACE]
 ]

其中

<backup_device>::=
{
 {logical_backup_device_name}
 |
 {DISK|TYPE}={'physical_backup_device_name'}
}
<file_or_filegroup>::=
{FILE=logical_file_name|FILEGROUP=logical_filegroup_name}

---

举例：
要求：把“StuInfo”数据库事务日志还原。

T-SQL代码如下：

RESTORE LOG StuInfo FROM jxgldisk 
WITH FILE=3,recovery