SQL如何备份数据库_SQL数据库备份的完整方法-SQL-PHP中文网

数据库备份是通过完整、差异和事务日志备份组合策略保障数据安全，利用SQL Server Agent或维护计划实现自动化，并通过RESTORE VERIFYONLY和定期测试恢复验证备份有效性，确保灾难发生时能准确恢复到指定时间点。

sql如何备份数据库_sql数据库备份的完整方法

数据库备份，说白了，就是给你的数据资产买一份保险。它不是什么玄学，核心思路就是利用数据库系统自带的命令或工具，把当前数据库的数据文件、日志文件等关键信息复制一份，存放到一个安全的地方。这样，万一哪天系统崩溃、数据损坏，或者不小心删错了东西，你就能拿着这份“保险”把数据恢复到某个时间点，避免巨大的损失。这事儿听起来简单，但执行起来，需要一点策略和细心。

解决方案

在SQL Server环境中，备份数据库主要依赖BACKUP DATABASE这个T-SQL命令。它提供了几种不同的备份类型，以应对不同的恢复需求和存储效率考量。

1. 完整备份（Full Backup） 这是最基础、最全面的备份。它会备份整个数据库，包括所有数据、索引、对象以及在备份操作开始时，用于确保数据库一致性的事务日志部分。 示例：

BACKUP DATABASE YourDatabaseName
TO DISK = 'D:\SQLBackup\YourDatabaseName_Full_20231027.bak'
WITH NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

登录后复制

这里的TO DISK指定了备份文件的存储路径和名称。WITH NOINIT表示将备份追加到现有备份文件（如果文件存在），而不是覆盖它。NOREWIND, NOUNLOAD是磁带备份的选项，但在磁盘备份中也常用。STATS = 10会每完成10%的备份进度就显示一次信息。

2. 差异备份（Differential Backup） 差异备份只备份自上次完整备份以来发生变化的数据。它的文件通常比完整备份小，备份速度也更快。恢复时，你需要先恢复最近的完整备份，再恢复最近的差异备份。 示例：

BACKUP DATABASE YourDatabaseName
TO DISK = 'D:\SQLBackup\YourDatabaseName_Diff_20231027.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

登录后复制

注意WITH DIFFERENTIAL这个关键词。

3. 事务日志备份（Transaction Log Backup） 对于处于完整恢复模式或大容量日志恢复模式的数据库，事务日志备份至关重要。它备份自上次日志备份以来发生的所有事务。这使得你可以将数据库恢复到任意一个时间点（Point-in-Time Recovery），而不是仅仅是某个完整备份或差异备份的时间点。 示例：

BACKUP LOG YourDatabaseName
TO DISK = 'D:\SQLBackup\YourDatabaseName_Log_20231027_1000.trn'
WITH NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

登录后复制

这里的BACKUP LOG是关键。

通常，我们会采取“完整备份 + 差异备份 + 事务日志备份”的组合策略。比如，每周一次完整备份，每天一次差异备份，每隔几小时（甚至几分钟）一次事务日志备份。这能最大限度地减少数据丢失，并缩短恢复时间。

自动化备份：如何让你的数据库备份不再是“看心情”？

说实话，手动备份这事儿，第一次做可能还挺认真，时间一长，谁都可能犯懒或者忘记。我个人觉得，任何重要的重复性任务，如果不能自动化，那它的可靠性就会大打折扣。SQL Server提供了非常成熟的自动化备份机制，主要通过SQL Server Agent来实现。

利用SQL Server Agent作业： 这是最直接、最灵活的方式。你可以创建一个SQL Server Agent作业，然后在作业中添加一个或多个T-SQL步骤来执行上面提到的BACKUP DATABASE或BACKUP LOG命令。

打开SQL Server Management Studio (SSMS)，连接到你的数据库实例。
在“对象资源管理器”中展开“SQL Server Agent” -> “作业”。
右键点击“作业”，选择“新建作业”。
常规：给作业起个有意义的名字，比如“每日完整备份_YourDatabaseName”。
步骤：
- 点击“新建”，添加一个步骤。
- 步骤名称：比如“执行完整备份”。
- 类型：选择“Transact-SQL脚本(TSQL)”。
- 数据库：选择你要备份的数据库。
- 在“命令”框中粘贴你的BACKUP DATABASE T-SQL脚本。
计划：
- 点击“新建”，设置作业的执行频率。你可以选择每日、每周、每月，或者在特定时间点执行。比如，每周日凌晨2点执行完整备份。
- 对于事务日志备份，可能需要设置更频繁的计划，比如每小时一次。
警报和通知：设置当作业成功或失败时发送邮件通知，这样你就能及时知道备份情况。

使用维护计划（Maintenance Plans）： 对于不熟悉T-SQL的用户，或者需要管理多个数据库备份策略的场景，维护计划是一个更友好的图形化工具。它能让你通过拖拽的方式创建备份任务、数据库完整性检查、索引维护等。

在SSMS中，展开“管理” -> “维护计划”。
右键点击“维护计划”，选择“维护计划向导”或“新建维护计划”。
向导会引导你选择要执行的任务（如“备份数据库(完整)”、“备份数据库(差异)”、“备份数据库(事务日志)”），选择数据库，指定备份文件的存储位置，以及设置计划。

无论是哪种方式，关键在于设置好之后，定期检查作业历史记录和备份文件是否存在、大小是否正常。自动化不等于“一劳永逸”，它只是帮你省去了重复劳动，但监控和验证的责任还在你。

备份的“保质期”：如何验证你的备份是可用的？

我见过太多人，辛辛苦苦地设置了备份，结果真到需要恢复的时候，才发现备份文件损坏了、不完整，或者根本无法恢复。这比没有备份更让人绝望，因为它给了你一个虚假的安全感。所以，验证备份的可用性，在我看来，和备份本身一样重要，甚至更重要。

1. RESTORE VERIFYONLY 命令： 这是SQL Server提供的一个轻量级验证工具。它会读取备份文件，检查其头部信息是否正确，以及整个备份集是否在结构上完整且可读。但请注意，它不会实际恢复数据，所以它无法验证数据本身的逻辑一致性。 示例：

RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Full_20231027.bak';

登录后复制

如果命令执行成功，你会收到类似“备份集在结构上有效”的消息。这至少能确保你的备份文件不是一个空壳或损坏的文件。

库宝AI

库宝AI是一款功能多样的智能伙伴助手，涵盖AI写作辅助、智能设计、图像生成、智能对话等多个方面。

109

查看详情

2. 定期进行实际的测试恢复： 这是最可靠、最彻底的验证方法。找一台测试服务器，或者在虚拟机上搭建一个临时的SQL Server实例，定期地用你的备份文件进行一次完整的数据库恢复操作。

恢复步骤：
1. 如果你有完整备份、差异备份和多个日志备份，你需要按照正确的顺序进行恢复：
  - RESTORE DATABASE YourTestDatabase FROM DISK = '完整备份文件路径' WITH NORECOVERY;
  - RESTORE DATABASE YourTestDatabase FROM DISK = '差异备份文件路径' WITH NORECOVERY; (如果有)
  - RESTORE LOG YourTestDatabase FROM DISK = '日志备份文件路径1' WITH NORECOVERY;
  - RESTORE LOG YourTestDatabase FROM DISK = '日志备份文件路径2' WITH NORECOVERY;
  - ...
  - RESTORE DATABASE YourTestDatabase WITH RECOVERY; (或者在最后一个日志备份后直接用WITH RECOVERY)
2. 恢复完成后，连接到这个测试数据库，运行一些查询，检查关键表的数据是否完整、是否符合预期。可以执行一些SELECT COUNT(*)或SELECT TOP 10 *来快速验证。

我个人的经验是： 至少每季度进行一次完整的测试恢复，尤其是在核心业务数据库上。如果你的备份策略或存储位置有任何重大调整，更应该立即进行测试。这个过程可能会有点折腾，但它能让你在真正需要恢复时，心里有底。别等到火烧眉毛了，才发现备份是个“摆设”。

当灾难来临：从备份中恢复数据库的实战步骤

当数据库真的出问题了，比如硬盘损坏、误操作删除了关键数据、或者系统崩溃，这时候就是备份发挥作用的时候了。恢复数据库是一个需要冷静和精确操作的过程，每一步都不能错。

1. 评估损失和确定恢复点（RPO）： 首先，你需要知道数据丢失到了什么程度，以及你希望将数据库恢复到哪个时间点。这是恢复策略的基础。

如果只是想恢复到上一个完整备份，那相对简单。
如果需要恢复到某个精确的时间点（比如误删操作发生前一秒），那就需要用到完整备份、差异备份（如果适用）以及一系列的事务日志备份。

2. 停止对数据库的所有访问： 在开始恢复之前，确保没有用户或应用程序正在连接到要恢复的数据库。这通常意味着你需要将数据库设置为单用户模式，或者直接停止相关的应用程序服务。

3. 执行恢复操作： 恢复命令是RESTORE DATABASE和RESTORE LOG。关键在于WITH NORECOVERY和WITH RECOVERY的使用。

恢复完整备份：
```
RESTORE DATABASE YourDatabaseName
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Full_20231027.bak'
WITH NORECOVERY,
MOVE 'YourDatabaseName_Data' TO 'E:\SQLData\YourDatabaseName_Data.mdf',
MOVE 'YourDatabaseName_Log' TO 'F:\SQLLog\YourDatabaseName_Log.ldf';
```
登录后复制
WITH NORECOVERY表示数据库在恢复完成后仍处于“正在恢复”状态，不能被访问，以便后续可以应用差异备份或日志备份。MOVE选项非常重要，它允许你指定数据文件和日志文件的新位置，这在你恢复到一台新服务器或者原文件路径不可用时非常有用。

应用差异备份（如果适用）：

RESTORE DATABASE YourDatabaseName
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Diff_20231027.bak'
WITH NORECOVERY;

登录后复制

同样使用NORECOVERY。

应用事务日志备份： 按照事务日志备份的时间顺序，逐一应用。

RESTORE LOG YourDatabaseName
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Log_20231027_1000.trn'
WITH NORECOVERY;

RESTORE LOG YourDatabaseName
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Log_20231027_1100.trn'
WITH NORECOVERY;

-- 如果需要恢复到特定时间点，使用 STOPAT
RESTORE LOG YourDatabaseName
FROM DISK = 'D:\SQLBackup\YourDatabaseName_Log_20231027_1200.trn'
WITH STOPAT = '2023-10-27 11:59:59.000', NORECOVERY;

登录后复制

STOPAT允许你指定一个精确的恢复时间点。

完成恢复： 在应用完所有需要的备份后，使用WITH RECOVERY使数据库上线并可用。
```
RESTORE DATABASE YourDatabaseName WITH RECOVERY;
```
登录后复制
如果你在最后一个日志备份命令中已经使用了WITH RECOVERY，则不需要单独执行此步骤。