如何解決Database處於Recovery Pending狀態

上篇文章裡, 我們通過指令碼修改了資料庫MDF和LDF檔案的位置, 恢複了之後, 探索資料庫處於Recovery Pending狀態.

 

如何解決呢?

 

嘗試一

=======================

運行下面的命令, 失敗.

RESTORE DATABASE SharePoint_Config WITH RECOVERY

 

報錯:

Msg 3148, Level 16, State 3, Line 1

This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

嘗試二

======================

運行下面的命令, 失敗.

DBCC checkdb ('SharePoint_Config')

 

報錯:

Msg 945, Level 14, State 2, Line 1

Database 'SharePoint_Config' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

 

這裡注意到"inaccessible files"字樣, 於是想起應該檢查一下存放MDF和LDF的路徑的使用權限設定是否與預設的檔案夾相同.

經過比較, 發現少了使用者"MSSQLSERVER"的full control.

添加NT Service\MSSQLSERVER使用者後, 賦予full control許可權, 問題依舊. 看起來不是許可權的問題.

 

筆者還嘗試了下面的命令, 均告失敗.

EXEC sp_resetstatus 'SharePoint_Config';ALTER DATABASE "SharePoint_Config" SET EMERGENCYDBCC checkdb('SharePoint_Config')ALTER DATABASE "SharePoint_Config" SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CheckDB ('SharePoint_Config', REPAIR_FAST)DBCC CheckDB ('SharePoint_Config', REPAIR_ALLOW_DATA_LOSS)

 

筆者最後將兩個資料庫刪掉, 重新Attach MDF和LDF檔案, 才將資料庫狀態恢複.

 

筆者不會放過這個問題, 明天找資料庫專家諮詢一下.

 

參考資料

=======================

Database States

http://msdn.microsoft.com/en-us/library/ms190442.aspx

DBCC CHECKDB

http://msdn.microsoft.com/en-us/library/aa258278%28v=sql.80%29.aspx

Suspect Database Recovery (SQL Server 2005)

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ea8b251f-fbce-4f8b-8a87-1b570688ce88/