SQL Server教程 - SQL Server 备份与恢复（BACKUP&RESTORE）

更新记录
转载请注明出处：https://www.cnblogs.com/cqpanda/p/16539344.html
2022年8月5日 发布。
2022年7月2日 从笔记迁移到博客。

数据库备份说明(BACKUP&RESTORE)

备份就是对数据库结构和数据对象的复制，以便在数据库遭到破坏时能够及时修复数据库，数据备份是数据库管理员非常重要的工作。系统意外崩溃或者硬件的损坏都可能导致数据的丢失，如软件或硬件系统的瘫痪、人为操作失误、数据磁盘损坏或者其他意外事故等。因此 SQL Server管理员应该定期地备份数据库，使得在意外情况发生时，尽可能地减少损失。数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中恢复数据库。数据库恢复是指将数据库备份加载到系统中的过程。系统在恢复数据库的过程中，自动执行安全性检查、重建数据库结构以及完成填写数据库内容。

数据库备份类型

完整数据库备份(Full Backup)

完整数据库备份将备份整个数据库，包括所有的对象、系统表、数据以及部分事务日志，备份SQL Server 将复制数据库中的一切。完整备份可以还原数据库在备份操作完成时的完整数据库状态。由于是对整个数据库的备份，因此这种备份类型速度较慢，并且将占用大量磁盘空间。在对数据库进行备份时，所有未完成的或发生在备份过程中的事务都将被忽略。这种备份方法可以快速备份小数据库。完整备份的速度较慢、占用大量磁盘空间。

差异备份(Differentia Backup)

差异备份基于所包含数据的前一次最新完整备份。差异备份仅捕获自该次完整备份后发生更改的数据。因为只备份改变的内容，所以这种类型的备份速度比较快，可以频繁地执行，差异备份中也备份了部分事务日志。

文件和文件组备份(File Backup)

文件和文件组的备份方法可以对数据库中的部分文件和文件组进行备份。当一个数据库很大时，数据库的完整备份会花很多时间，这时可以采用文件和文件组备份。在使用文件和文件组备份时，还必须备份事务日志，所以不能在启用【在检查点截断日志】选项的情况下使用这种备份技术。文件组是一种将数据库存放在多个文件上的方法，并运行控制数据库对象存储到那些指定的文件上，这样数据库就不会受到只存储在单个硬盘上的限制，而是可以分散到许多硬盘上。利用文件组备份，每次可以备份这些文件当中的一个或多个文件，而不是备份整个数据库。

事务日志备份(Log Backup)

创建第一个日志备份之前，必须先创建完整备份，事务日志备份所有数据库修改的记录，用来在还原操作期间提交完成的事务以及回滚未完成的事务，事务日志备份记录备份操作开始时的事务日志状态。事务日志备份比完整数据库备份节省时间和空间，利用事务日志进行恢复时，可以指定恢复到某一个时间，而完整备份和差异备份做不到这一点。

备份设备管理

备份设备类型

备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质，备份数据库之前，必须首先指定或创建备份设备。

备份设备可以是：磁盘、磁带、逻辑备份设备

磁盘备份设备

磁盘备份设备是存储在硬盘或者其他磁盘媒体上的文件，与常规操作系统文件一样，可以在服务器的本地磁盘或者共享网络资源的原始磁盘上定义磁盘设备备份。如果在备份操作将备份数据追加到媒体集时磁盘文件已满，则备份操作会失败。备份文件的最大大小由磁盘设备上的可用磁盘空间决定，因此，备份磁盘设备的大小取决于备份数据的大小。

磁带备份设备

磁带备份设备的用法与磁盘设备相同，磁带设备必须物理连接到SQL Server实例运行的计算机上。在使用磁带机时，备份操作可能会写满一个磁带，并继续在另一个磁带上进行。每个磁带包含一个媒体标头。使用的第一个媒体称为“起始磁带”，每个后续磁带称为“延续磁带”，其媒体序列号比前一磁带的媒体序列号大。将数据备份到磁带设备上，需要使用磁带备份设备或者微软操作系统平台支持的磁带驱动器，对于特殊的磁带驱动器，则需要使用驱动器制作商推荐的磁带。

逻辑备份设备

逻辑备份设备是指向特定物理备份设备(磁盘文件或磁带机)，具有可选的用户定义名称。通过逻辑备份设备，可以在引用相应的物理备份设备时使用间接寻址。逻辑备份设备可以更简单、有效地描述备份设备的特征。相对于物理设备的路径名称，逻辑设备备份名称较短。逻辑备份设备对于标识磁带备份设备非常有用。通过编写脚本使用特定逻辑备份设备，这样可以直接切换到新的物理备份设备。切换时，首先删除原来的逻辑备份设备，然后定义新的逻辑备份设备，新设备使用原来的逻辑设备名称，但映射到不同的物理备份设备。

添加备份设备-使用SSMS

打开SSMS窗口。在【对象资源管理器】窗口中，依次打开服务器节点下面的【服务器对象】→【备份设备】节点，右击【备份设备】节点，从弹出的快捷菜单中选择【新建备份设备】菜单命令。

添加备份设备-使用T-SQL

使用系统存储过程 sp_addumpdevice来创建备份设备。sp_addumpdevice 也可以用来添加备份设备，这个存储过程可以添加磁盘或磁带设备。

EXEC sp_addumpdevice @devtype = 'devicetype'
@logicalname = 'logical_name',
@physicalname = 'physical_name',

说明：
devtype表示备份设备的类型，磁盘使用'disk'
logicalname表示备份设备的逻辑名称
physicalname表示备份设备的物理路径

实例：添加一个名为mydiskdump的磁盘备份设备，其物理名称为 d:\dump\testdump.bak

EXEC sp_addumpdevice 'disk', 'mydiskdump', 'd:\dump\testdump.bak';

查看备份设备-使用T-SQL

EXEC sp_helpdevice

说明：查看服务器上所有备份设备的信息

删除备份设备-使用T-SQL

当备份设备不再需要使用时，可以将其删除，删除备份设备后，备份中的数据都将丢失，删除备份设备使用系统存储过程 sp_dropdevice,该存储过程同时能删除操作系统文件。

EXEC sp_dropdevice @logicalname = 'device',
[@delfile= 'delfile']

说明：
logicalname表示备份设备的逻辑名称。
delfile表示同时删除物理备份文件，可以指定为'DELFILE'表示删除。

实例：删除备份设备 mydiskdump(不删除设备文件)

EXEC sp_dropdevice mydiskdump

实例：删除备份设备 mydiskdump(同时删除设备文件)

EXEC sp_dropdevice mydiskdump, delfile

备份数据库

完整备份-使用SSMS

进入备份界面

选择具体参数：

完整备份-使用T-SQL

BACKUP DATABASE 数据库名
TO 备份设备,...
[WITH]
[NOINIT | INIT]
[COPY_ONLY]
[NAME = 备份文件名称]
[DESCRIPTION = '备份文件描述']
[PASSWORD = '备份集密码']
[EXPIREDATE = '日期']
[RETAINDAYS = days...]
[BLOCKSIZE = 块大小]

说明：
COPY_ONLY 表示只是一份数据，而不影响正常的备份顺序
INIT 表示覆盖所有备份集
NOINIT 表示追加到最新备份集，默认选项
EXPIREDATE 表示允许覆盖该备份的备份集的日期
RETAINDAYS 表示必须经过多少天蔡锷可以覆盖该备份集

实例：

BACKUP DATABASE [PandaDatabase]
TO DISK =
N'C:\TEST\test.back'
WITH 
    NOFORMAT,
    NOINIT,
    NAME = N'PandaDatabase-2020-Full Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10
GO

实例：创建完整备份，备份设备为创建好的【test数据库备份】本地备份设备

BACKUP DATABASE test
TO test数据库备份
WITH INIT,
NAME='test数据库完整备份'，
DESCRIPTION='该文件为test数据库的完整备份'

差异备份-使用SSMS

进入备份界面

选择具体参数：

差异备份-使用T-SQL

BACKUP DATABASE 数据库名
TO 备份设备,...
WITH DIFFERENTIAL
[COPY_ONLY]
[NAME = 备份文件名称]
[NOINIT | INIT]
[DESCRIPTION = '备份文件描述']
[PASSWORD = '备份集密码']
[EXPIREDATE = '日期']
[RETAINDAYS = days...]

实例：

BACKUP DATABASE [PandaDatabase]
TO DISK =
N'C:\TEST\test_diff.back'
WITH
    DIFFERENTIAL,
    NOFORMAT,
    NOINIT,
    NAME = N'PandaDatabase-2020-Differential Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10
GO

实例：对test做一次差异数据库备份

BACKUP DATABASE test
TO test数据库备份
WITH DIFFERENTIAL,NOINIT,
NAME='test数据库差异备份',
DESCRIPTION='该文件为test数据库的差异备份'

文件和文件组备份-使用SSMS

在【对象资源管理器】窗口中的服务器节点下，依次打开【数据库】→【test_db】节点，右击【test_db】数据库，从弹出的快捷菜单中选择【属性】茱单命令，打开【数据库属性】窗口。在【数据库属性－test_db】对话框中，选择左侧的【文件组】选项，在右侧选项卡中，单击【添加】按钮，在【名称】文本框中输入SecondFileGroup。

选择【文件】选项，在右侧选项卡中，单击【添加】按钮，然后设置

文件和文件组备份-使用T-SQL

BACKUP DATABASE 数据库名
FILEGROUP = '文件组名'
TO 备份设备,...
[WITH] DIFFERENTIAL
[COPY_ONLY]
[NAME = 备份文件名称]
[NOINIT | INIT]
[DESCRIPTION = '备份文件描述']
[PASSWORD = '备份集密码']
[EXPIREDATE = '日期']
[RETAINDAYS = days...]

实例：将 test数据库中添加的文件组SecondFileGroup 备份到本地备份设备【test数据库
备份】

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

事务日志备份-使用T-SQL

BACKUP LOG 数据库名
TO 备份设备,...
[WITH]
[NAME = 备份文件名称]
[DESCRIPTION = '备份文件描述']

实例：

BACKUP LOG [PandaDatabase]
TO DISK
= N'C:\TEST\test-log-backup.trn'
WITH
    NOFORMAT,
    NOINIT,
    NAME = N'PandaDatabase-Log-Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10
GO

实例：

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

数据库恢复类型

恢复模式可以保证在数据库发生故障的时候恢复相关的数据库，SQL Server 2019中包括3种恢复模式，分别是简单恢复模式、完整恢复模式和大容量日志恢复模式。不同恢复模式在备份、恢复方式和性能方面存在差异，而且不同的恢复模式对避免数据损失的程度也不同。

简单恢复模式

简单恢复模式可以将数据库恢复到上一次的备份，这种模式的备份策略由完整备份和差异备份组成。简单恢复模式能够提高磁盘的可用空间，但是该模式无法将数据库还原到故障点或特定的时间点。对于小型数据库或者数据更改程序不高的数据库，通常使用简单恢复模式。简单恢复模式可以最大限度地减少事务日志的管理开销，因为它不备份事务日志。

注意：
若数据库损坏，则简单恢复模式将面临极大的工作丢失风险。
数据只能恢复到己丢失数据的最新备份。
简单恢复模式并不适合生产系统，简单恢复模式用于测试和开发数据库。

master、msdb和tempdb 使用的是简单恢复模式。

完全恢复模式

完整恢复模式可以将数据库恢复到故障点或时间点。这种模式下，所有操作被写入日志，例如大容量的操作和大容量的数据加载，数据库和日志都将被备份，因为日志记录了全部事务，所以数据库的备份与恢复。

model数据库使用的是完整恢复模式。因为model是所有新建立数据库的模板数据库，所以用户数据库默认也是使用完整恢复模式。

差异备份还原

完整备份还原后，可以执行差异备份还原

大容量日志恢复模式

与完整恢复模式类似，大容量日志恢复模式使用数据库和日志备份来恢复数据库。使用这种模式可以在大容量操作和大批量数据装载时提供最佳性能和最少的日志使用空间。这种模式下，日志只记录多个操作的最终结果，而并非存储操作的过程细节，所以日志越小，大批量操作的速度也就越快。如果事务日志没有受到破坏，除了故障期间发生的事务以外，SQL Server能够还原全部数据，但是该模式不能恢复数据库到特定的时间点。

文件和文件组备份还原

一般不使用这种恢复，除非文件或文件组损坏

恢复数据库

配置恢复模式

用户可以根据实际需求选择适合的恢复模式，选择特定的恢复模式的操作步骤如下：
使用登录账户连接到SQL Server 2019,打开SSMS图形用户界面的管理工具，在【对象资源管理器】窗口中，打开服务器节点，依次选择【数据库】→【test】节点，右击test 数据库，从弹出的快捷菜单中选择【属性】菜单命令。打开【数据库属性－test】对话框，选择【选项】选项，打开右侧的选项卡，在【恢复模式】下拉列表框中选择其中的一种恢复模式即可

还原数据库-使用SSMS

选择具体参数：

验证还原文件的有效性-使用SSMS

验证还原文件的有效性-使用T-SQL

在还原数据库之前记得验证以下还原文件是否正确无误

RESTORE VERIFYONLY
FROM 备份设备或备份逻辑文件名 | DISK 备份物理文件名,....
[WITH]
[MOVE '备份文件逻辑名' TO '系统备份文件名' ....]
[FILE = Number]
[PASSWORD = '密码']
[MEDIANAME = '']
[MEDIAPASSWORD = '密码']
[CHECKSUM | NO_CHECKSUM]
[STOP_ON_ERROR | CONTINUE_AFTER_ERROR]
[STATS = PERCENTAGE]

实例：验证还原设备

RESTORE VERIFYONLY FROM test数据库备份

还原数据库-T-SQL

说明：
STATS表示显示一个百分比进度
恢复数据库-使用T-SQL
还原整个数据库

RESTORE DATABASE 数据库名
FROM 备份设备或备份逻辑文件名 | DISK 备份物理文件名,....
[WITH]
[PASSWORD = '密码']
[CHECKSUM | NO_CHECKSUM]
[STOP_ON_ERROR | CONTINUE_AFTER_ERROR]

实例：使用备份设备还原数据库

RESTORE DATABASE test FROM test数据库备份

实例：还原数据库从指定的文件

RESTORE DATABASE test
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
Backup\test数据库备份.bak'
WITH REPLACE

实例：强制还原并替换原有数据库

RESTORE DATABASE [PandaDatabase]
FROM DISK = N'C:\TEST\test.back'
WITH 
    FILE = 1,
    REPLACE,
    NOUNLOAD,
    STATS = 5
GO

实例：还原但不覆盖原有数据库

RESTORE DATABASE [PandaDatabase]
FROM DISK = N'C:\TEST\test.back'
WITH 
    FILE = 1,
    NORECOVERY,
    NOUNLOAD,
    STATS = 5
GO

还原事务日志

RESTORE LOG 数据库名
FROM 备份设备或备份逻辑文件名 | DISK 备份物理文件名,....
[WITH]
[PASSWORD = '密码']
[CHECKSUM | NO_CHECKSUM]
[STOP_ON_ERROR | CONTINUE_AFTER_ERROR]

差异备份还原-T-SQL

差异备份还原与完整备份还原的语法基本一样，只是在还原差异备份时，必须先还原完整备份，再还原差异备份。完整备份和差异备份可能在同一个备份设备中，也可能不在同一个备份设备中。如果在同一个备份设备中应使用file参数指定备份集。无论备份集是否在同一个备份设备中，除了最后一个还原操作，其他所有还原操作都必须加上NORECOVERY或STANDBY参数。

RESTORE DATABASE test FROM test数据库备份
WITH FILE =1,NORECOVERY,REPLACE
RESTORE DATABASE test FROM test数据库备份
WITH FILE = 2

事务日志备份还原-T-SQL

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

RESTORE DATABASE test FROM test数据库备份
WITH FILE=1,NORECOVERY, REPLACE
GO
RESTORE DATABASE test FROM test数据库备份
WITH FILE=4

文件和文件组备份还原-T-SQL

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

使用名称为【test 数据库备份】的备份设备来还原文件和文件组，同时使用第7个备份集来还原事务日志备份。

RESTORE DATABASE test
FILEGROUP = 'PRIMARY'
FROM test数据库备份
WITH REPLACE,NORECOVERY
GO
RESTORE LOG test
FROM test数据库备份
WITH FILE =7

脱机与联机数据库

脱机-使用SSMS

脱机常用于数据库迁移操作

比如：开发机器上的数据库直接通过脱机迁移数据库文件到服务器上

联机-使用SSMS

分离与附加数据库

说明

分离和附加用于将数据库文件进行移动
分离数据库是指将数据库从SQL Server实例中删除，但数据文件和事务日志文件中保持不变
附加数据库时，该数据库必须已分离且所有数据文 (MDF和 NDF文件)都必须可用

分离-使用SSMS

分离-使用T-SQL

USE master;
EXEC sp_detach_db @dbname = '数据库名';

附加-使用SSMS

选择指定数据库文件，然后点击确定

附加-使用T-SQL

USE master;
CREATE DATABASE [数据库名] ON
(FILENAME = '文件路径.mdf'),
(FILENAME = '文件路径.ndf'),
(FILENAME = '文件路径.ldf')
FOR ATTACH;

检查数据库文件

当从其他环境附加数据库到生产环境时

记得检查数据库文件是否完整以及是否存在恶意SQL代码

检测数据库一致性

DBCC CHECKDB;

导入导出数据

导入数据-使用SSMS

导出数据-使用SSMS

选择要导出的数据源等参数

选择要导出的数据目标等参数

收缩数据库-使用SSMS

收缩可以选择收缩整个数据库或者单个数据库文件

可以通过收缩单个文件并将数据移动到另一个文件达到删除数据库文件的目的

或者直接在数据库属性页面设置

数据库生成脚本-使用SSMS

架构管理

架构含义

架构是指包含表、视图、存储过程等数据库对象的容器
服务器实例包含数据库，数据库包含架构

这意味着在一个数据库下，可以有不同的架构
比如：一个数据库下可以同时存在dbo.TableName表和panda.TableName表
也可以认为架构是数据库下的命名空间，即是容器，也是命名空间

创建架构-使用T-SQL

CREATE SCHEMA 架构名
[AUTHORIZATION 拥有者];

创建架构-使用SSMS

修改架构-使用SSMS

点击架构的名字即可修改

修改架构-使用T-SQL

ALTER SCHEMA 架构名 TRANSFER;

删除架构-使用SSMS

删除架构-使用T-SQL

DROP SCHEMA 架构名;

设置数据库自动收缩-使用SSMS

设置自动备份计划-使用SSMS

在SQL Server配置管理器中开启SQL Server Agent

新建维护计划向导

设置备份计划的名称和执行时间

选择要执行的任务(完整备份)

设置备份的信息

设置保存报告的地址

备份和恢复经验

如何加快备份速度

如果要加快备份速度，可以备份到多个备份设备，这些种类的备份可以在硬盘驱动器、网络或者是本地磁带驱动器上执行。执行备份到多个备份设备时将并行使用多个设备，数据将同时写到所有介质上。

日志备份如何不覆盖现有备份集

使用BACKUP语句执行差异备份时，要使用WITH NOINIT选项，这样将追加到现有的备份集，避免覆盖已存在的完整备份。

时间点恢复有什么弊端

时间点恢复不能用于完全与差异备份，只可用于事务日志备份，并且使用时间点恢复时，指定时间点之后整个数据库上发生的任何修改都会丢失。