"Error: 9004" for SQL Server

A customer's field acquisition computer crashes and cannot be started. After booting with PE, the MDF and LDF data are copied out, and the following error is reported when attaching to the normal database:

An error occurred while processing the log for database ' DatabaseName '. If possible, restore from a backup. If you do not have a backup available, you may need to regenerate the log. Unable to open new database 'databasename'. CREATE DATABASE abort. (Microsoft SQL Server, error: 9004)

After finding the data, all the words with " update sysdatabases  set status=-32768  where dbid=DB_ID( ‘test‘ ) " are invalid and will be reported "do not allow ad hoc updates to the system catalog." ", SQL server2005 is not allowed to update the system directory later.

The following methods are valid by my test:

steps to resolve:
1. Use the default method to establish a database (such as Database_forrestore) to use for recovery.
2. Stop the DB instance service.
3. Database_forrestore.ldf Delete the log file for the database you just generated, overwriting the database data file database_forrestore_data.mdf that you just generated with the database MDF file you want to recover.
4. Start the database server. You will see that the database Database_forrestore is inaccessible.
5. ALTER DATABASE database_forrestore SET EMERGENCY
6. ALTER DATABASE database_forrestore SET single_user "This step is to execute the following DBCC"
7. ALTER DATABASE database_forrestore REBUILD LOG on (name=dbname_log, Filename= ' d:\data\database_forrestore_ Log.ldf ')
after execution, there will be a warning message "Warning: The Log for database ' Database_forrestore ' has been regenerated." The consistency of the transaction has been lost. The RESTORE chain is broken and the server no longer has the context of the previous log files, so you need to know what they are. You should run DBCC CHECKDB to verify physical consistency. The database has been placed in dbo-only mode. When you are ready to make the database available, you need to reset the database options and remove any extra log files. "
8. DBCC CHECKDB (<dbname>, repair_allow_data_loss) "This step, I reported here 655 error, version incompatible, let me upgrade the database, my SQL Server servers are 2012, with sp_ Dbcmptlevel view Database_forrestore Its compatibility level is 110, also 2012, there should be no compatibility issues, but this step is not to execute, you can skip the "
9. ALTER DATABASE database_forrestore SET multi_user "Recover multi-user mode"
ALTER Database Database_forrestore SET online, which can be executed in the right-click menu on the database .

11. Refresh, and the data should be back.

There is also an English version to refer to

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

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

Microsoft suggest there is 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'll is in suspect mode which means so far everything going as it should be.
• Next is to put the database in emergency mode. ALTER DATABASE <DatabaseName> SET EMERGENCY The database status to EMERGENCY.
• A database which is in emergency mode can being repaired with allowing some data loss. DBCC CHECKDB Repair_allow_data_loss would do the task.
• In this process some of the data is lost but database would in working condition with most of the data retrieved from L og.

2) Rebuild the log.

• Create database using CREATE database for Attach_rebuild_log.
• This could fix the problem right away if database which are attempted to be attach are shut down properly. If database is corrupt it won't be restored correctly.
• Next DBCC CHECKDB Repair_allow_data_loss should be-ran to bring back the database operational. This could loose some data but it could bring back database in working condition.
• In this option as log was rebuilt SQL Server does not use log file to retrieve any data. The This process is none of the log operation like transaction rollback would 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 found on book Online for further guidance.

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