Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Taiwan
    Posts
    4

    Unanswered: Try to attach DB, but missing the log.ldf file

    I'm in a BIG BIG trouble...
    A co-worker tried to help me out with the huge database huge log file.
    So he first detach my db, and deleted the log.ldf file...
    now, i tried to attach it back....ERROR...
    could someone help me out ... please..

    !_! Crying Nyssa.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    what is the error you are getting
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Oct 2003
    Location
    Taiwan
    Posts
    4

    error msg

    the old db which i detached is "topdb", and i only have "topdb_Data.mdf" for now.
    The topdb_log.ldf was accidently deleted.
    so now, i create a new db named "topdb" again, and so it built both "topdb_Data.MDF" and "topdb_Log.MDF" (of course i renamed the old "topdb_Data.MDF" to another name first)
    then i stop the MS Server and deleted the topdb_data_MDF and topdb_Log.mdf which the new db created. and rename the old topdb_Data.mdf back. and restart the sql
    It's so far what i have done.
    And the db shows "suspect" for now...and no idea what to do next.. or maybe what i have done was wrong...
    totally...................tired....

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Which version of sql including service pack ? Also, what prompted you to detach the database ? When you attempted to attach the database back, what was the error message ?

  5. #5
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: error msg

    examples from bol:


    --will reset the status of Suspect db
    EXEC sp_resetstatus 'PUBS'
    go

    --will attach the db again
    EXEC sp_attach_db @dbname = N'pubs',
    @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
    @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
    go


    --Best Of Luck !

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: error msg

    should recreate an empty log on the fly

    detaching and retaching db's is a quick way to empty large transaction logs

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You will have to use sp_attach_single_file_db. Check it out in BOL, as I believe there may be some data loss involved, but that would mainly be in the transactions that were running at the time of the detach. Good luck.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This is the only thing I found on that subject

    I swear I even printed out a more precise procedure on how to deal with it, but, as usual, I can't find it now...was searching ever since I saw the post

  9. #9
    Join Date
    Oct 2003
    Location
    Taiwan
    Posts
    4

    Unhappy still

    aie...
    It keeps saying that my mdf file is wrong..
    Well, maybe there were something wrong when we're back up it.
    Thank you all ^_^

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •