Attaching a database with error 9004

Question

Post reply

Attaching a database with error 9004

swc

SSC-Addicted

Points: 401
More actions
May 13, 2008 at 5:41 am

#186475

I am trying to restore a database on a DR server and getting an error 9004. The database became detached but i don't know how, but when i try to reattach it i get the error 9004.
What i was wondering is if i was to take the MDF and LDF files from the live server and put them onto the DR server would the database attach or would i still get the same error?
Cheers!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 1

9004 is a corrupted log file. See http://msdn.microsoft.com/en-us/library/aa337407.aspx

You may try...

move your .ldf file out to a temp directory.

IN Management Studio right-click> attach > add you .mdf file

Remove the log file from the lower database details window.

If you prefer TSQL:

CREATE DATABASE [dbname] ON

(FILENAME = N'G:\SQL_DATA\dbname.mdf' ) --where your mdf resides

FOR ATTACH

This should re-create a log file with output like this...

File activation failure. The physical file name "F:\SQL_LOGS\todd_log.LDF" may be incorrect.

New log file 'F:\SQL_LOGS\todd_log.LDF' was created.

You will not be able to access production database files while the database is online. So the database would have to be taken OFFLINE first (or SQL Service Stopped). Probably not what you want to do.

You could create a fresh backup of the production database while it is online and bring that backup file to your other server for restoration.

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

swc SSC-Addicted Points: 401 More actions · Answer 2

Will this work using Enterprise manager in SQL 2000? Instead of using SQL 2005.

Thanks for the reply BTW

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 3

Yup, that functionality is the same between 2000 and 2005.

You will receive a message in Enterprise manager... The filename specified is incorrect. A new log filie may be created. Do you want to continue?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

swc SSC-Addicted Points: 401 More actions · Answer 4

Hi guys,

Thanks for the replies. In the end I took the live database from the live server and put it onto the DR server which worked a treat. I have however tried the suggestion given and that was successful as well.

Cheers.