SQL Server 2008 R2（可疑）模式——如何修復？

我有處於可疑模式的 SQL Server 2008 R2 數據庫。我試圖修復它執行這個查詢：

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

但修復輸出是這條消息：

Warning: You must recover this database prior to access.
Msg 8921, Level 16, State 1, Line 5
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Warning: The log for database 'ServeDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer 
has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has 
been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 8921, Level 16, State 1, Line 9
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

如何全面修復數據庫？我做的這個修復，只工作了幾天，然後數據庫再次進入可疑模式……

我遇到了類似的問題。但是，由於備份也很可疑，因此我無法消除對數據庫的懷疑。我使用 SSMS 中的導出功能將表和數據移動到新的空白數據庫。

然而，這並不會移動您所有的儲存過程、視圖、函式等。我有一份 RedGate 的 SQL 比較（我強烈推薦給任何定期使用 SQL 模式的人），它處理了所有的結構遷移。所以它使這個過程相當輕鬆。如果您需要使用 RedGate，他們的軟體有 14 天的試用期。

如果您只有表，或者少量的過程、視圖等，您可以使用 SSMS 的“生成腳本”功能為這些項目製作創建腳本，然後在您的新數據庫上執行它們。

RedGate 的 SQL 比較： http ://www.red-gate.com/products/sql-development/sql-compare/

引用自：https://dba.stackexchange.com/questions/11233