如何使用查询在 SQL Server 中备份数据库

备份 SQL Server 数据库是数据库管理员和 IT 团队最重要的工作之一。虽然 SQL Server Management Studio (SSMS) 提供了用于创建备份的图形界面，但许多专业人士更倾向于使用 T-SQL 查询，因为它们更快、更易于自动化，也更容易集成到维护脚本中。

使用 SQL 查询还让管理员能够更好地控制备份操作，使其成为计划作业、灾难恢复计划和企业数据库管理的理想选择。

在本指南中，您将学习如何使用查询命令在 SQL Server 中备份数据库，包括完整备份、差异备份、事务日志备份、备份验证和数据库恢复。无论您是初学者还是经验丰富的 DBA，本教程都将帮助您创建可靠的 SQL Server 备份 策略。

为什么要使用查询来备份 SQL Server 数据库？

在学习备份命令之前，了解为什么许多 DBA 依赖 T-SQL 而非图形工具会有所帮助。

更快的管理

使用查询可以让管理员直接执行备份操作，无需在 SSMS 中浏览多个菜单。

更易于自动化

T-SQL 备份命令可以集成到 SQL Server Agent 作业、PowerShell 脚本和企业自动化工作流中。

更好的可扩展性

使用标准化脚本时，跨多个数据库管理备份会变得显著更加容易。

运行 SQL Server 备份查询之前的前提条件

在创建备份之前，请验证您的环境满足以下要求。

验证权限

执行备份命令的账户应具有足够的权限。

运行以下查询：

SELECT IS_SRVROLEMEMBER('sysadmin'); 

如果结果为 1，则该账户具有 sysadmin 权限。

验证备份位置

确保目标目录已存在。

D:\SQLBackups\ 

此外：

• SQL Server 服务账户必须具有写入权限。

• 目标驱动器应有足够的可用空间。

• 应定期监控备份存储。

检查数据库大小

估算数据库大小有助于避免因存储空间不足导致的备份失败。

执行：

EXEC sp_spaceused;

在选择备份目标之前查看数据库大小。

如何使用查询在 SQL Server 中备份数据库

BACKUP DATABASE 语句是用于创建 SQL Server 数据库备份的主要命令。

让我们逐步完成该过程。

步骤 1. 创建完整数据库备份

完整备份包含整个数据库，包括所有表、索引、存储过程和全部数据。

要创建完整备份，请执行以下查询：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH
    FORMAT,
    INIT,
    NAME = 'SalesDB Full Backup';

此查询的作用

BACKUP DATABASE SalesDB

指定要备份的数据库。

TO DISK

TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'

定义备份的位置和文件名。

FORMAT

创建新的媒体集并删除之前的备份头。

INIT

如果备份文件已存在，则覆盖它。

NAME

为备份集添加描述性标签。

预期结果

执行后，SQL Server 应返回类似以下的消息：

BACKUP DATABASE successfully processed.

备份文件现在应存在于指定的目录中。

步骤 2. 创建压缩备份

备份压缩可减少存储需求并提高备份性能。

运行：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Compressed.bak'
WITH COMPRESSION;

压缩的优势

• 更小的备份文件

• 更低的存储成本

• 更快的网络传输

• 更轻松的备份管理

强烈建议在生产数据库中使用压缩。

步骤 3. 验证备份文件

创建备份是不够的。您应始终验证 SQL Server 能否成功读取备份。

执行：

RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak';

预期结果

如果备份有效，SQL Server 返回：

The backup set on file 1 is valid.

为什么验证很重要

许多管理员假设成功的备份作业就能保证可恢复性。然而，存储问题、损坏或权限问题仍然可能影响备份文件。

运行 RESTORE VERIFYONLY 有助于在灾难发生之前识别潜在问题。

步骤 4. 检查备份历史记录

SQL Server 将备份历史记录存储在 MSDB 数据库中。

执行以下查询：

SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_finish_date DESC;

理解查询结果

此查询对于审计备份活动和确认计划作业成功运行非常有用。

如何使用查询创建差异备份

完整备份提供全面的保护，但它们可能变得庞大且耗时。

差异备份仅捕获自最近一次完整备份以来的变更。

要创建差异备份，请运行：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Diff.bak'
WITH DIFFERENTIAL; 

差异备份的工作原理

考虑以下场景：

周三的差异备份包含自周一完整备份以来所有变更的数据。

恢复要求

要成功恢复，您需要：

1. 最近的完整备份。

2. 最近的差异备份。

这种方法在保持高效恢复的同时减小了备份大小。

如何使用查询备份 SQL Server 事务日志

对于在完整恢复模式下运行的数据库，事务日志备份是必不可少的。

它们有助于最大限度地减少数据丢失并支持 时间点恢复。

步骤 1. 检查恢复模式

运行：

SELECT
    name,
    recovery_model_desc
FROM sys.databases
WHERE name = 'SalesDB';

如果结果显示 FULL，则可以创建事务日志备份。

步骤 2. 创建事务日志备份

执行：

BACKUP LOG SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Log.trn';

事务日志备份的优势

• 捕获近期事务

• 降低恢复点目标 (RPO)

• 支持时间点恢复

• 防止事务日志过度增长

示例场景

想象一下：

• 午夜完整备份

• 每 15 分钟日志备份

• 下午 2:07 数据库故障

使用日志备份，您可以将数据库恢复到大约下午 2:06，从而最大限度地减少数据丢失。

如何从备份文件恢复 SQL Server 数据库

创建备份只是恢复过程的一半。您还应了解在需要时如何恢复数据库。

恢复完整数据库备份

要从备份文件恢复数据库，请执行：

RESTORE DATABASE SalesDB
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH REPLACE;

此查询的作用

RESTORE DATABASE

指定要恢复的数据库。

FROM DISK

定义备份文件位置。

WITH REPLACE

允许 SQL Server 覆盖现有数据库。

重要注意事项

恢复前：

• 确保没有活动连接。

• 验证备份文件有效。

• 确认备份是正确的版本。

• 了解当前数据库将被覆盖。

验证已恢复的数据库

恢复后，运行：

DBCC CHECKDB ('SalesDB');

此命令检查数据库一致性并验证恢复操作是否成功完成。

如何使用 SQL Server Agent 自动化 SQL Server 备份

手动运行备份查询适用于测试和学习目的，但生产环境需要自动化来确保备份按计划一致地执行。

SQL Server Agent 允许您自动化备份作业，无需人工干预。

步骤 1. 创建新的 SQL Server Agent 作业

在 SQL Server Management Studio (SSMS) 中，导航到：

对象资源管理器
→ SQL Server Agent
→ 作业
→ 新建作业

输入有意义的作业名称，例如：

每日完整数据库备份

使用描述性的作业名称有助于未来的维护和故障排查。

步骤 2. 添加备份步骤

在新作业中，选择 步骤 并创建新步骤。

选择：

类型：Transact-SQL 脚本 (T-SQL)
数据库：master

然后输入您的备份查询：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH
    COMPRESSION,
    INIT;

保存该步骤。

步骤 3. 配置计划

选择 计划 并创建新计划。

常见示例包括：

例如：

每天晚上 11:00

或

每周日凌晨 1:00

最佳计划取决于业务需求和可接受的数据丢失窗口。

步骤 4. 测试作业

在依赖计划之前，先手动运行该作业。

验证：

• 备份文件创建

• 作业成功完成

• 无权限错误

• 充足的存储空间

自动化降低了遗漏备份的风险并确保持续的保护。

常见 SQL Server 备份查询错误及解决方案

即使简单的备份操作也可能因环境配置不当而失败。

以下是最常见的备份相关错误及其解决方案。

操作系统错误 5（访问被拒绝）

典型错误消息

Operating system error 5 (Access is denied).

原因

SQL Server 服务账户对目标目录缺乏写入权限。

解决方案

验证 SQL Server 服务账户：

SELECT servicename, service_account
FROM sys.dm_server_services;

授予对备份目录的写入权限并重试备份。

无法打开备份设备

典型错误消息

Cannot open backup device.
Operating system error 3.

原因

指定的文件夹不存在或文件路径不正确。

例如：

D:\SQLBackups\

可能在服务器上不存在。

解决方案

验证：

• 目录存在

• 盘符正确

• SQL Server 可以访问该位置

磁盘空间不足

典型错误消息

There is insufficient free space on disk volume.

原因

备份目标没有足够的可用存储空间。

解决方案

考虑：

• 删除过时的备份文件

• 扩展存储容量

• 使用备份压缩

• 实施备份保留策略

事务日志备份失败

典型错误消息

BACKUP LOG cannot be performed because there is no current database backup.

原因

事务日志备份需要现有的完整备份。

解决方案

首先创建完整备份：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak';

然后再次运行日志备份。

SQL Server 备份查询最佳实践

创建备份很重要，但遵循最佳实践才能确保在发生故障时成功恢复。

将备份存储在不同的存储上

切勿将备份存储在与生产数据库相同的磁盘上。

如果存储设备故障，数据库和备份文件都可能丢失。

更好的方法是：

生产数据库
    ↓
专用备份存储
    ↓
异地或云存储

这与广泛采用的 3-2-1 备份策略一致。

定期测试恢复流程

许多组织验证备份但从不测试实际恢复。

定期：

1. 将备份恢复到测试服务器。

2. 验证应用程序功能。

3. 确认数据完整性。

无法恢复的备份不提供任何保护。

组合使用完整备份、差异备份和日志备份

仅依赖完整备份通常会增加备份窗口和存储需求。

常见策略是：

这种方法在恢复速度和存储效率之间取得平衡。

监控备份作业

失败的备份作业绝不应被忽视。

监控：

• 作业失败

• 备份持续时间

• 存储消耗

• 备份完成状态

早期检测可防止在故障期间出现恢复意外。

加密敏感备份

如果备份包含客户信息、财务记录或受监管数据，应启用加密。

示例：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Encrypted.bak'
WITH ENCRYPTION
(
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = BackupCertificate
);

加密有助于保护备份文件免受未经授权的访问。

手动 SQL 备份查询的局限性

T-SQL 为数据库备份提供了出色的灵活性。然而，随着环境的增长，手动管理备份变得越来越困难。

常见挑战包括：

多个 SQL Server 实例

组织通常跨不同服务器管理数十甚至数百个数据库。

为每个实例维护备份脚本可能很快变得复杂。

有限的集中可见性

T-SQL 脚本不提供用于监控跨环境备份状态的统一仪表板。

管理员可能需要手动查看作业历史和日志。

恢复复杂性

恢复大型环境通常需要：

• 识别正确的备份链

• 恢复多个文件

• 验证恢复一致性

这在关键故障期间可能非常耗时。

人为错误风险增加

手动维护会引入风险，例如：

• 错误的文件路径

• 遗漏备份作业

• 错误配置的保留设置

• 备份验证失败

随着环境规模的扩大，这些问题变得更加常见。

使用 i2Backup 简化 SQL Server 备份与恢复

对于管理业务关键数据库的组织来说，备份成功只是挑战的一部分。集中管理、监控、合规性和恢复速度同样重要。

这正是 i2Backup 可以补充传统 SQL Server 备份方法的地方。

什么是 i2Backup？

i2Backup 是一款企业级备份与恢复解决方案，旨在通过单一管理平台保护数据库、物理服务器、虚拟机、应用和云工作负载。

管理员无需完全依赖手动维护的备份脚本，而是可以通过集中界面管理备份操作。

为什么组织选择 i2Backup 来保护 SQL Server

自动化备份调度

备份任务可以集中调度和管理，减少管理开销。

集中管理

管理员可以跨多个 SQL Server 实例获得可见性，无需为每个环境维护单独的脚本。

增量备份能力

通过仅备份已变更的数据，组织可以减少存储消耗并提高备份效率。

更快的恢复操作

简化的恢复工作流有助于在故障和数据丢失事件期间减少停机时间。

统一数据保护

除了 SQL Server，组织还可以从同一平台保护虚拟机、文件系统和其他关键工作负载。

何时选择 i2Backup 而非手动查询

以下对比突显了专用备份平台可能在哪些场景下提供额外价值。

对于许多组织来说，T-SQL 仍然是创建备份的有用工具，而集中平台则有助于在大规模环境中简化管理和恢复。

关于如何使用查询在 SQL Server 中备份数据库的常见问题

如何使用查询备份 SQL Server 数据库？

使用 BACKUP DATABASE 命令：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak';

这将创建指定数据库的完整备份。

完整数据库备份的 SQL 查询是什么？

常见示例如下：

BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH COMPRESSION;

这将创建压缩的完整备份文件。

如何从备份文件恢复 SQL Server 数据库？

执行：

RESTORE DATABASE SalesDB
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH REPLACE;

这从指定的备份文件恢复数据库。

我可以自动化 SQL Server 备份查询吗？

可以。SQL Server Agent 允许管理员调度在预定义时间间隔自动运行的备份作业。

完整备份和差异备份有什么区别？

完整备份包含整个数据库。

差异备份仅包含自最近一次完整备份以来的变更。

如何验证 SQL Server 备份文件？

使用：

RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak';

这在不恢复数据库的情况下验证备份文件。

SQL 查询比 SSMS 更适合备份吗？

两种方法创建相同的备份文件。然而，T-SQL 查询通常在自动化、脚本编写和大规模数据库管理方面更受青睐。

结论

学习如何使用查询命令在 SQL Server 中备份数据库是数据库管理员和 IT 专业人员的一项基本技能。通过掌握 T-SQL 备份操作，您可以创建完整备份、差异备份、事务日志备份、验证备份完整性、自动化备份计划，并在需要恢复时恢复数据库。

然而，创建备份只是完整数据保护策略的一部分。组织还必须监控备份成功、验证可恢复性、管理保留策略，并在故障期间缩短恢复时间。

对于较小的环境，原生的 SQL Server 备份查询可能就足够了。对于更大或更复杂的基础设施，像 i2Backup 这样的解决方案可以帮助简化备份管理、提高运维效率并加强整体数据保护。