Want to attach LDF to the database in SQL 2000.

Question

Want to attach LDF to the database in SQL 2000.

sanjeev_krs2004

Default port

Points: 1462
More actions
August 18, 2009 at 10:37 pm

#203973

Hi All,
I am facing a problem where I want to attach the database in SQL 2000. I am using the following command:
sp_attach_db 'CoreDB','F:\CoreDB Data\CoreDB_data.mdf','F:\CoreDB Data\CoreDB_log.ldf'
But getting the following error message:
Server: Msg 9003, Level 20, State 1, Line 1
The LSN (31748:1923:1) passed to log scan in database 'CoreDB' is invalid.
Connection Broken
I can very well attach the MDF file though by modifying the above command to:
sp_attach_db 'CoreDB','F:\CoreDB Data\CoreDB_data.mdf'
But this way, I am not able to attach the LDF file now. Any help here would be highly appreciated.
Thanks,
Sanjeev.

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

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

Why do you still need the LDF file?

If the database you want to reattach was properly detached from SQL, the LDF should have no active entries. If the physical files are snapshots from a backup then I don't believe it is as simple as reattaching the files in SQL.

I have tried this before by creating a new database that matches my original MDF and LDF from a production box on a development box. Then stopped SQL services and replaced those MDF and LDF files with the ones I need. I honestly don't recall what the outcome of that was, it has been a while.

If there are transactions you need from the LDF file that is beyond my experience of T-SQL commands. But Google is the bets tool for that research (or BING :).

EDIT: Forgot to Spell Check...those that read this will be glad to know 'Google' is not in the dictionary for SSC 🙂

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It means that either the log or the database file has been damaged in some way. If you can attach with just the mdf, great, but please run a CheckDB afterwards.

It is easily possible for a database to be detached and not shutdown cleanly. Typically happens when the log is full. If that is the case, the attach will fail and a hack will be needed to get the DB back into the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 3

Hello All -

Thanks for your time on this. What I have done is that I attached the MDF file only and then rename the LDF file (need to stop the SQL services) which changes the database into suspect mode.

Sp_configure "allow updates", 1

go

Reconfigure with override

GO

Update sysdatabases set status = 32768 where name = 'CoreDB'

go

Sp_configure "allow updates", 0

go

Reconfigure with override

GO

After rebooting the server and it changes the database to emergency mode. And now you can create the new log file on the drive where you want:

DBCC REBUILD_LOG(CoreDB,'F:\CoreDB Data\CoreDB_log.ldf')

Can anybody tell me how to END this query.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why don 't you just use sp_attach_rebuild_log? No need for all the other work.

p.s. Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 5

sure,

I will keep it in mind next time.... I was not aware of this sp.