Failure Attaching MSSQL 2000 Database

Question

Failure Attaching MSSQL 2000 Database

noahluke

SSC Journeyman

Points: 92
More actions
September 18, 2007 at 5:30 pm

#174814

Hi,
I'm having problem of attaching the mssql 2000 database.
My customer database was in suspect mode so they detached
the database with errors. Later they restarted the server
and the database was no longer can be seen in the Enterprise Manager.
They tried to attach the database using the EM but failed due to corrupted ldf file.
I gave them the below command to attach the mdf file only and it returned an error.
exec sp_attach_single_file_db @dbname= 'flxhrmsq',
@physname = 'd:\sqldatabase\flxhrmsq_data.mdf'
Server: Msg 3624, Level 20, State 1, Line 1

Location: recovery.c:2021
Expression: curr->GetOpCode () != LOP_NULL
SPID: 52
Process ID: 328
Connection Broken
They can't restore the old backup because all the while they backup the wrong database. I need to re-solve this ASAP.

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Try creating a DB of the same name, with empty files in the same places, etc...

Shut SQL down and replace the new files with the corrupted ones. Start SQL, which should put the DB in question in suspect mode.

Once started up - put the DB in Emergency mode

ALTER DATABASE <dbname> SET emergency

DBCC CheckDB('<dbname>', REPAIR_ALLOW_DATA_LOSS)

exec sp_resetstatus <dbname>

ALTER DATABASE SET ONLINE

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nido786 Default port Points: 1482 More actions · Answer 2

ok dear dont worry

just apply this techenique

right click on your corrupted LDF file and mar read only option

and then attached it again inshallah it resolved.

Regards

Syed Muhammad Naveed

Database Administrator

Naveed_shah15@hotmail.com

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

syed muhammad naveed (10/29/2007)
ok dear dont worry
just apply this techenique
right click on your corrupted LDF file and mar read only option
and then attached it again inshallah it resolved.
Regards
Syed Muhammad Naveed
Database Administrator
Naveed_shah15@hotmail.com

How does this help?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005