SQL Server删除卡在RESTORING状态数据库的完整方案

遇到一个数据库卡在“正在恢复”状态、删又删不掉的时候很多人的第一反应是执行DROP DATABASE结果 SQL Server 直接甩出一句报错告诉你这个库正处在 RESTORING 状态只能执行 RESTORE 操作。这篇文章想说的就是这个场景SQL Server 删除正在恢复RESTORING数据库的完整方法以及背后为什么会出现这种状态、有哪些安全的删除路径、每一步操作的理由是什么。无论你是专职 DBA还是偶尔要做备份还原演练的开发同学只要遇到过“删不掉”的库这套思路都可以直接用。我先把结论放前面删除 RESTORING 状态的库没有“一招通吃”的万能命令但有一条很清晰的解决链路——先查会话、杀占用再走正常恢复流程恢复不了就用 EMERGENCY 强制状态硬删。下面我按这个链路把原理、命令、坑点一次讲透。1. 认识 RESTORING 状态它从哪里来为什么删不掉1.1 先分清几种容易混淆的状态SQL Server 里数据库状态名不少英文状态和中文提示还经常对不上排查的时候最怕把状态搞混。最常见的是下面这几种状态英文状态实际含义在线ONLINE正常可用正在恢复 / 正在还原RESTORING正在进行还原过程或使用了 NORECOVERY 等待后续恢复正在恢复启动RECOVERING实例启动时正在执行事务回滚/前滚过一会儿会变 ONLINE恢复挂起RECOVERY_PENDING恢复过程中缺少文件或权限有问题可疑SUSPECT文件访问失败、IO 异常等导致库无法正常启动紧急模式EMERGENCY由 DBA 手动设置的只读维护状态方便排查损坏库离线OFFLINE由 DBA 手动下线很多人会把 RECOVERING 和 RESTORING 搞混其实一个发生在实例启动阶段一个发生在还原操作阶段。本文标题说的“正在恢复”实际上对应的就是 RESTORING 这个状态——你在 SSMS 里看到数据库名字后面跟着“正在恢复 / 正在还原”的提示那就是它了。1.2 RESTORING 状态下 DROP 被拒绝的两个原因数据库处于 RESTORING 状态时直接执行 DROPSQL Server 通常会报两种错一种说“数据库当前正在使用中”另一种说“数据库处于 RESTORING 状态只能执行 RESTORE 操作”。这两个报错对应的原因其实不一样。“当前正在使用”是会话问题。RESTORING 状态的库也允许某些会话连着比如某个监控脚本刚好连在这个库上或者还原作业的后台任务还挂着。只要还有会话持有了这个库的引用DROP就会被拒绝。“只能执行 RESTORE 操作”是状态机限制。SQL Server 的数据库状态机设计得比较保守RESTORING 状态下系统只允许你继续执行恢复流程相关动作不允许直接跳过状态做物理删除。这就像你正在安装一个软件安装程序没跑完之前系统不允许你直接卸载——必须先让安装流程结束或者强制终止安装才能删除。所以删 RESTORING 库的核心思路就两条一是把所有连接清掉二是让 SQL Server 允许你绕过 RESTORING 限制。1.3 用一条 SQL 定位卡住的库不管后面怎么做第一步永远是先看看实例上到底哪些库处于异常状态。在 master 库执行USE master; GO SELECT database_id, name, state_desc, recovery_model_desc, create_date FROM sys.databases ORDER BY state_desc, name;重点看state_desc列。如果某个库显示RESTORING而且它并不是你当前正在执行的还原任务那就是需要考虑删除的候选对象。这里有个细节查询前一定要确认当前连接上下文是 master不要停在目标库里执行查询。后续所有删库操作也应该在 master 上下文完成否则你自己的会话就把目标库占用了。2. 动手前四项检查权限、会话、数据、文件路径删库这事不可怕可怕的是在错误的前提下删库。我处理这类问题时动手之前固定会做四项检查。2.1 权限与环境准备删除数据库需要服务器级权限一般要求属于 sysadmin 固定服务器角色或者至少是被赋予了 DROP DATABASE 权限的账号。用SELECT IS_SRVROLEMEMBER(sysadmin)可以快速确认当前账号是不是 sysadminSELECT IS_SRVROLEMEMBER(sysadmin) AS is_sysadmin;返回 1 表示是 sysadmin0 表示不是。如果 0可以先找有权限的账号不要硬来。另外环境准备阶段我会把 SSMS 的“对象资源管理器”自动连接库的功能注意一下尽量只开一个查询窗口减少额外会话干扰。2.2 会话占用排查KILL 之前先看清楚删库前必查会话占用。用下面这条 SQL 可以列出所有连接到目标库的会话USE master; GO SELECT s.session_id, s.login_name, s.status, s.host_name, s.program_name, r.command, r.blocking_session_id FROM sys.dm_exec_sessions AS s LEFT JOIN sys.dm_exec_requests AS r ON s.session_id r.session_id WHERE s.database_id DB_ID(N目标库); GO如果查出来有会话再逐个判断能不能杀。如果只是自己开的查询窗口先关掉如果是系统后台任务就要谨慎。KILL 命令长这样KILL session_id;注意别把自己的会话杀了当前会话的 session_id 可以用SELECT SPID;查看。还有一种更隐蔽的占用某个会话当前连接在别的库但正在执行针对目标库的查询。只看sys.dm_exec_sessions.database_id会漏掉它。可以用sys.dm_exec_requests辅助判断SELECT r.session_id, s.login_name, r.command, r.status, DB_NAME(r.database_id) AS request_db FROM sys.dm_exec_requests AS r LEFT JOIN sys.dm_exec_sessions AS s ON r.session_id s.session_id WHERE r.database_id DB_ID(N目标库);如果确实有活动请求也要先处理。删库操作最忌讳的就是连自己都没排查清楚直接 KILL 一片结果把正在跑的业务进程干掉了。2.3 数据安全评估这个库真的不要了吗RESTORING 状态的库有两种来源一种是正在还原、可能马上要恢复好另一种是还原失败、卡死了。动手删之前必须回答三个问题这个库是测试库还是生产库库里的数据是否已经有备份如果删除后需要重建备份文件是否可用如果库是通过RESTORE ... WITH NORECOVERY还原到一半的副本且原始备份文件还在那其实不一定非删不可可以先把还原流程走完后面再说。但如果这个库已经没有保留价值比如还原演练留下的垃圾库、损坏不可用的库、被反复重建的旧库那才可以进入删除阶段。我个人习惯是在删库前先对目标库做一次文件系统层面的备份或者确保已有独立备份文件可恢复再继续。这个成本很低但能避免误操作时彻底无法挽回。2.4 记录文件路径为手工清理做准备DROP DATABASE 成功之后正常情况下 SQL Server 会尝试删除物理文件。但 RESTORING 状态库的物理文件可能处于异常状态删除后偶尔会有残留文件所以提前记录文件路径很有必要SELECT database_id, file_id, name AS logical_name, physical_name FROM sys.master_files WHERE database_id DB_ID(N目标库); GO把physical_name记下来后面如果发现文件没被清理干净可以手动定位删除不用满硬盘找。3. 首选方案让数据库正常走完恢复流程再删除不是所有 RESTORING 库都只能走暴力路径。很多情况下这个库其实是有救的让它恢复正常状态再删才是最优解。3.1 什么情况下可以走正常恢复典型的场景是之前执行过RESTORE DATABASE ... WITH NORECOVERY准备继续还原日志备份结果后续日志没跟上库就一直停在 RESTORING。这种情况下数据库的原始备份和日志链都在只要补一个WITH RECOVERY就能让库回到 ONLINE 状态然后再正常 DROP。判断依据很简单这个库是不是你主动用 NORECOVERY 还原出来的中间态如果是优先恢复如果不是再看是不是还原中断导致的。3.2 用 RESTORE DATABASE ... WITH RECOVERY 收尾对于上述场景直接在 master 下执行RESTORE DATABASE [目标库] WITH RECOVERY; GO这条命令会让 SQL Server 完成当前恢复序列的收尾把库从 RESTORING 状态带回 ONLINE。待库在线后再按常规方式删除USE master; GO ALTER DATABASE [目标库] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE [目标库]; GO先用 SINGLE_USER 是为了确保之后删除时没有其他会话进来占用WITH ROLLBACK IMMEDIATE 会立刻断开所有连接并回滚未完成事务。同时删除过程也会干净很多。如果手头有备份文件也可以用完整 RESTORE 命令重新覆盖还原并加 RECOVERYRESTORE DATABASE [目标库] FROM DISK ND:\Backup\目标库.bak WITH REPLACE, RECOVERY; GOREPLACE会强制覆盖现有库RECOVERY则让还原完成后库直接在线。这条适合那种“已经不想保留当前状态用备份文件直接覆盖重建”的情况。3.3 为什么不先 ALTER DATABASE SET ONLINE / SINGLE_USER很多人卡在这里既然要删为什么不能直接执行ALTER DATABASE [目标库] SET SINGLE_USER WITH ROLLBACK IMMEDIATE然后再 DROP实际操作中你会发现RESTORING 状态下执行SET SINGLE_USER、SET ONLINE、SET OFFLINE大多会报错提示当前状态只允许 RESTORE 操作。原因还是状态机限制——RESTORING 是一个由 RESTORE 流程主导的状态DBA 的常规状态修改命令在它面前作用范围很小。所以正确顺序是要么先用RESTORE ... WITH RECOVERY把它推进到 ONLINE要么走下一章的 EMERGENCY 强制方案。不要在一个注定报错的命令上反复试验。4. 强制删除方案EMERGENCY 状态下的完整链路当库没法正常恢复时就需要强制方案了。我用的最多的手段是把库先踢到 EMERGENCY 状态再删除。4.1 SET EMERGENCY 为什么能绕开限制EMERGENCY 是 SQL Server 提供的特殊维护状态最初的设计目的是让 DBA 能进入一个“虽然有问题但可只读访问”的库去排查日志、看看能抢救多少数据。它的一个额外作用就是可以把 RESTORING 状态切换成一种 DBA 可控的状态。为什么 EMERGENCY 能绕开限制因为它本质上是在告诉 SQL Server这个库已经是异常库了我需要以维护者身份接管。在 EMERGENCY 状态下系统对库的状态约束会被放宽后续DROP DATABASE就能把库从实例中移除。需要注意EMERGENCY 是只读的。进入该状态后你不能让库回到正常工作也不能执行正常写入操作它就是一个“临时的尸体处理台”。4.2 强制删除的标准操作序列下面这套命令我在处理 RESTORING 卡死库时用了很多次是优先级最高的强制删除方案。所有命令都在 master 上下文执行。第一步把库踢进 EMERGENCY 状态。USE master; GO ALTER DATABASE [目标库] SET EMERGENCY; GO执行后可以确认一下状态SELECT name, state_desc FROM sys.databases WHERE name N目标库;如果显示EMERGENCY说明成功。少数版本或特殊情况下这条命令会不生效那多半是有会话占用先执行下一步杀掉会话再重试。第二步杀掉残留会话确保没有连接占着。可以使用上一章提到的会话排查 SQL把所有连接到目标库的会话 KILL 掉。为了效率也可以直接用 SINGLE_USER 强制断开ALTER DATABASE [目标库] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO这一步在 EMERGENCY 状态下通常能成功。它会立刻断开所有非自身会话并把库标记为单用户模式。注意执行完这句话后当前连接就占用着库了后续 DROP 还是要在另一个 master 窗口执行或者接受这个会话本身是 master 连接。第三步删除数据库。DROP DATABASE [目标库]; GO如果一切正常DROP 会执行成功实例元数据会清掉这个库。我遇到过 EMERGENCY 之后 DROP 依然失败的情况十有八九是第一步之前还有活动事务或者目标库有文件离线。解决办法重新执行 SET EMERGENCY再 KILL 所有会话再 SET SINGLE_USER再 DROP。顺序不能乱。4.3 兜底暴力法文件移除法高风险还有一种极少用、但确实存在的兜底方法直接处理物理文件。当 EMERGENCY 都设置不了或者库的文件损坏到连元数据状态都无法修改时可以考虑把物理文件移走/重命名强制让库进入 SUSPECT 状态再通过 EMERGENCY 删除。这个操作风险极高相当于告诉 SQL Server这个库的文件没了你把它标记为可疑吧。之后你再进 EMERGENCY 模式把库从实例中剥离开。完整步骤如下停止 SQL Server 服务或者先离线该库如果连离线都做不到就要停服务。把目标库的.mdf、.ldf文件移动到其他目录或者重命名。启动 SQL Server 服务。此时实例会发现文件缺失把库标记为 SUSPECT 或 RECOVERY_PENDING。执行ALTER DATABASE [目标库] SET EMERGENCY;执行DROP DATABASE [目标库];这个方法我只建议在完全确认不需要该库、且物理文件已经明确备份的情况下使用。移动文件前一定要检查是否有其他库共享了这些文件路径最稳妥的办法是先在sys.master_files里把所有引用这个路径的库都查一遍确保没有共文件依赖。4.4 删除后的文件与注册信息清理不管是正常删除还是 EMERGENCY 删除DROP 成功之后都要检查物理文件是否被自动清理。检查方法很简单打开 Windows 资源管理器进入之前记录的物理文件目录看还有没有对应的.mdf/.ldf。如果文件还在先确认是不是文件句柄被其他进程占用比如杀毒软件、备份软件等。确认没有进程占用后可以手动删除。如果文件被某个进程占用导致删不掉可以先把 SQL Server 的相关服务状态确认一下再考虑在维护窗口重启实例释放句柄。注意重启实例影响面大必须在确认目标库已从实例中消失后再做。5. 一次删库失败的完整排查演示光讲命令不够我完整走一遍排查过程从构造场景到最终删除给大家参考。5.1 在测试环境构造一个 RESTORING 库为了安全我建议所有读者先在测试环境复现一遍不要直接在生产环境试。构造方法很简单准备一个正常库备份它再用 NORECOVERY 还原出一个副本。USE master; GO -- 第一步备份一个正常库 BACKUP DATABASE [正常库] TO DISK NC:\Temp\正常库.bak WITH INIT, COMPRESSION; GO -- 第二步用 NORECOVERY 还原到新库让副本停在 RESTORING 状态 RESTORE DATABASE [目标库_副本] FROM DISK NC:\Temp\正常库.bak WITH MOVE N正常库 TO NC:\Temp\目标库_副本.mdf, MOVE N正常库_log TO NC:\Temp\目标库_副本_log.ldf, NORECOVERY; GO执行完第二步目标库_副本就已经处于 RESTORING 状态了。这时候你就可以在这个副本上放心练习各种删除方法。5.2 走一遍从报错到删除的完整命令现在开始复现“删除失败”的全过程。先在 master 窗口执行USE master; GO DROP DATABASE [目标库_副本]; GO大概率会看到报错。先别急按排查链路来。第一步查会话SELECT s.session_id, s.login_name, s.status, s.host_name, r.command FROM sys.dm_exec_sessions AS s LEFT JOIN sys.dm_exec_requests AS r ON s.session_id r.session_id WHERE s.database_id DB_ID(N目标库_副本);第二步查状态SELECT name, state_desc FROM sys.databases WHERE name N目标库_副本;确认是RESTORING后执行强制方案USE master; GO ALTER DATABASE [目标库_副本] SET EMERGENCY; GO ALTER DATABASE [目标库_副本] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE [目标库_副本]; GO执行完再看sys.databases副本应该已经不存在了。再检查C:\Temp下的物理文件正常情况下.mdf和.ldf也会被清理。5.3 两个容易忽略的坑第一个坑执行SET EMERGENCY后忘记确认状态就直接SET SINGLE_USER。如果 EMERGENCY 没切换成功后面两步都可能失败。稳妥做法是每执行一步都查一下state_desc确认状态确实变化了再走下一步。第二个坑当前查询窗口停在了目标库上下文里。很多人会直接在 SSMS 的数据库下拉框选中目标库再执行 DROP结果报“数据库正在使用中”。其实这就是你自己的会话占着库。排查时始终把命令窗口保持在 masterSQL 脚本里也统一写USE master;能少踩很多坑。6. 让 RESTORING 垃圾库不再出现的四个习惯删得掉是一种能力但更重要的是别让这种库频繁出现。下面这些习惯是我在实际运维里慢慢养成的对减少 RESTORING 垃圾库非常有用。6.1 还原脚本必须显式收尾写还原脚本时最后一步一定要明确状态如果是正式还原末尾加WITH RECOVERY如果还需要继续还原日志明确写WITH NORECOVERY并在后续步骤中保证会补上 RECOVERY。我见过不少卡住的库就是脚本写到一半被人手动停掉或者还原计划任务没配后续步骤结果库就一直挂在 RESTORING。一个规范的还原作业最后应该有一段“状态校验”逻辑IF EXISTS ( SELECT 1 FROM sys.databases WHERE name N目标库 AND state_desc NRESTORING ) BEGIN RAISERROR(目标库仍处于 RESTORING 状态提醒检查还原流程, 16, 1); END把这类检查写进自动化作业能第一时间发现问题。6.2 巡检作业盯住非 ONLINE 状态定期巡检非常有必要。SQL Agent 作业里加一个步骤每天检查实例上有哪些库不是 ONLINE 状态然后输出告警或写日志SELECT name, state_desc FROM sys.databases WHERE state_desc NONLINE ORDER BY name;如果实例上库很多这条结果集平时应该为空一旦有输出就说明有库异常可以立刻介入处理。这套逻辑很朴素但能避免垃圾库在角落里卡上几周没人发现。6.3 AlwaysOn 参与者的删除顺序如果目标库是 AlwaysOn 可用性组中的数据库直接 DROP 大概率会失败因为系统不允许删除还参与可用性组的库。正确顺序是先在辅助副本上把库从可用性组中摘除再执行删除或者直接在主副本上移除数据库并同步到辅助副本。在辅助副本上可以用ALTER DATABASE [目标库] SET HADR OFF; GO之后再执行 DROP。这个操作同样需要先确认没有其他会话占用生产环境更要提前评估影响。6.4 对存疑库先留一个备份文件再动刀最后一条习惯也是最实用的一条凡是遇到状态异常、不确定原因、暂时不用的库动刀之前先想办法留一个备份文件。即使备份文件是用COPY_ONLY方式打的也总比什么都没有强。因为删除是个不可逆动作一旦执行凭经验和复盘都救不回来。我现在处理这类库的顺序已经固化下来先查状态再查会话再评估是否有备份然后选择“正常恢复”或“EMERGENCY 强制删除”最后确认文件清理完毕。这套流程用下来还没遇到过删不掉的 RESTORING 库。如果哪天你又遇到一个显示“正在恢复”的库不要慌先在 master 里跑一遍SELECT name, state_desc FROM sys.databases;然后按上面这套链路一步步走大概率十分钟内就能搞定。