前阵子做电商的朋友半夜找我说 SQL Server 数据库被人误删了几张表翻遍服务器只找到一个三个月前的 .bak 文件。我陪他折腾到凌晨最后只能告诉他备份不等于安全能快速还原的备份才叫安全。今天这篇就围绕 SQL Server 数据库的备份和还原把从零开始的保姆级流程完整拆给你。不管你是刚接触数据库的实习生、需要自己维护服务器的创业团队还是偶尔客串 DBA 的后端开发照着做基本都能跑通。文章会依次讲清楚三件事先搞明白恢复模式因为它是整个备份方案的地基接着把完整备份、差异备份、日志备份怎么做讲明白最后给出还原操作、常见排错和自动化落地的完整思路。1. 恢复模式这件事决定你的备份方案能走多远很多人一上来就右键数据库点备份压根没看过数据库属性里的恢复模式。这个字段如果选错了后期想做时间点还原会发现根本没有日志备份可以做等于给自己挖了个大坑。1.1 简单恢复、完整恢复、大容量日志恢复的区别SQL Server 的恢复模式一共三种我直接用大白话解释恢复模式是否支持日志备份是否支持时间点还原日志文件情况简单恢复否否会自动截断基本不膨胀完整恢复是是不做日志备份会持续膨胀大容量日志是但有限制不推荐依赖批量操作时写入更少日志简单恢复模式是最省心的数据库自动把不用的日志空间释放掉所以你几乎不需要管日志文件。代价就是没有事务日志备份一旦完整备份之后发生问题只能恢复到最近一次完整备份或差异备份的时间点中间产生的数据大概率会丢。开发环境、测试环境、报表库这种能容忍一定数据丢失的场景选简单模式完全没问题。完整恢复模式是生产环境的首选。它会把每一条事务都记到日志里配合事务日志备份就能把数据库还原到某一个具体时间点比如今天下午 14:30 误删数据之前。代价是日志文件需要你定期备份来截断否则日志会一直涨涨到磁盘满了整个数据库都起不来。这个坑我在后面日志备份部分会详细讲。大容量日志恢复模式比较特殊它主要为了大批量导入导出、重建索引这种操作用的。批量操作时只记少量日志所以性能比完整模式快同时还算保留日志链。但它有个问题一旦发生了大容量操作日志备份里可能没有足够细节来精确定位到某个时刻尽量只在批量操作期间临时切换操作完马上切回完整恢复模式。1.2 恢复模式与备份策略的对应关系选好恢复模式后备份策略才有意义。我常用的一套配置是这样生产库完整恢复模式。完整备份每天一次放凌晨业务低峰。差异备份每 4 到 6 小时一次减少还原时要重放日志的数据量。事务日志备份每 15 到 30 分钟一次把数据丢失窗口压缩到 15 到 30 分钟。如果业务要求更高比如金融交易系统日志备份可以缩短到每 5 分钟甚至更短。这里的核心指标是 RPO也就是最多允许丢多少数据。日志备份间隔越长RPO 越差间隔太短又会给磁盘和 IO 带来压力需要根据业务量权衡。1.3 一个备份成功但还原失败的真实场景我见过最典型的情况是这样的某公司每天凌晨都用维护计划做完整备份任务历史里全部显示成功运维以为万事大吉。直到有一天磁盘坏了拿去还原管理员账号然后所有虚拟主机都变得不复存在了。还原到一个新的测试服务器时SQL Server 直接报备份集是旧的无法还原或者设备上不存在备份集。查了一整晚最后发现维护计划写的是备份到网络共享盘那个共享盘早就满了SQL Server 没有报告失败而是把备份文件覆盖写成了一个损坏的文件但任务状态还是显示成功。这里要记住一条铁律备份任务显示成功不代表备份文件能成功还原。所以后面我会反复强调校验和还原演练的重要性。2. 完整备份实操SSMS 点击流和 T-SQL 脚本两条路完整备份是所有备份的基石。差异备份和日志备份都依赖它所以在它身上花点时间怎么都不亏。2.1 用 SSMS 完成第一次完整备份如果你刚接触 SQL Server最直接的方式是用 SQL Server Management StudioSSMS图形界面操作打开 SSMS连接到目标实例展开数据库。右键你要备份的数据库选择任务 - 备份。在备份类型里选择完整备份组件保持数据库。目标选择磁盘点击添加输入备份文件的完整路径比如D:\SQLBackup\MyERP_FULL_20240815.bak。如果路径不存在或权限不足点击添加时会直接报错所以建议提前建好目录。切到左侧的选项页这里有几个关键选项覆盖媒体如果想覆盖同一个文件就选备份到现有媒体集并勾选覆盖所有现有备份集。如果想生成新文件直接用新文件名就行。可靠性推荐勾选写入介质前检查校验和这一步会对备份页做校验能在写入阶段发现某些损坏。压缩如果版本支持压缩勾选压缩备份文件体积能明显变小恢复时也更快。压缩会额外消耗 CPU但对大多数服务器来说完全可控。点击确定进度条跑完看到备份已成功完成一个完整的 .bak 文件就出来了。2.2 用 T-SQL 执行完整备份SSMS 适合手动操作但日常运维我更推荐直接写 T-SQL因为它可复制、可保存、可放进作业里自动化还能在脚本里带上校验参数。一个最常用的完整备份命令长这样BACKUP DATABASE [MyERP] TO DISK ND:\SQLBackup\MyERP_FULL_20240815.bak WITH INIT, COMPRESSION, CHECKSUM, STATS 10;逐个解释关键参数[MyERP]要备份的数据库名。如果库名里带空格或特殊符号方括号是必须的。TO DISK备份到本地磁盘路径。也可以直接写网络共享路径比如\\192.168.1.10\Backup\MyERP.bak但网络盘不稳定会直接导致备份失败建议先在本地落盘再同步走。INIT覆盖同名文件。如果不写默认是追加备份集文件里会累积多个备份还原时要通过FILE 1、FILE 2去指定第几个备份集容易搞混。日常轮换场景建议用 INIT。COMPRESSION开启备份压缩文件更小、写入更快。CHECKSUM启用校验和给每个备份页生成一个校验值。还原时会自动校验能发现很多潜在损坏。STATS 10每完成 10% 输出一次进度长时间备份时方便观察状态。另外还有一个常用参数FORMAT它会把目标文件中已有的旧备份集全部清掉重新初始化一个新的媒体集比INIT更彻底。迁移环境或者更换备份介质时可以考虑。2.3 备份文件命名规范与介质选择命名看起来是小事但灾后恢复时不规范的命名会让你在几十个文件里翻半天。我个人的规范是数据库名_备份类型_日期_时间.扩展名例如MyERP_FULL_20240815_0600.bak MyERP_DIFF_20240815_1200.bak MyERP_LOG_20240815_1230.trn文件扩展名也做区分完整备份和差异备份统一用.bak事务日志备份用.trn。这样只看文件名就能快速判断这个文件扮演什么角色。介质选择上普通业务用本地磁盘就够了但至少别跟数据文件放在同一块物理盘否则物理盘坏了数据和备份一起没。稍好一点的做法是把备份放到另一台服务器或 NAS 共享目录更稳妥的方案是备份到云存储对象SQL Server 2008 R2 之后的版本可以直接备份到 Azure Blob也可以让备份文件落盘后由同步工具拷贝到异地。异地备份这件事我后面会单独说。3. 差异备份和日志备份把还原窗口从某天压缩到某分钟只有完整备份的话你最多恢复到昨天凌晨那个点今天一天的数据都没了。要想不让数据丢得太惨必须上差异备份和日志备份。3.1 差异备份减少还原时需要重放的日志量差异备份记录的是最近一次完整备份之后所有发生变化的数据。它比完整备份文件小、耗时短但核心作用是缩短还原时间。这么说吧假设你有 30 天完整备份 每天 1440 个日志备份。如果没有差异备份还原时要把从昨天凌晨到现在的所有日志一个接一个重放有了每 4 小时一次的差异备份还原时只需要完整备份 最近一次差异备份 之后几十个日志备份。日志重放是还原过程里最耗时的部分差异备份能帮你跳过大量中间日志。差异备份的 T-SQL 很简单BACKUP DATABASE [MyERP] TO DISK ND:\SQLBackup\MyERP_DIFF_20240815_1200.bak WITH DIFFERENTIAL, INIT, COMPRESSION, CHECKSUM, STATS 10;注意唯一的关键就是带上了DIFFERENTIAL。还原差异备份时默认会先寻找最近一次完整备份作为基线所以你的完整备份不能丢。我的建议是完整备份每天一次的前提下差异备份设成每 4 到 6 小时一次。如果业务库非常大完整备份十几个小时都做不完那就拉长完整备份周期把差异备份加密但要保证差异备份始终在上一轮完整备份的覆盖范围之内。3.2 事务日志备份恢复模式之外最容易踩的坑事务日志备份是时间点还原的关键。T-SQL 命令BACKUP LOG [MyERP] TO DISK ND:\SQLBackup\MyERP_LOG_20240815_1230.trn WITH INIT, COMPRESSION, CHECKSUM, STATS 10;但是这条命令只有在数据库处于完整恢复模式或大容量日志恢复模式下才能执行。如果数据库是简单恢复模式SSMS 里事务日志备份入口是灰的T-SQL 也会直接报错。遇到这种情况先改恢复模式ALTER DATABASE [MyERP] SET RECOVERY FULL;改完之后建议立刻做一次完整备份因为日志备份的基线是从某个完整备份开始的。接着说我见过最多的坑。很多新手把数据库切成完整恢复模式后发现日志文件一天比一天大每天几 GB 涨上去吓得到处查日志为什么这么大。原因很简单完整恢复模式下日志不会自动截断只有日志备份才会把已提交事务的日志空间标记为可重用。你没有做日志备份日志文件当然只增不减。正确做法就是按固定频率做日志备份比如 15 到 30 分钟一次。做完日志备份后日志文件体积并不会立刻变小但里面的空间已经被释放后续新事务可以复用这些空间。如果某个库的日志文件已经膨胀到了几百 GB临时手段是在日志备份之后执行DBCC SHRINKFILE把它压缩回合理大小但这只是治标。治本是保证日志备份节奏稳定否则下次还会涨回来。3.3 三种备份的还原位置如何配合还原的时候顺序是铁律还原最近一次完整备份。还原在这份完整备份之后、最近的一次差异备份可选。按时间顺序还原差异备份之后产生的所有事务日志备份直到目标时间点。最后一步执行RECOVERY让数据库恢复到可用状态。如果只有完整备份和日志备份没有差异备份也没关系那就是从完整备份开始把之后的所有日志按顺序全重放一遍只是耗时更长。还有一个容易犯迷糊的地方NORECOVERY和RECOVERY的区别。还原中间步骤必须用NORECOVERY它告诉 SQL Server数据库还没恢复完请继续下一个备份集。只有最后一步才能用RECOVERY把数据库从正在还原状态变为可访问状态。如果中途不小心用了RECOVERYSQL Server 会认为还原序列已经结束后面再想追加还原日志就会报错只能重新从完整备份再来一遍。4. 还原数据库实操从完整恢复一步步走到时间点恢复备份做得再漂亮还原不会操作也白搭。这一节从最简单的完整还原开始逐步到时间点还原。4.1 完整还原的基本操作SSMS T-SQLSSMS 操作方式右键数据库 - 还原数据库 - 源选择设备 - 浏览找到备份文件 - 目标数据库填库名 - 选项页勾选覆盖现有数据库 - 确认。这里有个经典报错数据库正在使用因此无法获得对数据库的独占访问权。原因是目标库还有别的会话连着。SSMS 里可以在选项页勾选关闭到目标数据库的现有连接或者直接用 T-SQL 把库切到单用户模式强制踢掉连接ALTER DATABASE [MyERP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO RESTORE DATABASE [MyERP] FROM DISK ND:\SQLBackup\MyERP_FULL_20240815.bak WITH REPLACE, RECOVERY; GO ALTER DATABASE [MyERP] SET MULTI_USER;WITH ROLLBACK IMMEDIATE会立刻回滚所有未完成事务并断开连接适合争分夺秒的还原场景。REPLACE的作用是允许覆盖一个同名数据库即使备份文件里的库名和当前库不同也能强行覆盖但要小心别把有用的库给顶掉了。T-SQL 方式省去图形界面层层点击重点就是上面这段。4.2 完整差异日志的三段式还原假设现在是下午 13:30今天 13:25 有人误删了一张订单表你想把数据库还原到 13:25 这个误删之前的时刻。你手里有今天 06:00 的完整备份今天 12:00 的差异备份今天每 15 分钟的日志备份最新到 13:30还原脚本按顺序执行-- 第一步还原完整备份指定不恢复 RESTORE DATABASE [MyERP] FROM DISK ND:\SQLBackup\MyERP_FULL_20240815_0600.bak WITH NORECOVERY, REPLACE; -- 第二步还原最近一次差异备份仍然不恢复 RESTORE DATABASE [MyERP] FROM DISK ND:\SQLBackup\MyERP_DIFF_20240815_1200.bak WITH NORECOVERY; -- 第三步按顺序还原日志并在目标时间点停止 RESTORE LOG [MyERP] FROM DISK ND:\SQLBackup\MyERP_LOG_20240815_1215.trn WITH NORECOVERY; RESTORE LOG [MyERP] FROM DISK ND:\SQLBackup\MyERP_LOG_20240815_1230.trn WITH NORECOVERY; RESTORE LOG [MyERP] FROM DISK ND:\SQLBackup\MyERP_LOG_20240815_1245.trn WITH STOPAT N2024-08-15T13:25:00, RECOVERY;第三步是关键STOPAT指定还原停止时间点。如果 13:00 和 13:15 的日志备份也在手里应该放在 13:00、13:15、13:30 都还原最后一份用STOPAT精确停下来。注意这里写的 13:30 日志备份可以包含 13:25 这个时间点的事务所以用STOPAT把它停在 13:25。如果备份文件在时间上不连续中间缺了一份SQL Server 会报日志链断裂无法继续还原所以日志备份的文件完整性很重要。4.3 还原时的 MOVE 选项和路径调整数据库迁移到新服务器是最常见的还原场景之一。原库的数据文件和日志文件在D:\Data新服务器的盘符和目录不同直接还原会报文件 MyERP 无法还原到 D:\Data\MyERP.mdf请使用 WITH MOVE。解决办法是先看备份文件里的逻辑文件名RESTORE FILELISTONLY FROM DISK ND:\SQLBackup\MyERP_FULL_20240815.bak;这会输出 LogicalName、PhysicalName、Type 等信息。然后用 MOVE 把它重新映射到目标路径RESTORE DATABASE [MyERP] FROM DISK ND:\SQLBackup\MyERP_FULL_20240815.bak WITH MOVE NMyERP TO NE:\MSSQL\Data\MyERP.mdf, MOVE NMyERP_log TO NE:\MSSQL\Log\MyERP_log.ldf, REPLACE, RECOVERY;MOVE只影响物理文件位置不会改变逻辑文件名。遇到迁移时最好先执行RESTORE FILELISTONLY确认逻辑文件名再拼还原语句能省掉很多来回试的麻烦。5. 还原失败排查备份文件、权限和版本兼容性实操中还原大概率会踩坑这一节把最常见的问题按排查链路捋一遍下次遇到能少走弯路。5.1 校验和验证RESTORE VERIFYONLY 到底在验什么很多人备份完会执行一条命令RESTORE VERIFYONLY FROM DISK ND:\SQLBackup\MyERP_FULL_20240815.bak;结果显示验证成功就以为备份文件没问题。这里要泼盆冷水RESTORE VERIFYONLY只验证备份集的元数据和结构是否可读不会真正还原所有数据页。如果备份时用了WITH CHECKSUM它会顺手验证备份页的校验和此时覆盖率会高不少但它仍然不等同于一次完整的还原。真正靠谱的验证是在测试环境里把备份完整还原一次然后执行DBCC CHECKDB([MyERP]) WITH NO_INFOMSGS;CHECKDB 会扫描库的物理和逻辑完整性发现页面损坏、分配错误等问题。一个月一次恢复演练的成本并不高但能让你确认备份真的能用而不是等到灾难发生时才发现文件早就坏了。5.2 数据库被占用和权限问题怎么绕过去前面提过数据库正在使用的问题除了切单用户模式还可以直接找到占用会话并杀掉SELECT session_id, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE database_id DB_ID(NMyERP);找到阻塞的 session_id 后执行KILL session_id。注意如果杀掉的是别人的重要查询业务方会找你动手前最好确认会话来源。权限上执行 BACKUP DATABASE 需要db_backupoperator或db_owner角色还原需要服务器级sysadmin或dbcreator固定角色。如果 SQL Server 登录账号是普通用户还原时报权限不足或不允许备份或还原数据库那就去角色里添权限USE master; EXEC sp_addsrvrolemember Nyour_login, Ndbcreator;生产环境不建议给普通账号开太高的权限单独建一个专门做备份还原的账号会更安全。5.3 高版本备份能还原到低版本吗版本兼容陷阱直接给结论不能。SQL Server 备份文件是不能向下兼容的也就是说 SQL Server 2019 的备份无法还原到 SQL Server 2016。反过来低版本的备份可以向上还原到高版本。这是很多备份在测试环境还原时突然报备份文件版本不兼容的原因。排查思路报错后先看源库和目标库的版本号SELECT SERVERPROPERTY(ProductVersion), SERVERPROPERTY(Edition);如果确实存在版本差又没有更高版本的实例那就别想着直接还原备份文件了。务实的选择是用导出数据层应用程序BACPAC或生成脚本加数据导出的方式迁移结构和数据虽然慢一点但至少能完成跨版本迁移。5.4 还原后客户端连不上的一个容易忽略的配置把备份还原到一台新机器或新实例后客户端连接时偶尔会见到类似SQL Server SSL 提供程序证书链是由不受信任的颁发机构颁发的这种报错。这其实不是还原步骤的问题而是新实例的服务器证书没有被客户端信任。常见处理方式如果网络环境允许明文校验可以在连接字符串里加TrustServerCertificateTrue让客户端跳过对服务器证书链的校验更规范的做法是把新服务器的证书导入到客户端的受信任根证书存储区。如果之前启用了强制加密而服务器换了证书客户端连接时就会因为不信任新证书而失败。这个问题在迁移还原的场景里经常出现排查时不要只盯着备份文件还要把实例级的加密配置一并检查。6. 自动化备份落地维护计划与 Agent 作业手动备份偶尔做一次可以长期靠手动一定会有漏掉的一天。自动化这件事值得单独来写。6.1 维护计划向导配置定时完整备份SQL Server 自带的维护计划是最容易上手的方式SSMS 里展开管理右键维护计划选择新建维护计划向导。给计划起个名字比如DailyFullBackup。选择任务时勾选备份数据库完整如果想顺带做日志备份可以再加一个备份数据库事务日志。为每个备份任务指定数据库、备份目录、文件扩展名。定义计划触发时间比如每天凌晨 02:00。完成后维护计划会生成一个对应的 SQL Server Agent 作业到点自动执行。维护计划的优点是所有配置都有向导界面新手不容易漏选项。缺点是要做差异备份、日志备份、清理历史等复杂逻辑时多个任务配置起来比较啰嗦而且生成的代码不好纳入版本管理。所以很多团队用了一段时间维护计划后最终都转向了脚本化的 Agent 作业。6.2 用 SQL Agent 作业跑 T-SQL 备份脚本我更推荐的方式是直接用 SQL Agent 作业因为它完全由脚本控制能放进 Git 管理扩展能力也更强。大致操作是这样的展开SQL Server 代理右键作业新建作业。在步骤里新建一个步骤类型选Transact-SQL 脚本 (T-SQL)命令里写完整备份语句比如BACKUP DATABASE [MyERP] TO DISK ND:\SQLBackup\MyERP_FULL_ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), -, ) .bak WITH INIT, COMPRESSION, CHECKSUM, STATS 10;在计划里新建一个计划频率设为每天凌晨执行。如果要做日志备份再新建一个步骤或单独的作业频率设成每 15 分钟一次。用脚本的好处是可以把复杂逻辑塞进去比如备份前先做 DBCC CHECKDB备份后立即删除过期文件甚至发送告警邮件。只要 T-SQL 能写出来的流程Agent 都能定时跑。6.3 保留策略、异地备份和定期还原演练自动化跑起来之后下一个要处理的问题是备份文件堆积。一个完整备份 50 GB存 30 天就是 1.5 TB不清理会很快把磁盘塞满。常见的保留策略是完整备份保留 30 天。差异备份保留 14 天。日志备份保留 7 天。具体多久根据业务要求来如果审计要求必须留一年那就得规划近线存储或云归档。异地备份同样重要。备份和数据库放在同一台机器上硬盘坏了就是一起没。最理想的情况是把备份文件自动同步到另一台物理服务器或云存储至少能让数据库服务器整体宕掉时手里还有一份可以恢复的备份。你可以用 SQL Agent 作业外加 PowerShell 脚本定时拷贝也可以用系统自带的 robocopy 同步目录。最后不要跳过还原演练。我会定期在测试服务器上把最新完整备份恢复一遍把日志恢复到接近当前时间点再跑一次 DBCC CHECKDB。只有这一步通过了我才能跟业务方说备份是可靠的。恢复演练发现的问题永远比灾难发生后才发现问题要便宜得多。用这套思路走下来SQL Server 数据库的备份和还原就不是什么碰运气的事而是一套你可以放心睡大觉的机制。我个人的体会是备份方案里最重要的不是用多复杂的工具而是把恢复模式选对、把日志备份节奏稳住、把还原演练当回事。如果还没搭过今晚就可以先在测试库里做一次完整备份再加一次日志备份然后用还原向导亲手还原到另一个库里跑一遍 CHECKDB这套流程只要跑通一次后面就再也不慌了。