完整数据库备份

项目
01/09/2024

适用范围：SQL Server

本文将介绍如何使用 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 脚本。

连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。
展开“数据库”，选择用户数据库，或展开“系统数据库”，选择系统数据库。
右键单击要备份的数据库，指向“任务”，然后选择“备份...”。
在“备份数据库”对话框中，所选的数据库显示在下拉列表中（可将其更改为服务器上的任何其他数据库）。
在“备份类型”下拉列表中，选择备份类型，默认值为“完整”。

重要

必须先至少执行一个完整数据库备份，然后才能执行差异备份或事务日志备份。
在“备份组件”下，选择“数据库”。
在“目标”部分中，查看备份文件的默认位置（位于 ../mssql/data 文件夹）。

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

若要删除备份目标，请依次选择目标和“删除”。若要查看现有备份目标的内容，请依次选择目标和“内容”。
（可选）查看“介质选项”和“备份选项”页面下的其他可用设置。

要详细了解各种备份选项，请参阅常规页、介质选项页和备份选项页。
若要启动备份，请选择“确定”。
在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

其他信息

创建完整数据库备份后，可以创建差异数据库备份或事务日志备份。
还可以选择“仅复制备份”复选框创建仅复制备份。仅复制备份是独立于独立于传统 SQL Server 备份顺序的 SQL Server 备份。有关详情，请参阅仅复制备份 (SQL Server)。仅复制备份不可用于“差异”备份类型。
若要备份到 URL，“介质选项”页上的“覆盖介质”选项被禁用。

示例

对于以下示例，使用以下 Transact-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 数据库备份到默认备份位置处的磁盘。

连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。
展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。
选择“确定”。
在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

执行 SQL 备份

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

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

连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。
展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。
在“常规”页的“目标”部分中，从“备份到:”下拉列表中选择“磁盘”。
选择“删除”，直到所有现有备份文件均已删除。
选择“添加”，这将打开“选择备份目标”对话框。
在“文件名”文本框中输入有效的路径和文件名，并使用 .bak 作为扩展名，以简化此文件的分类。
选择“确定”，然后再次选择“确定”，以开始备份。
在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

更改 DB 位置

°C 创建加密备份

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

连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。
依次展开“数据库”和“系统数据库”，右键单击“master”，然后选择“新建查询”，以打开连接到 SQLTestDB 数据库的查询窗口。

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

-- 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';

在“对象资源管理器”的“数据库”节点中，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。
在“介质选项”页的“覆盖介质”部分中，选择“备份到新介质集并清除所有现有备份集”。
在“备份选项”页的“加密”部分中，选择“加密备份”复选框。
从“算法”下拉列表中选择 AES 256。
从“证书”或“非对称密钥”下拉列表中选择 MyCertificate。
选择“确定”。

加密备份

D. 备份到 Azure Blob 存储

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

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

连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中展开服务器树。
展开“数据库”，右键单击“SQLTestDB”，指向“任务”，然后选择“备份...”。
在“目标”部分中的“常规”页上，从“备份到：”下拉列表中选择“URL”
选择“添加”，这将打开“选择备份目标”对话框。
如果之前已经注册过要与 SQL Server Management Studio 配合使用的 Azure 存储容器，请选择它。否则，选择“新建容器”来注册新的容器。
在“连接到 Microsoft 订阅”对话框中，登录你的帐户。
在“选择存储帐户”下拉文本框中，选择你的存储帐户。
在“选择 Blob 容器”下拉文本框中，选择你的 Blob 容器。
在“共享访问策略过期”下拉日历框中，为此示例中创建的共享访问策略选择到期日期。
选择“创建凭据”，以在 SQL Server Management Studio 中生成共享访问签名和凭据。
选择“确定”，以关闭“连接到 Microsoft 订阅”对话框。
在“备份文件”文本框中，修改备份文件的名称（可选）。
选择“确定”，以关闭“选择备份目标”对话框。
若要启动备份，请选择“确定”。
在备份成功完成后，选择“确定”，以关闭“SQL Server Management Studio”对话框。

注意

目前不支持使用托管标识备份到 Azure Blob 存储。

“使用 Transact-SQL”

通过执行 BACKUP DATABASE 语句创建完整数据库备份，同时指定：

要备份的数据库的名称。
写入完整数据库备份的备份设备。

用于完整数据库备份的基本 Transact-SQL 语法如下：

BACKUP DATABASE database TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

选项	说明
database	要备份的数据库。
backup_device [ ,...n ]	指定一个列表，它包含 1 至 64 个用于备份操作的备份设备。您可以指定物理备份设备，也可以指定对应的逻辑备份设备（如果已定义）。若要指定物理备份设备，请使用 DISK 或 TAPE 选项： { DISK \| TAPE } =physical_backup_device_name 有关详细信息，请参阅备份设备 (SQL Server)。
WITH with_options [ ,...o ]	用于指定一个或多个选项 (o)。有关某些基本 WITH 选项的信息，请参阅步骤 2。

（可选）指定一个或多个 WITH 选项。下面描述了几个基本 WITH 选项。有关所有 WITH 选项的详细信息，请参阅 BACKUP (Transact-SQL)。

基本备份集 WITH 选项：

{ COMPRESSION | NO_COMPRESSION }：（仅适用于 SQL Server 2008 (10.0.x) Enterprise 和更高版本）指定是否对此备份执行备份压缩，浙江覆盖服务器级的默认设置。
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY)：仅适用于 SQL Server 2014 或更高版本，指定要使用的加密算法以及要用于保护加密的证书或非对称密钥。
DESCRIPTION = { 'text' | @text_variable }：指定说明备份集的自由格式文本。该字符串最长可达 255 个字符。
NAME = { backup_set_name | @backupsetvar } 指定备份集的名称。名称最长可达 128 个字符。如果未指定 NAME，则为空白。

默认情况下，BACKUP 将备份追加到现有介质集中，并保留现有备份集。若要显式指定，请使用 NOINIT 选项。有关追加到现有备份集的信息，请参阅媒体集、媒体簇和备份集 (SQL Server)。

若要格式化备份介质，请使用 FORMAT 选项：

FORMAT [ , MEDIANAME= { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

当你第一次使用介质或要覆盖所有现有数据时，可以使用 FORMAT 子句。根据需要，可以为新介质指定介质名称和说明。

重要

当使用 BACKUP 语句的 FORMAT 子句时要十分小心，因为它会破坏以前存储在备份介质中的所有备份。

示例

对于以下示例，使用以下 Transact-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 创建新的介质集，将整个 FORMAT 数据库备份到磁盘。

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. 备份到磁带设备

下面的示例将完整的 SQLTestDB 数据库备份到磁带上，并将该备份追加到以前的备份中。

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

°C 备份到逻辑磁带设备

下例为某个磁带驱动器创建一个逻辑备份设备，然后，将完整的 SQLTestDB 数据库备份到该设备上。

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

使用 PowerShell

使用 Backup-SqlDatabase cmdlet。若要显式指明完整数据库备份，请指定 -BackupAction 参数，并保留其默认值 Database。对于完整数据库备份而言，此参数是可选的。

注意

这些示例需要安装 SqlServer 模块。要确定是否已安装该模块，请运行 Get-Module -Name SqlServer。若要安装，请在 PowerShell 的管理员会话中运行 Install-Module -Name SqlServer。

有关详细信息，请参阅 SQL Server PowerShell Provider。

重要

若要在 SQL Server Management Studio 中打开 PowerShell 窗口来连接到 SQL Server 安装，则可以省略凭据部分，因为 SSMS 中的凭据自动用于在 PowerShell 和 SQL Server 实例之间建立连接。

示例

A. 完整备份（本地）

下面的示例在服务器实例 <myDatabase> 的默认备份位置创建数据库 Computer\Instance的完整数据库备份。此示例也可以指定 -BackupAction Database。

有关完整语法示例，请参阅 Backup-SqlDatabase。

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. 完整备份到 Azure

以下示例在 <myDatabase> 实例创建的数据库 <myServer> 完整备份，以备份到 Azure Blob 存储。已经创建具有读取、写入和表权限的存储访问策略。已使用与存储访问策略相关联的共享访问签名创建 SQL Server 凭据 https://<myStorageAccount>.blob.core.windows.net/<myContainer>。 PowerShell 命令使用 BackupFile 参数指定位置 (URL) 和备份文件名。

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

通过

完整数据库备份

限制和局限

建议

安全性

权限

使用 SQL Server Management Studio

其他信息

示例

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

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

°C 创建加密备份

D. 备份到 Azure Blob 存储

“使用 Transact-SQL”

示例

A. 备份到磁盘设备

B. 备份到磁带设备

°C 备份到逻辑磁带设备

使用 PowerShell

示例

A. 完整备份（本地）

B. 完整备份到 Azure

相关任务

反馈

其他资源

通过

完整数据库备份

限制和局限

建议

安全性

权限

使用 SQL Server Management Studio

其他信息

示例

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

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

°C 创建加密备份

D. 备份到 Azure Blob 存储

“使用 Transact-SQL”

示例

A. 备份到磁盘设备

B. 备份到磁带设备

°C 备份到逻辑磁带设备

使用 PowerShell

示例

A. 完整备份（本地）

B. 完整备份到 Azure

相关任务

相关内容

反馈

其他资源