Post reply

Attach MDF file with no LOG

  • February 7, 2006 at 11:22 am

    #189081

    Greetings,

    I'm trying to attach a MDF file with no LOG (log file has been deleted). Normally SQLServer creates a new LOG file. But this time I get this message:

    Server: Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'FCT_ENTIDADES'. CREATE DATABASE is aborted.

    Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FCT_ENTIDADES_Log.LDF' may be incorrect.

    There is no FCT_ENTIDADES_Log.LDF in the Data directory.

    Can someone give me a hand on this?

    Thanks

     

     

  • February 7, 2006 at 11:46 am

    #619360

    Hello,

    execute this query with the respective information

    EXEC sp_attach_single_file_db @dbname = '', --- your database name

       @physname = '' --- the location of the .mdf file

    Thanks and have a nice day!!!


    Lucky

  • February 7, 2006 at 11:52 am

    #619362

    Hi.

    Thank you for replying.

    That's precisely the script i'm executing...

     

  • February 7, 2006 at 11:59 am

    #619366

    Then you try to attach the same through Enterprise Manager.

    Right Click on databases -> all tasks -> attach database

    a new window will be opened. Give the location of the .mdf file

    In the next block, you can see the original file names and physical file names. here you uncheck the .ldf file and speacigy the database name to attach as and the database owner.

    Hope it will work now.


    Lucky

  • February 7, 2006 at 12:02 pm

    #619367

    Hello.

    I've this one indeed. But i obtain the same message in the dialog box.

    Thanks for your help.

  • February 15, 2006 at 11:16 am

    #621137

    When u attach the db with no log file, the device activation error occurs and sql server creates a new log file..

    After executing the attach statement, exec sp_helpdb 'dbname' should give you the locations of the files..

     

    -Krishnan

  • February 23, 2006 at 5:42 pm

    #623094

    If the files were not detached (using sp_detach_db) succesfully then you cannot not use sp_attach_db. The key here is detached "successfully".

  • March 30, 2006 at 7:34 am

    #629839

    Correct me if I am wrong, but if the database originally had more than one physical file for the log then attach command will not recreate the log file.  This only seems to work if you have only one physical file for your log.  At least that has been the case for me.

  • March 31, 2006 at 8:56 am

    #630160

    There is no easy way of doing it.

    I am sure that database was not cleanly shutdown so you won't be able to use only mdf file to recover the database (sp_attach_single_file_db)

    Do below steps.

    • Run the command for MDF file and get the logical and physical name of Log file.

    == I detached ran for pubs database files==

    DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 3)

    • Create a database with same physcial and logical file name.
    • Stop SQL Server.
    • Keep old mdf file on same location as of new database and rename log file.
    • Start SQL Server.
    • Database would come up in suspect mode.
    • Since Log File will not be linked to the new MDF File you have to run DBCC REBUILD_LOG

      dbcc rebuild_log('dbname','New Log File NAme')

    • THIS IS UNDOCUMENTED COMMAND.

    • database will come into Single User mode

    • Run dbcc checkdb and if there are problems then you need to restore from backup

     

    HTH

     

     

  • March 31, 2006 at 2:24 pm

    #630237

    This would have been good information to know two months ago. I love undocumented commands, especially when they have been documented.

  • April 2, 2006 at 6:44 pm

    #630307

    It's been quite while since I wrote this, but it may help:

    http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • April 2, 2006 at 7:33 pm

    #630309

    I don't think this procedure would have help in my situation. I had a database server crash (while doing backups) and the log was corrupted. Tried to do a detach and it detached but with an error so niether of the sp_attach_db procs would work. I would up setting the database to emergency mode and coying all the objects to a new database. However, all transactions for 10 hours before teh crash were gone. I would have expected all transactions to have been flushed to disk by then.

  • June 8, 2006 at 1:12 am

    #642438

    see here:

    http://www.se-pro.com/faq/sql/faq1.html

  • September 5, 2006 at 2:09 pm

    #658640

    If this post helped someone please post about it.

     

  • October 11, 2006 at 7:03 am

    #665198

    Make sure the .mdf files are not read only.

Viewing 15 posts - 1 through 15 (of 19 total)

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