How to Resolve MS SQL server error 9004?

-->

Microsoft SQL server database offers stored procedures for detaching and attaching SQL server database that are sp_detach_db and sp_attach_sdb. These stored procedures help you to move a database or database file from one server or disk to another server or disk without copy the backup of database or database files. It helps in several conditions like out of disk space, server problem and many more.

Suppose you want to move a database from one server to another server by the using of detaching and attaching method then you have to follow three steps:

• Detach the SQL server database.
• Move the SQL database to your desired location on another server.
• Attach the detached SQL server database.

Sometimes, when a SQL server database user tries to attach database then get below error message:

An error occurred while processing the log for database 'student'. Could not open database student. Create database is aborted. (Microsoft Sql Server, error 9004).

How to Solve Error 9004: To do this, a database administrator will have to perform following procedures.

1. Create an empty SQL server database with same name & layout.
2. Now shutdown the services of SQL server.
3. Move the database file into newly created empty database file that you want to attach.
4. Start the SQL server database.
5. Probably, your database will go in suspect mode.
6. Now ALTER your database and set database in emergency mode.
7. Run DBCC CHECKDB command on your database with and repair clause, It will give you a repair clause and again run DBCC CHECKDB with repair_allow_data_loss. You will probably loose some data or records of your SQL server database.
8. If you database is very important and you can invest some amount for recover SQL database then you should try any third party SQL database repair software.

Note: It is totally depends upon you to try any third party software or not but I can suggest you to try avoid any third party software as much as possible.