SQL Server的“错误：9004”

客户的一台现场采集电脑崩溃，无法启动。用PE引导后，将MDF和LDF数据复制出来，往正常的数据库附加时，报如下错误：

处理数据库 ‘databasename‘ 的日志时出错。如果可能，请从备份还原。如果没有可用备份，可能需要重新生成日志。无法打开新数据库 ‘databasename‘。CREATE DATABASE 中止。 (Microsoft SQL Server，错误: 9004)

查找资料以后，凡是带有“update sysdatabases set status=-32768 where dbid=DB_ID(‘test‘)”这个语句的，一律无效，会报“不允许对系统目录进行即席更新。”，sql server2005以后不允许更新系统目录了。

以下方法经我测试有效：

解决步骤：
1．使用默认方式建立一个供恢复使用的数据库(如database_forrestore)。
2．停止数据库实例服务。
3．将刚才生成的数据库的日志文件database_forrestore.ldf删除，用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件database_forrestore_data.mdf。
4．启动数据库服务器。此时会看到数据库database_forrestore不可访问。
5. ALTER DATABASE database_forrestore SET EMERGENCY
6. ALTER DATABASE database_forrestore SET SINGLE_USER【这一步是为了执行下面的dbcc】
7. ALTER DATABASE database_forrestore REBUILD LOG ON (NAME=dbname_log, FILENAME=‘d:\data\database_forrestore_log.ldf‘)
    执行后，会有警告信息“警告: 数据库 ‘database_forrestore‘ 的日志已重新生成。已失去事务的一致性。RESTORE 链已断开，服务器不再有以前的日志文件的上下文，因此您需要了解它们的内容。应运行 DBCC CHECKDB 验证物理一致性。数据库已置于 dbo-only 模式。在准备使数据库可用时，需要重置数据库选项，并删除所有多余的日志文件。”
8. DBCC CHECKDB(<dbname>, REPAIR_ALLOW_DATA_LOSS) 【这一步时，我这里报655错误，版本不兼容，让我升级数据库，我的sqlserver服务器是2012,用sp_dbcmptlevel查看database_forrestore 其兼容级别是110，也是2012，应该不存在兼容问题，但是这一步就是执行不了，可以跳过】
9. ALTER DATABASE database_forrestore  SET MULTI_USER【恢复多用户模式】
10. ALTER DATABASE database_forrestore  SET ONLINE 【联机，可以在数据库上的右键菜单中执行】

11.刷新，至此，数据应该回来了

这里还有个英文版可以参考

ERROR : 9004 An error occurred while processing the log for database.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.

If you receive above error it means you are in great trouble. This error occurs when database is attempted to attach and it does not get attached. I have solved this error using following methods. Hope this will help anybody who is facing the same error.

Microsoft suggest there are two solution to this problem.

1) Restore from a backup.

• Create Empty Database with same name and physical files (.ldf and .mdf).
• Shut down SQL Server.
• Replace the files which you want to attach with this new empty database files.
• Start SQL Server.
• Database will be in suspect mode which means so far everything going as it should be.
• Next is to put database in emergency mode. ALTER DATABASE <DatabaseName> SET EMERGENCY will change the database status to emergency.
• A database which is in emergency mode can be repaired with allowing some data loss. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS will do the task.
• In this process some of the data may be lost but database will in working condition with most of the data retrieved from log.

2) Rebuild the log.

• Create database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.
• This may fix the problem right away if database which is attempted to be attach is shut down properly. If database is corrupt it will be not restored correctly.
• Next DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be ran to bring back the database operational. This may loose some data but it may bring back database in working condition.
• In this option as log is rebuilt SQL Server does not use log file to retrieve any data. In this process none of the log operation like transaction rollback will work.

I prefer to use the first method if it works. First method has worked sevral time without any issue. Syntax of the CREATE database can be found on Book Online for further guidance.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server的“错误：9004”,古老的榕树,5-wow.com