如何fix suspect mode in SQL Server

最新推荐文章于 2021-03-10 13:26:10 发布

转载最新推荐文章于 2021-03-10 13:26:10 发布 · 1.6k 阅读

SQL Server数据库专栏收录该内容

128 篇文章

订阅专栏

本文提供两种解决SQL Server数据库损坏的方法：一是通过设置紧急模式和单用户模式来运行DBCC CHECKDB并尝试修复数据；二是当无法通过常规方式修复且没有可用备份时，重建事务日志文件，并给出具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1,方法一

ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

2,方法二

When DBCC CHECKDB detects a corruption error, a dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory (Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG). The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. You could check it for more error information. If the error cannot be fixed and restoring from a backup is not possible, the last option is to rebuild your log file for the corrupted database. Here is the syntax:

ALTER database YOUR_DB REBUILD LOG on
(
NAME = 'YOUR_DB_log',
FILENAME = 'F:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\YOUR_DB_newLog
)

Note:
The Command is an undocumented and unsupported command that can be used in
emergency situations where the database is offline because of some catastrophic
problem with the transaction log. It is designed to destroy the entire contents of
the current transaction log, rebuild a new one, and allow the user to bring the
database back online. However, this may result in more data lost than if the last
known, good backup was restored.

If the log is rebuild successfully, then run the DBCC CheckDB command and create a backup for the database.

Last, we strongly recommend you perform backup and restore strategies in SQL Server. For more information, please refer to http://technet.microsoft.com/en-us/library/ms191239(SQL.90).aspx