尧图网站建设 
做这行十来年我接手过的数据库事故没有一百也有八十。最让人无语的不是服务器硬盘烧了也不是机房断电而是接手时发现整个库从来没做过一次备份。你说气不气人数据库这东西平时安安静静躺着一旦出事就是大事而备份和还原就是唯一能救命的药。这篇我就把SQL Server的备份和还原从头到尾捋一遍从原理到实操、从图形界面到脚本自动化、从策略规划到故障排查一次性讲透保证你跟着做就能上手。这篇教程没有花架子也不整虚的全是实际干活时要用的东西。无论你是刚入门的运维、写业务的开发还是被迫兼职管数据库的小团队负责人只要你的服务器上有SQL Server这篇文章都值得你花十分钟看完然后照着配一套完整的备份方案。1. 先搞清楚SQL Server的备份机制再动手很多人上来就点备份跟赌博一样压根没搞明白自己备份的是什么、还原能还原到什么程度。这里面的核心其实是恢复模式Recovery Model它决定了你能做哪几种备份也决定了数据库日志会不会无限膨胀。动手之前先把这块地基打好。1.1 恢复模式完整、简单、大容量日志怎么选恢复模式是数据库级别的属性在SSMS里右键数据库→属性→选项就能看到。三种模式的本质区别在于日志文件里记录了哪些信息以及崩溃时能恢复到什么程度。完整恢复模式Full所有事务都会完整写入日志。好处是能支持时间点恢复PITR理论上可以把数据库恢复到故障发生前的一秒。坏处是日志文件会持续增长如果从不做日志备份它会涨到把磁盘撑爆。生产环境核心业务库比如订单、财务、用户数据一律用这个模式。简单恢复模式Simple事务提交后日志空间就被复用不需要做日志备份日志文件基本稳定。代价是灾难发生时只能恢复到最近一次完整/差异备份中间那段时间的数据全部丢失。适合数据允许丢失的开发库、测试库或者只读报表库。大容量日志恢复模式Bulk-Logged介于两者之间对大容量操作比如批量导入、索引重建只记少量日志性能好但时间点恢复到这些操作会受限。一般在大批量导数据时临时切换导完切回完整模式并立刻做一次日志备份。一句话总结想着能恢复到任意时间点的选完整模式能接受丢几小时数据的选简单模式。我接手过很多项目最常见的错误是业务库用了简单模式出事才发现恢复不了悔得肠子都青了。1.2 备份类型全量、差异、日志备份各管什么备份类型不是越多越好而是匹配恢复模式和使用场景。完整备份Full Backup备份整个数据库的所有数据页包括部分日志。这是所有恢复方案的基石。通常每天做一次放在业务低峰期。差异备份Differential Backup只备份自上次完整备份以来发生变化的数据页。体积比全量小很多备份速度快。通常在全量备份之间做几次比如每6小时或每天中午做一次。事务日志备份Transaction Log Backup只在完整恢复模式下有意义。备份的是日志中记录的所有事务可以恢复到任意时间点。频率可以很频繁比如15分钟一次甚至5分钟一次。它的特点是文件小、速度快但依赖完整备份作为基线。打个比方。完整备份相当于给书架拍了一张全景照片差异备份是拍从上次全景照片之后新增了哪些书日志备份则是记录每本书什么时候放上去、什么时候拿下来的操作流水账。要恢复书架的原貌你需要全景照片打底加上差异照片减少工作量再用流水账补充到精确时间点。需要特别提醒很多人把差异和增量混为一谈。SQL Server的差异备份是相对于上次完整备份的累积差异不是相对上次差异的增量。这是SQL Server与MySQL、文件系统备份逻辑上一个常见的认知差异理解错了会导致备份链设计失误。1.3 备份介质和目标位置本地磁盘、网络路径、磁带还是云SQL Server备份可以直接写到本地磁盘、网络共享路径、磁带现在很少见了或者Azure存储等云服务。介质选择直接影响备份的安全性和恢复的速度。我见过的翻车现场最经典的是备份文件和数据库文件放在同一块物理磁盘上。硬盘一坏数据和备份一起没了。这不是备份这是心理安慰。正确的做法是备份到独立磁盘或者至少放到独立的物理卷有条件就通过UNC路径写到另一台服务器或者直接备份到对象存储。另外网络路径UNC支持用\\server\share格式直接作为备份目标但要注意SQL Server服务账号必须对共享目录有写权限。如果你想备份到NAS或另一台机器这是最省事的方式不需要额外装任何软件。1.4 备份文件命名策略与保留周期这块看起来不起眼但到了要恢复的时候能省很多事。我踩过坑备份文件名一律dbname_YYYYMMDD.bak结果同一天做了多次备份文件名一模一样旧文件被直接覆盖。后来我统一成这个规则库名_类型_日期时间.bak 示例OrderDB_FULL_20250115_030001.bak、OrderDB_DIFF_20250115_120000.bak、OrderDB_LOG_20250115_153000.trn保留周期上我的经验值是全量保留最近14天差异保留最近7天日志保留最近3~5天。这样既能满足最近一周随时回退的需求又不会让磁盘被备份文件堆爆。清理工作不要手动做用一条PowerShell或批处理脚本跑定时任务就行后面会讲到。2. 实操SSMS图形界面备份与还原保姆级步骤如果你是偶尔手动备份一次SSMS的图形界面足够用。但要注意图形界面改不了底层的机制问题比如恢复模式不对、日志链断掉——这些得靠你对原理的理解去判断。2.1 用SSMS做一次完整备份5分钟学会打开SSMS连上实例展开数据库节点右键目标数据库 → 任务 → 备份弹出备份窗口。在常规页备份类型选完整备份组件选数据库。目标那里默认是磁盘上的库名.bak点删除去掉默认路径再点添加选择你要存放的位置。文件名按前面说的命名规则来。点选项页勾选覆盖所有现有备份集这样不会因为同名文件而报错。完成后点确定SSMS右下角会提示备份已成功完成。这里有个魔术般的操作默认备份文件名如果重复SQL Server会报文件已存在。所以每次要么手动改文件名要么勾选覆盖所有现有备份集。要恢复旧备份的千万别勾覆盖否则好端端的原备份就被新数据冲掉了。我习惯每次都写上带时间戳的新文件名保留现场又稳妥。提示如果你在做备份时勾选了复制仅备份那么它不会影响原备份链。在给生产库首次配置自动备份之前用这个选项试跑一两次再正式上作业能避免不少意外。2.2 还原数据库完整还原和差异链还原还原操作同样在数据库节点上。右键数据库 → 任务 → 还原 → 数据库。源设备类型选设备点省略号选择备份文件。SQL Server会自动识别该文件包含哪些备份集。如果文件里有多个备份集比如一次完整加多次日志底部网格会列出。默认勾选最近一次完整备份。如果要连差异一起还原把差异备份集也勾上。目标数据库名默认是原来的名字你完全可以改成新库名。这是把一个库克隆成另一个库最常用的路子。点选项页勾选覆盖现有数据库WITH REPLACE否则如果库已存在且连接还原会失败。勾选关闭与目标数据库的现有连接以免有会话占用导致无法还原。恢复状态选择RESTORE WITH RECOVERY这是正常还原完毕、库可用的状态。注意如果你想还原后立即查看数据但还想继续往前恢复要选RESTORE WITH NORECOVERY。这个模式会让数据库处于Restoring状态看起来像卡住了其实是正常的这是做日志链还原的必经状态。一次性把完整备份和差异备份勾选还原SQL Server会自动按顺序读取不需要你手动拆分步骤。但日志备份要单独加进去因为你要指定还原到哪个时间点。2.3 时间点还原把数据库恢复到出事的下一秒完整恢复模式下最值钱的功能就是时间点还原Point-in-Time Restore。假设今天下午3点有人误删了一大批数据如果你在3点前的日志备份都齐全就能把库恢复到2点59分59秒的状态。操作时先按上面的方式选中完整备份、相应的差异备份和若干日志备份然后在上方还原到这个时间点处点浏览选择具体时间。更精确的做法是回填时间值时精确到秒确保不把误删操作的那笔事务包括进去。SQL Server会把指定时间点之后提交的事务回滚掉。现实的坑在于很多开发库用的简单恢复模式根本做不了日志备份也做不了时间点还原。你要是给用户库用了简单模式就别指望时间点恢复了老老实实承认只能恢复到最近一次备份吧。3. 进阶T-SQL脚本化备份与自动化任务图形界面适合临时救急但不适合长期运维。生产库的备份必须是自动化的、可监控的、可追溯的。这一节把脚本写法和自动作业配置一次讲全。3.1 常用T-SQL备份与还原脚本模板完整备份的一句话命令BACKUP DATABASE [OrderDB] TO DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH INIT, COMPRESSION, STATS 10;COMPRESSION是备份压缩默认在标准版和企业版可以启用STATS 10表示每完成10%打印一次进度。压缩通常能省一半空间还能减少I/O压力强烈建议保持开启。差异备份BACKUP DATABASE [OrderDB] TO DISK ND:\Backup\OrderDB_DIFF_20250115_120000.bak WITH DIFFERENTIAL, INIT, COMPRESSION, STATS 10;日志备份BACKUP LOG [OrderDB] TO DISK ND:\Backup\OrderDB_LOG_20250115_153000.trn WITH INIT, COMPRESSION, STATS 10;还原完整备份RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH REPLACE, RECOVERY;还原完整差异然后接着回放日志到指定时间点RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH NORECOVERY, REPLACE; RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_DIFF_20250115_120000.dif WITH NORECOVERY; RESTORE LOG [OrderDB] FROM DISK ND:\Backup\OrderDB_LOG_20250115_153000.trn WITH RECOVERY, STOPAT N2025-01-15T15:29:59;顺序不能乱完整 → 差异如果有且时间点晚于完整备份 → 日志按备份时间正序一个个来。中间全用NORECOVERY最后一个用RECOVERY库才算是恢复完成并上线。3.2 备份策略规划一套可以直接抄的作业以一套业务量中等、数据变更频繁的生产库为例给一个可落地的组合方案备份类型频率保留周期说明完整备份每天一次凌晨3点14天压缩后通常几十GB内差异备份每天两次中午12点、晚上21点7天缩小日志恢复时的数据量日志备份每15分钟一次3天恢复窗口控制在15分钟内这套方案能保证最多丢失15分钟数据恢复时先全量、再差异、再回放少量日志耗时可控。如果数据量非常大比如全量就要跑2小时可以考虑调整完整备份为每周六一次、周日到周五只做差异和日志但恢复时间会长一些。需要测算一下恢复时间目标RTO。如果你每天凌晨3点全量备份中午12点差异备份下午2点出事。恢复时加载全量可能需要1小时加上差异加上不到2小时的日志约8个日志文件整个恢复时长约1.5小时。如果这个数字在业务容忍范围内方案就是合格的。3.3 SQL Agent作业配置把备份变成自动的SQL Server Agent是自带的任务调度器没有它之前我都是靠Windows计划任务跑SQL脚本也可以用但不够灵活。推荐用Agent作业因为可以看历史、发告警、调优先级。创建一个备份作业的步骤SSMS对象资源管理器里展开SQL Server Agent右键作业 → 新建作业。常规页填名称比如Backup_OrderDB_Full_Daily。步骤页点新建。步骤名称填完整备份类型选Transact-SQL 脚本 (T-SQL)数据库选master这里特别重要不要在步骤里USE目标库因为备份的是指定库mater上下文更安全。命令框里贴入上面的BACKUP DATABASE脚本。计划页新建计划。类型选重复执行频率设每天时间设为凌晨3:00注意避开业务高峰和统计作业。确定后右键作业 → 启动作业先手动试跑一次。跑完右键查看历史确认无报错。差异备份和日志备份各建一个作业计划时间按策略表来。日志备份作业每15分钟触发一次会在你的服务器上创建一个近乎永续的运行记录。提示无论什么作业第一次跑完后务必打开作业活动监视器查看最近运行结果。别让计划看似配置了实际上从来没跑过这种事发生在你身上我见得多了。3.4 跨服务器迁移与异机还原备份还原是数据库迁移最稳妥的手段没有之一。要把生产库从A迁移到B最标准操作是在A库做完整备份拷贝到B服务器然后用RESTORE ... WITH RECOVERY还原为新库。这里有两个常见场景同实例克隆新库还原时目标数据库名填新名字比如OrderDB_Dev可以同时让开发库和生产库共存。跨实例迁移注意SQL Server版本不能低版本往高版本倒着来低版本备份文件可以被高版本还原反之不行。2008 R2的备份拿到2019上还原没问题2019的备份拿到2012上则报数据库版本不兼容。跨服务器时文件路径也要改。还原脚本示例RESTORE DATABASE [OrderDB] FROM DISK N\\NAS\SQLBackup\OrderDB_FULL_20250115_030001.bak WITH MOVE OrderDB TO NE:\Data\OrderDB.mdf, MOVE OrderDB_log TO NF:\Log\OrderDB_log.ldf, REPLACE, RECOVERY;WITH MOVE是让数据库文件落到目标服务器的特定位置。不指定MOVE的话SQL Server会尝试在目标服务器的相同路径下找文件失败就会报错。4. 真实场景排查SQL Server备份还原的坑与经验再完美的方案到了真刀真枪的时候总会有你想不到的情况。这一节全是我在实际运维中遇到过、并且花了不少时间才弄明白的问题直接整理成速查表和你分享。4.1 常见错误对照表报错信息、原因与处理报错特征常见原因处理方式无法打开备份设备/操作系统错误 5(拒绝访问)SQL Server服务账号无权限给服务账号授予备份目录的读写权限或改用服务账号能访问的路径RESTORE ... 未能打开备份设备/ 路径不存在目标服务器路径与源主机不一致使用WITH MOVE重定位文件确认目标目录存在备份集包含的数据库名与现有数据库不同从别的库文件恢复或备份文件里有多个库用RESTORE HEADERONLY查看备份集信息确认库名数据库正在使用因此无法获得对它的独占访问权有其他会话连着库用ALTER DATABASE [库名] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;再还原或勾选关闭连接有一个或多个数据库未能恢复日志链断裂、备份文件损坏检查完整/差异/日志的时间顺序用RESTORE VERIFYONLY校验文件LSN 太早/日志备份中的 LSN 与数据库不匹配日志链断裂数据库曾被覆盖或日志备份缺失只能用最近的完整备份启动新的备份链还原后数据不全差异备份时间早于完整备份或日志未回放确认还原顺序正确完整 → 差异 → 日志这些错误里我碰到最多的还是权限问题。尤其是你把备份放到了D盘某个业务软件创建的目录而该目录的ACL并不允许SQL Server服务账号写入。排查思路先用文件资源管理器确认路径是否存在再用一个普通域账号试着往该路径写文件最后再看SQL Server实例的服务账号是什么。4.2 备份慢、写日志慢WRITELOG等待的性能问题如果你的库在备份或者正常运行时大量出现WRITELOG等待类型先别慌着加CPU加内存。这个等待通常意味着日志文件写入速度跟不上事务提交速度根源往往是日志和数据文件放在同一块物理磁盘上导致随机写入争抢同一块磁盘的I/O。一个高性能的SQL Server实例日志文件的物理位置应当与数据文件分离。日志文件对顺序写要求极高放到独立的SSD或者独立的数据盘上性能提升会非常明显。备份时如果发现备份过程很慢也先检查目标磁盘的I/O能力比如备份写到机械盘跑到10MB/s这就不正常了。用压缩备份减少生成的I/O量通常能解决一大半问题。数据库所在磁盘充足空间是另一个前提。记住一条硬约束日志文件所在分区的可用空间必须大于日志文件当前大小。否则瞬间就会报事务日志已满严重时直接宕库。备份文件目录也需要预留足够的空间全量备份通常是库实际大小的30%~80%取决于压缩和数据冗余度。4.3 备份文件校验与安全别在恢复时才发现备份是坏的备份文件没有校验等于没有备份。我自己经历过两次还原到一半报损坏的尴尬从那以后备份出来的文件我都会跑一次校验。校验有两种RESTORE VERIFYONLY FROM DISK N...只检查备份集完整性不实际还原数据速度快。定期的还原演练脚本还原到临时库再Drop掉这是最真实的验证。真正恢复一次比检查一百遍都让人放心。备份文件同样要注意安全。如果备份里有敏感数据装到传输介质里就是泄露风险我的建议是启用SQL Server的透明数据加密TDE保护库文件本身备份时再配合加密BACKUP DATABASE ... WITH ENCRYPTION需要证书或非对称密钥。生产库的备份文件散落在网盘、U盘上那是定时炸弹。4.4 周边工具与第三方软件的取舍SQL Server自带的SSMS备份对于大多数场景已经够用但有些场景需要第三方工具辅助。比如你想批量管理几十台实例的统一备份或者需要把备份直接推送到云对象存储可以看看这几类工具SQL Server Agent PowerShell脚本免费灵活适合自己动手的用户。商业备份软件比如Veritas Backup Exec、Commvault、Veeam企业级环境常用能和SQL Server交互实现应用一致性备份同时备份操作系统层面文件。适合有预算、有合规要求的大中型企业。数据库管理工具如Navicat for SQL Server、dbx数据库工具可以连SQL Server做基础备份还原适合偶尔跨平台管理数据库、不想装SSMS的场景。但对还原的精细控制不如原生SSMS。另外提一嘴数据库同步不等同于数据库备份。同步软件如AlwaysOn可用性组、第三方同步工具解决的是实时冗余问题是用来降低RTO的备份解决的是回到过去的问题能防止人为或逻辑错误。两者互补别混为一谈。4.5 备份策略随版本演进的注意事项SQL Server版本跨度很大2008 R2、2012、2016、2019到现在更迭不断备份原理基本一致但有几个差异点需要注意2012开始引入了新的备份格式和增强的压缩算法压缩比例通常更好。2016开始支持备份加密BACKUP ... WITH ENCRYPTION。2019开始支持备份到S3兼容对象存储不走第三方工具也能低成本异地容灾。版本太老如2000/2005的备份文件在高版本还原通常没问题但高版本备份不能低版本还原。跨大版本迁移时先确认兼容级别和功能差异。还有个小技巧还原一个很老的备份文件到高版本实例时有时候会提示数据库版本不兼容。可以用RESTORE HEADERONLY看看BackupFormatVersion和DatabaseVersion确认备份文件来自哪个版本的实例再决定要不要升级。最后分享一点我个人的经验。每次配置完备份方案我都会把恢复文档写死并打印一份放在机房的醒目位置。文档里包含三样东西备份文件存放位置、最近一次完整备份的文件名、完整的还原命令脚本。你很难保证关键时刻你还能冷静打开电脑翻找文件但一张A4纸上的命令永远比记忆可靠。备份是运维的保险而还原演练才是保险是否生效的唯一验证手段。每月抽5分钟把最近的全量备份还原到一台临时实例查一下关键表的数据量确认无误再删掉。这套成本极低的流程能在真正出事时救你一命。 SQL Server备份与还原实战：从机制原理到自动化运维
做这行十来年我接手过的数据库事故没有一百也有八十。最让人无语的不是服务器硬盘烧了也不是机房断电而是接手时发现整个库从来没做过一次备份。你说气不气人数据库这东西平时安安静静躺着一旦出事就是大事而备份和还原就是唯一能救命的药。这篇我就把SQL Server的备份和还原从头到尾捋一遍从原理到实操、从图形界面到脚本自动化、从策略规划到故障排查一次性讲透保证你跟着做就能上手。这篇教程没有花架子也不整虚的全是实际干活时要用的东西。无论你是刚入门的运维、写业务的开发还是被迫兼职管数据库的小团队负责人只要你的服务器上有SQL Server这篇文章都值得你花十分钟看完然后照着配一套完整的备份方案。1. 先搞清楚SQL Server的备份机制再动手很多人上来就点备份跟赌博一样压根没搞明白自己备份的是什么、还原能还原到什么程度。这里面的核心其实是恢复模式Recovery Model它决定了你能做哪几种备份也决定了数据库日志会不会无限膨胀。动手之前先把这块地基打好。1.1 恢复模式完整、简单、大容量日志怎么选恢复模式是数据库级别的属性在SSMS里右键数据库→属性→选项就能看到。三种模式的本质区别在于日志文件里记录了哪些信息以及崩溃时能恢复到什么程度。完整恢复模式Full所有事务都会完整写入日志。好处是能支持时间点恢复PITR理论上可以把数据库恢复到故障发生前的一秒。坏处是日志文件会持续增长如果从不做日志备份它会涨到把磁盘撑爆。生产环境核心业务库比如订单、财务、用户数据一律用这个模式。简单恢复模式Simple事务提交后日志空间就被复用不需要做日志备份日志文件基本稳定。代价是灾难发生时只能恢复到最近一次完整/差异备份中间那段时间的数据全部丢失。适合数据允许丢失的开发库、测试库或者只读报表库。大容量日志恢复模式Bulk-Logged介于两者之间对大容量操作比如批量导入、索引重建只记少量日志性能好但时间点恢复到这些操作会受限。一般在大批量导数据时临时切换导完切回完整模式并立刻做一次日志备份。一句话总结想着能恢复到任意时间点的选完整模式能接受丢几小时数据的选简单模式。我接手过很多项目最常见的错误是业务库用了简单模式出事才发现恢复不了悔得肠子都青了。1.2 备份类型全量、差异、日志备份各管什么备份类型不是越多越好而是匹配恢复模式和使用场景。完整备份Full Backup备份整个数据库的所有数据页包括部分日志。这是所有恢复方案的基石。通常每天做一次放在业务低峰期。差异备份Differential Backup只备份自上次完整备份以来发生变化的数据页。体积比全量小很多备份速度快。通常在全量备份之间做几次比如每6小时或每天中午做一次。事务日志备份Transaction Log Backup只在完整恢复模式下有意义。备份的是日志中记录的所有事务可以恢复到任意时间点。频率可以很频繁比如15分钟一次甚至5分钟一次。它的特点是文件小、速度快但依赖完整备份作为基线。打个比方。完整备份相当于给书架拍了一张全景照片差异备份是拍从上次全景照片之后新增了哪些书日志备份则是记录每本书什么时候放上去、什么时候拿下来的操作流水账。要恢复书架的原貌你需要全景照片打底加上差异照片减少工作量再用流水账补充到精确时间点。需要特别提醒很多人把差异和增量混为一谈。SQL Server的差异备份是相对于上次完整备份的累积差异不是相对上次差异的增量。这是SQL Server与MySQL、文件系统备份逻辑上一个常见的认知差异理解错了会导致备份链设计失误。1.3 备份介质和目标位置本地磁盘、网络路径、磁带还是云SQL Server备份可以直接写到本地磁盘、网络共享路径、磁带现在很少见了或者Azure存储等云服务。介质选择直接影响备份的安全性和恢复的速度。我见过的翻车现场最经典的是备份文件和数据库文件放在同一块物理磁盘上。硬盘一坏数据和备份一起没了。这不是备份这是心理安慰。正确的做法是备份到独立磁盘或者至少放到独立的物理卷有条件就通过UNC路径写到另一台服务器或者直接备份到对象存储。另外网络路径UNC支持用\\server\share格式直接作为备份目标但要注意SQL Server服务账号必须对共享目录有写权限。如果你想备份到NAS或另一台机器这是最省事的方式不需要额外装任何软件。1.4 备份文件命名策略与保留周期这块看起来不起眼但到了要恢复的时候能省很多事。我踩过坑备份文件名一律dbname_YYYYMMDD.bak结果同一天做了多次备份文件名一模一样旧文件被直接覆盖。后来我统一成这个规则库名_类型_日期时间.bak 示例OrderDB_FULL_20250115_030001.bak、OrderDB_DIFF_20250115_120000.bak、OrderDB_LOG_20250115_153000.trn保留周期上我的经验值是全量保留最近14天差异保留最近7天日志保留最近3~5天。这样既能满足最近一周随时回退的需求又不会让磁盘被备份文件堆爆。清理工作不要手动做用一条PowerShell或批处理脚本跑定时任务就行后面会讲到。2. 实操SSMS图形界面备份与还原保姆级步骤如果你是偶尔手动备份一次SSMS的图形界面足够用。但要注意图形界面改不了底层的机制问题比如恢复模式不对、日志链断掉——这些得靠你对原理的理解去判断。2.1 用SSMS做一次完整备份5分钟学会打开SSMS连上实例展开数据库节点右键目标数据库 → 任务 → 备份弹出备份窗口。在常规页备份类型选完整备份组件选数据库。目标那里默认是磁盘上的库名.bak点删除去掉默认路径再点添加选择你要存放的位置。文件名按前面说的命名规则来。点选项页勾选覆盖所有现有备份集这样不会因为同名文件而报错。完成后点确定SSMS右下角会提示备份已成功完成。这里有个魔术般的操作默认备份文件名如果重复SQL Server会报文件已存在。所以每次要么手动改文件名要么勾选覆盖所有现有备份集。要恢复旧备份的千万别勾覆盖否则好端端的原备份就被新数据冲掉了。我习惯每次都写上带时间戳的新文件名保留现场又稳妥。提示如果你在做备份时勾选了复制仅备份那么它不会影响原备份链。在给生产库首次配置自动备份之前用这个选项试跑一两次再正式上作业能避免不少意外。2.2 还原数据库完整还原和差异链还原还原操作同样在数据库节点上。右键数据库 → 任务 → 还原 → 数据库。源设备类型选设备点省略号选择备份文件。SQL Server会自动识别该文件包含哪些备份集。如果文件里有多个备份集比如一次完整加多次日志底部网格会列出。默认勾选最近一次完整备份。如果要连差异一起还原把差异备份集也勾上。目标数据库名默认是原来的名字你完全可以改成新库名。这是把一个库克隆成另一个库最常用的路子。点选项页勾选覆盖现有数据库WITH REPLACE否则如果库已存在且连接还原会失败。勾选关闭与目标数据库的现有连接以免有会话占用导致无法还原。恢复状态选择RESTORE WITH RECOVERY这是正常还原完毕、库可用的状态。注意如果你想还原后立即查看数据但还想继续往前恢复要选RESTORE WITH NORECOVERY。这个模式会让数据库处于Restoring状态看起来像卡住了其实是正常的这是做日志链还原的必经状态。一次性把完整备份和差异备份勾选还原SQL Server会自动按顺序读取不需要你手动拆分步骤。但日志备份要单独加进去因为你要指定还原到哪个时间点。2.3 时间点还原把数据库恢复到出事的下一秒完整恢复模式下最值钱的功能就是时间点还原Point-in-Time Restore。假设今天下午3点有人误删了一大批数据如果你在3点前的日志备份都齐全就能把库恢复到2点59分59秒的状态。操作时先按上面的方式选中完整备份、相应的差异备份和若干日志备份然后在上方还原到这个时间点处点浏览选择具体时间。更精确的做法是回填时间值时精确到秒确保不把误删操作的那笔事务包括进去。SQL Server会把指定时间点之后提交的事务回滚掉。现实的坑在于很多开发库用的简单恢复模式根本做不了日志备份也做不了时间点还原。你要是给用户库用了简单模式就别指望时间点恢复了老老实实承认只能恢复到最近一次备份吧。3. 进阶T-SQL脚本化备份与自动化任务图形界面适合临时救急但不适合长期运维。生产库的备份必须是自动化的、可监控的、可追溯的。这一节把脚本写法和自动作业配置一次讲全。3.1 常用T-SQL备份与还原脚本模板完整备份的一句话命令BACKUP DATABASE [OrderDB] TO DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH INIT, COMPRESSION, STATS 10;COMPRESSION是备份压缩默认在标准版和企业版可以启用STATS 10表示每完成10%打印一次进度。压缩通常能省一半空间还能减少I/O压力强烈建议保持开启。差异备份BACKUP DATABASE [OrderDB] TO DISK ND:\Backup\OrderDB_DIFF_20250115_120000.bak WITH DIFFERENTIAL, INIT, COMPRESSION, STATS 10;日志备份BACKUP LOG [OrderDB] TO DISK ND:\Backup\OrderDB_LOG_20250115_153000.trn WITH INIT, COMPRESSION, STATS 10;还原完整备份RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH REPLACE, RECOVERY;还原完整差异然后接着回放日志到指定时间点RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_FULL_20250115_030001.bak WITH NORECOVERY, REPLACE; RESTORE DATABASE [OrderDB] FROM DISK ND:\Backup\OrderDB_DIFF_20250115_120000.dif WITH NORECOVERY; RESTORE LOG [OrderDB] FROM DISK ND:\Backup\OrderDB_LOG_20250115_153000.trn WITH RECOVERY, STOPAT N2025-01-15T15:29:59;顺序不能乱完整 → 差异如果有且时间点晚于完整备份 → 日志按备份时间正序一个个来。中间全用NORECOVERY最后一个用RECOVERY库才算是恢复完成并上线。3.2 备份策略规划一套可以直接抄的作业以一套业务量中等、数据变更频繁的生产库为例给一个可落地的组合方案备份类型频率保留周期说明完整备份每天一次凌晨3点14天压缩后通常几十GB内差异备份每天两次中午12点、晚上21点7天缩小日志恢复时的数据量日志备份每15分钟一次3天恢复窗口控制在15分钟内这套方案能保证最多丢失15分钟数据恢复时先全量、再差异、再回放少量日志耗时可控。如果数据量非常大比如全量就要跑2小时可以考虑调整完整备份为每周六一次、周日到周五只做差异和日志但恢复时间会长一些。需要测算一下恢复时间目标RTO。如果你每天凌晨3点全量备份中午12点差异备份下午2点出事。恢复时加载全量可能需要1小时加上差异加上不到2小时的日志约8个日志文件整个恢复时长约1.5小时。如果这个数字在业务容忍范围内方案就是合格的。3.3 SQL Agent作业配置把备份变成自动的SQL Server Agent是自带的任务调度器没有它之前我都是靠Windows计划任务跑SQL脚本也可以用但不够灵活。推荐用Agent作业因为可以看历史、发告警、调优先级。创建一个备份作业的步骤SSMS对象资源管理器里展开SQL Server Agent右键作业 → 新建作业。常规页填名称比如Backup_OrderDB_Full_Daily。步骤页点新建。步骤名称填完整备份类型选Transact-SQL 脚本 (T-SQL)数据库选master这里特别重要不要在步骤里USE目标库因为备份的是指定库mater上下文更安全。命令框里贴入上面的BACKUP DATABASE脚本。计划页新建计划。类型选重复执行频率设每天时间设为凌晨3:00注意避开业务高峰和统计作业。确定后右键作业 → 启动作业先手动试跑一次。跑完右键查看历史确认无报错。差异备份和日志备份各建一个作业计划时间按策略表来。日志备份作业每15分钟触发一次会在你的服务器上创建一个近乎永续的运行记录。提示无论什么作业第一次跑完后务必打开作业活动监视器查看最近运行结果。别让计划看似配置了实际上从来没跑过这种事发生在你身上我见得多了。3.4 跨服务器迁移与异机还原备份还原是数据库迁移最稳妥的手段没有之一。要把生产库从A迁移到B最标准操作是在A库做完整备份拷贝到B服务器然后用RESTORE ... WITH RECOVERY还原为新库。这里有两个常见场景同实例克隆新库还原时目标数据库名填新名字比如OrderDB_Dev可以同时让开发库和生产库共存。跨实例迁移注意SQL Server版本不能低版本往高版本倒着来低版本备份文件可以被高版本还原反之不行。2008 R2的备份拿到2019上还原没问题2019的备份拿到2012上则报数据库版本不兼容。跨服务器时文件路径也要改。还原脚本示例RESTORE DATABASE [OrderDB] FROM DISK N\\NAS\SQLBackup\OrderDB_FULL_20250115_030001.bak WITH MOVE OrderDB TO NE:\Data\OrderDB.mdf, MOVE OrderDB_log TO NF:\Log\OrderDB_log.ldf, REPLACE, RECOVERY;WITH MOVE是让数据库文件落到目标服务器的特定位置。不指定MOVE的话SQL Server会尝试在目标服务器的相同路径下找文件失败就会报错。4. 真实场景排查SQL Server备份还原的坑与经验再完美的方案到了真刀真枪的时候总会有你想不到的情况。这一节全是我在实际运维中遇到过、并且花了不少时间才弄明白的问题直接整理成速查表和你分享。4.1 常见错误对照表报错信息、原因与处理报错特征常见原因处理方式无法打开备份设备/操作系统错误 5(拒绝访问)SQL Server服务账号无权限给服务账号授予备份目录的读写权限或改用服务账号能访问的路径RESTORE ... 未能打开备份设备/ 路径不存在目标服务器路径与源主机不一致使用WITH MOVE重定位文件确认目标目录存在备份集包含的数据库名与现有数据库不同从别的库文件恢复或备份文件里有多个库用RESTORE HEADERONLY查看备份集信息确认库名数据库正在使用因此无法获得对它的独占访问权有其他会话连着库用ALTER DATABASE [库名] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;再还原或勾选关闭连接有一个或多个数据库未能恢复日志链断裂、备份文件损坏检查完整/差异/日志的时间顺序用RESTORE VERIFYONLY校验文件LSN 太早/日志备份中的 LSN 与数据库不匹配日志链断裂数据库曾被覆盖或日志备份缺失只能用最近的完整备份启动新的备份链还原后数据不全差异备份时间早于完整备份或日志未回放确认还原顺序正确完整 → 差异 → 日志这些错误里我碰到最多的还是权限问题。尤其是你把备份放到了D盘某个业务软件创建的目录而该目录的ACL并不允许SQL Server服务账号写入。排查思路先用文件资源管理器确认路径是否存在再用一个普通域账号试着往该路径写文件最后再看SQL Server实例的服务账号是什么。4.2 备份慢、写日志慢WRITELOG等待的性能问题如果你的库在备份或者正常运行时大量出现WRITELOG等待类型先别慌着加CPU加内存。这个等待通常意味着日志文件写入速度跟不上事务提交速度根源往往是日志和数据文件放在同一块物理磁盘上导致随机写入争抢同一块磁盘的I/O。一个高性能的SQL Server实例日志文件的物理位置应当与数据文件分离。日志文件对顺序写要求极高放到独立的SSD或者独立的数据盘上性能提升会非常明显。备份时如果发现备份过程很慢也先检查目标磁盘的I/O能力比如备份写到机械盘跑到10MB/s这就不正常了。用压缩备份减少生成的I/O量通常能解决一大半问题。数据库所在磁盘充足空间是另一个前提。记住一条硬约束日志文件所在分区的可用空间必须大于日志文件当前大小。否则瞬间就会报事务日志已满严重时直接宕库。备份文件目录也需要预留足够的空间全量备份通常是库实际大小的30%~80%取决于压缩和数据冗余度。4.3 备份文件校验与安全别在恢复时才发现备份是坏的备份文件没有校验等于没有备份。我自己经历过两次还原到一半报损坏的尴尬从那以后备份出来的文件我都会跑一次校验。校验有两种RESTORE VERIFYONLY FROM DISK N...只检查备份集完整性不实际还原数据速度快。定期的还原演练脚本还原到临时库再Drop掉这是最真实的验证。真正恢复一次比检查一百遍都让人放心。备份文件同样要注意安全。如果备份里有敏感数据装到传输介质里就是泄露风险我的建议是启用SQL Server的透明数据加密TDE保护库文件本身备份时再配合加密BACKUP DATABASE ... WITH ENCRYPTION需要证书或非对称密钥。生产库的备份文件散落在网盘、U盘上那是定时炸弹。4.4 周边工具与第三方软件的取舍SQL Server自带的SSMS备份对于大多数场景已经够用但有些场景需要第三方工具辅助。比如你想批量管理几十台实例的统一备份或者需要把备份直接推送到云对象存储可以看看这几类工具SQL Server Agent PowerShell脚本免费灵活适合自己动手的用户。商业备份软件比如Veritas Backup Exec、Commvault、Veeam企业级环境常用能和SQL Server交互实现应用一致性备份同时备份操作系统层面文件。适合有预算、有合规要求的大中型企业。数据库管理工具如Navicat for SQL Server、dbx数据库工具可以连SQL Server做基础备份还原适合偶尔跨平台管理数据库、不想装SSMS的场景。但对还原的精细控制不如原生SSMS。另外提一嘴数据库同步不等同于数据库备份。同步软件如AlwaysOn可用性组、第三方同步工具解决的是实时冗余问题是用来降低RTO的备份解决的是回到过去的问题能防止人为或逻辑错误。两者互补别混为一谈。4.5 备份策略随版本演进的注意事项SQL Server版本跨度很大2008 R2、2012、2016、2019到现在更迭不断备份原理基本一致但有几个差异点需要注意2012开始引入了新的备份格式和增强的压缩算法压缩比例通常更好。2016开始支持备份加密BACKUP ... WITH ENCRYPTION。2019开始支持备份到S3兼容对象存储不走第三方工具也能低成本异地容灾。版本太老如2000/2005的备份文件在高版本还原通常没问题但高版本备份不能低版本还原。跨大版本迁移时先确认兼容级别和功能差异。还有个小技巧还原一个很老的备份文件到高版本实例时有时候会提示数据库版本不兼容。可以用RESTORE HEADERONLY看看BackupFormatVersion和DatabaseVersion确认备份文件来自哪个版本的实例再决定要不要升级。最后分享一点我个人的经验。每次配置完备份方案我都会把恢复文档写死并打印一份放在机房的醒目位置。文档里包含三样东西备份文件存放位置、最近一次完整备份的文件名、完整的还原命令脚本。你很难保证关键时刻你还能冷静打开电脑翻找文件但一张A4纸上的命令永远比记忆可靠。备份是运维的保险而还原演练才是保险是否生效的唯一验证手段。每月抽5分钟把最近的全量备份还原到一台临时实例查一下关键表的数据量确认无误再删掉。这套成本极低的流程能在真正出事时救你一命。