MSSQL 2016 服务器:所有数据库都标记为恢复挂起状态 [英] MSSQL 2016 Server: All databases marked Recovery Pending state

问题描述

今晚我遇到了这个问题，在执行 Windows(服务器 2016)更新后，我的生产服务器上的所有数据库都处于恢复挂起状态，更新我的 Redgate SQL 备份软件并重新启动服务器.查看日志，我发现所有数据库都无法访问，并出现拒绝访问"错误.所以我认为这与运行 MSSQLSERVER 服务的用户有关.因此，我明确地为该用户设置了数据和日志文件夹的权限并重新启动了 SQL Server 服务，一切又正常了.

Tonight I had run into this issue where all my databases on our production server were in a Recovery Pending state after doing a Windows (server 2016) Update, an update to my Redgate SQL Backup software and restarting the server. Looking at the logs I found that all of the databases were not accessible with an "Access denied" error. So I figured it was something to do with the user under which the MSSQLSERVER service was running. So I explicitly set permissions to the data and log folders for that user and restarted SQL Server service and all is fine again.

然而，这台服务器已经运行了很长时间——我还没有遇到过这种用户突然无法访问数据和日志文件夹的问题.某些东西必须删除对这些文件夹的权限.此外，现在当我查看这些文件夹的安全选项卡时，我看到我用来运行 MSSQLSERVER 的用户是明确定义的.但是在我们的临时服务器上，它具有完全相同的配置，我没有看到在那里明确定义的用户，但服务器运行得很好.

HOWEVER, this server has been running for a very long time - and I have not seen this kind of issue where suddenly the user doesn't have access to the data and log folders. SOMETHING had to have removed permissions on those folders. In addition, now when I look at the security tab of those folders, I see that the user I'm using to run the MSSQLSERVER is explicitly defined. But on our staging server, which has the exact same configuration, I DO NOT see the user explicitly defined there, yet the server is running along just fine.

SO - 我的问题.到底发生了什么?有任何想法吗?我是否应该担心现在在我的数据和日志文件夹的安全"选项卡中明确定义了运行我的 MSSQLSERVER 服务的用户帐户?

SO - To my question. What the heck happened? Any ideas? Should I be worried that the user account running my MSSQLSERVER service is now explicitly defined in the Security tab of my data and log folders?

推荐答案

使用 ALTER DATABASE ... REBUILD LOG ON.先设置成紧急，然后重建日志，然后设置在线和多用户.您将收到一条警告，指出数据库可能在事务上不一致(确实如此).

Use ALTER DATABASE ... REBUILD LOG ON. First set to Emergency, then rebuild the log, then set online and multi-user. You will get a warning that the database may not be transactionally consistent (and it is true).

Alter Database TestDB Set Emergency;
Go

Alter Database TestDB Rebuild Log On 
    (Name = N'TestDB_log',
    FileName = N'L:\Path\To\LogFile\TestDB_log.ldf')
Go

Alter Database TestDB Set Online, Multi_user;
Go

这篇关于MSSQL 2016 服务器:所有数据库都标记为恢复挂起状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！