Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    12

    Unanswered: How to attach MDF without LDF

    Hi all

    I have a difficult time to attach MDF database with out the log file LDF. Is there a way to attach without LDF?

    I am using 2003 server

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can cope with some lost data (due to losing the log file), you could use sp_attach_single_file_db. Note: You need to think carefully about this, there is no majik bullet that will recover any data lost in the log, it will be gone.

    -PatP

  3. #3
    Join Date
    Apr 2008
    Posts
    12
    I try it and i got this error

    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'd_dsdata'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'E:\Data\d_dsdata_syn_Log.LDF' may be incorrect.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    does that drive path exist on the machine?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2008
    Posts
    12
    Quote Originally Posted by Thrasymachus
    does that drive path exist on the machine?

    The path is correct... i copy and paste it work fine

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I think there is your problem. post your code. I believe with this procedure, you do not specify a log file.

    I could be wrong. I have not used this in a while.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2008
    Posts
    12
    I use this
    EXEC sp_attach_single_file_db @dbname = 'db_name',
    @physname = 'E:\full_path\rel_ddata_syn.MDF'

    I try follow

    http://www.sqlteam.com/forums/topic....c_id=78376#top

    i got stuck at #7 where i cant set the alt db to emergency

    how do i set it to alter database db_name set emergency

    Thanks

  8. #8
    Join Date
    Apr 2008
    Posts
    12

    alter db

    to set the db emergency...i use the folllowing code and get an error

    ALTER DATABASE db_name set EMERGENCY
    what i did wrong?

    Thanks advance

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There doesn't happen to be a log file on the disk, does there? Or at least a file named the same thing? When the new log file is created, I do not believe SQL Server will attempt to overwrite a file.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    also, make sure you're doing it from a query, not through a GUI.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2008
    Posts
    12

    work for me

    I do this and it work

    I created another DB with the same name, after that I put the new DB to suspect mode and do DB restore..its work like a charm

    However the log file back almost normal size

  12. #12
    Join Date
    Apr 2009
    Posts
    1
    If you are using SQL 2005, the solution here won't work. Check this out and it helps me to resolve the problem:

    Blog Rat: How to attach MDF without LDF into SQL 2005

Posting Permissions

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