Post reply

Want to attach LDF to the database in SQL 2000.

  • 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.

  • August 18, 2009 at 10:51 pm

    #1041267

    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

  • August 19, 2009 at 12:11 am

    #1041278

    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

  • August 19, 2009 at 1:49 am

    #1041312

    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.

  • August 19, 2009 at 2:20 am

    #1041327

    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

  • August 19, 2009 at 2:52 am

    #1041340

    sure,

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

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

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