完整数据库备份

本主题将介绍如何使用 SQL Server Management Studio、Transact-SQL 或 PowerShell 在 SQL Server 中创建完整数据库备份。

有关详细信息，请参阅使用 Azure Blob 存储执行 SQL Server 备份和还原和到 URL 的 SQL Server 备份。

限制和局限

• 不允许在显式事务或隐式事务中使用 BACKUP 语句。
• 无法在早期版本的 SQL Server 中还原较新版本的 SQL Server 创建的备份。

有关备份概念和任务的概述和详细信息，请在继续操作前先参阅备份概述 (SQL Server)。

建议

• 随着数据库不断增大，完整数据库备份的完成时间会延长，并且需要占用更多存储空间。 对于大型数据库，请考虑用一系列差异数据库备份来补充完整数据库备份。
• 使用 sp_spaceused 系统存储过程估计完整数据库备份的大小。
• 默认情况下，每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果频繁备份，成功消息就会迅速累积，导致大量错误日志，进而加大查找其他消息的难度。 在这些情况下，如果脚本均不依赖于这些备份日志条目，则可使用跟踪标志 3226 取消这些条目。 有关详细信息，请参阅跟踪标志 (Transact-SQL)。

安全性

针对数据库备份，TRUSTWORTHY 设置为 OFF。 有关如何将 TRUSTWORTHY 设置为 ON 的信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

自 SQL Server 2012 (11.x) 起，PASSWORD 和 MEDIAPASSWORD 选项不再用于创建备份。 不过，您仍可以还原使用密码创建的备份。

权限

默认情况下，为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限 。

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。 SQL Server 服务应对设备进行读写。 运行 SQL Server 服务所用的帐户必须拥有对备份设备的写入权限。 但是，用于在系统表中添加备份设备条目的 sp_addumpdevice 并不检查文件访问权限。 除非使用备份或尝试还原，否则备份设备的物理文件可能不会出现问题。

使用 SQL Server Management Studio

 备注

使用 SQL Server Management Studio 指定备份任务时，可以通过单击“脚本”按钮，再为选择脚本目标来生成相应的 Transact-SQL BACKUP 脚本。

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。

2. 展开“数据库”，选择用户数据库，或展开“系统数据库”，选择系统数据库。

3. 右键单击要备份的数据库，指向“任务”，然后选择“备份...”。

4. 在“备份数据库”对话框中，所选的数据库显示在下拉列表中（可将其更改为服务器上的任何其他数据库）。

5. 在“备份类型”下拉列表中，选择备份类型，默认值为“完整”。

    重要

   必须先至少执行一个完整数据库备份，然后才能执行差异备份或事务日志备份。

6. 在“备份组件”下，选择“数据库”。

7. 在“目标”部分中，查看备份文件的默认位置（位于 ../mssql/data 文件夹）。

   可以使用“备份到”下拉列表来选择其他设备。 选择“添加”来添加备份对象和/或目标。 可以跨多个文件对备份集进行分条，以提高备份速度。

   若要删除备份目标，请依次选择目标和“删除”。 若要查看现有备份目标的内容，请依次选择目标和“内容”。

8. （可选）查看“介质选项”和“备份选项”页面下的其他可用设置。

   要详细了解各种备份选项，请参阅常规页、介质选项页和备份选项页。

9. 若要启动备份，请选择“确定”。

10. 在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

其他信息

• 创建完整数据库备份后，可以创建差异数据库备份或事务日志备份。

• 还可以选择“仅复制备份”复选框创建仅复制备份。 仅复制备份是独立于独立于传统 SQL Server 备份顺序的 SQL Server 备份。 有关详情，请参阅仅复制备份 (SQL Server)。 仅复制备份不可用于“差异”备份类型。

• 若要备份到 URL，“介质选项”页上的“覆盖介质”选项被禁用。

示例

对于以下示例，使用以下 Transact-SQL 代码创建测试数据库：

SQL复制

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. 完整备份到默认位置的磁盘

在此示例中，将 SQLTestDB 数据库备份到默认备份位置处的磁盘。

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。

2. 展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。

3. 选择“确定”。

4. 在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

B. 完整备份到非默认位置的磁盘

在此示例中，将 SQLTestDB 数据库备份到所选位置处的磁盘。

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。

2. 展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。

3. 在“常规”页的“目标”部分中，从“备份到:”下拉列表中选择“磁盘”。

4. 选择“删除”，直到所有现有备份文件均已删除。

5. 选择“添加”，这将打开“选择备份目标”对话框。

6. 在“文件名”文本框中输入有效的路径和文件名，并使用 .bak 作为扩展名，以简化此文件的分类。

7. 选择“确定”，然后再次选择“确定”，以开始备份。

8. 在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

°C 创建加密备份

在此示例中，将已加密的 SQLTestDB 数据库备份到默认备份位置。

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。

2. 依次展开“数据库”和“系统数据库”，右键单击“master”，然后选择“新建查询”，以打开连接到 SQLTestDB 数据库的查询窗口。

3. 执行以下命令，在 master 数据库中创建数据库主密钥和证书。

   SQL复制

   -- Create the master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
   
   -- If the master key already exists, open it in the same session that you create the certificate (see next step)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
   
   -- Create the certificate encrypted by the master key
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';  
   

4. 在“对象资源管理器”的“数据库”节点中，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。

5. 在“介质选项”页的“覆盖介质”部分中，选择“备份到新介质集并清除所有现有备份集”。

6. 在“备份选项”页的“加密”部分中，选择“加密备份”复选框。

7. 从“算法”下拉列表中选择 AES 256。

8. 从“证书”或“非对称密钥”下拉列表中选择 MyCertificate。

9. 选择“确定”。

D. 备份到 Azure Blob 存储

此示例创建 SQLTestDB 的完整数据库备份，以备份到 Azure Blob 存储。 此示例假设你已经拥有带有 blob 容器的存储帐户。 此示例为你创建共享访问签名；如果容器现有共享访问签名，则此示例失败。

如果存储帐户中没有 Azure Blob 存储容器，请在继续操作之前先创建一个。 请参阅创建常规用途存储帐户和创建容器。

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。

2. 展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。

3. 在“目标”部分中的“常规”页上，从“备份到：”下拉列表中选择“URL”

4. 选择“添加”，这将打开“选择备份目标”对话框。

5. 如果之前已经注册过要与 SQL Server Management Studio 配合使用的 Azure 存储容器，请选择它。 否则，选择“新建容器”来注册新的容器。

6. 在“连接到 Microsoft 订阅”对话框中，登录你的帐户。

7. 在“选择存储帐户”下拉文本框中，选择你的存储帐户。

8. 在“选择 Blob 容器”下拉文本框中，选择你的 Blob 容器。

9. 在“共享访问策略过期”下拉日历框中，为此示例中创建的共享访问策略选择到期日期。

10. 选择“创建凭据”，以在 SQL Server Management Studio 中生成共享访问签名和凭据。

11. 选择“确定”，以关闭“连接到 Microsoft 订阅”对话框。

12. 在“备份文件”文本框中，修改备份文件的名称（可选）。

13. 选择“确定”，以关闭“选择备份目标”对话框。

14. 若要启动备份，请选择“确定”。

15. 在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。