如何修复SQL SERVER置疑数据库

如果自己搞不定可以找诗檀软件专业SQL SERVER数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：[email protected]

若用户没有任何有效的备份，则唯一可行的恢复措施是将数据库置入紧急模式EMERGENCY MODE。这样让用户可以访问数据库，但是需要注意的是所需要做的恢复并没有被完成，所以数据库中可以被读取的内容可能是行级别不一致的，也可能是结构不一致的。下面使用EMERGENCY MODE修复数据库。即先后将数据库置入EMERGENCY和SINGLE_USER模式。

ALTER DATABASE [数据库名] SET EMERGENCY;

GO

ALTER DATABASE [数据库名]   SET SINGLE_USER;

GO

DBCC CHECKDB(N’数据库名’,REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS,ALL_ERRORMSGS;

Msg 5172, Level 16, State 15, Line 1

The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.LDF’ is not a valid database file header. The PageAudit property is incorrect.

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.LDF” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

The Service Broker in database “DemoSuspect” will be disabled because the Service Broker GUID in the database (B72D1765-80C6-4C2F-8C12-5B78DAA2DA83) does not match the one in sys.databases (001AE95A-AE22-468F-93A4-C813F4A9112D).

Warning: The log for database ‘DemoSuspect’ 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.

可能出现如上报错。该操作会尝试首先做常规的ATTACH_REBUILD_LOG操作。当此操作失败，DBCC CHECKDB接手并尝试从被损坏的日志中尽可能做恢复操作，然后强制重建日志。后续会运行一个全库修复，检测全库中的损坏存在。

注意在上面的例子中因为SERVICE Broker GUID是有问题的。所以需要耍个花招新建一个同名的空数据库，该操作会在MASTER.SYS.DATABASES中创建对应的记录。

可以使用如下语句来重设GUID:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE