Post reply

physicaly Deleted LOG file...want new databse to point to MDF file.

  • August 21, 2008 at 4:05 am

    #197218

    Dear All,

    I physicaly deleted LOG file of one database. I have the MDF file with me now. I want to create a database to point to that MDF file..how can I do this..? Please help...

    Thanks in advance..

    Santhu.

  • August 21, 2008 at 4:09 am

    #861102

    nairsanthu1977 (8/21/2008)

    Dear All,

    I physicaly deleted LOG file of one database. I have the MDF file with me now. I want to create a database to point to that MDF file..how can I do this..? Please help...

    Thanks in advance..

    Santhu.

    You can use sp_attach_single_file_db to attach only the mdf (it will generate an ldf). For more information see http://msdn.microsoft.com/en-us/library/ms174385.aspx

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 21, 2008 at 6:46 am

    #861189

    Provided the database was shut down cleanly, that is.

    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 21, 2008 at 7:03 am

    #861213

    GilaMonster (8/21/2008)

    Provided the database was shut down cleanly, that is.

    That is correct, thanks Gail. In case the database has not been shut down cleanly, you can still rescue most of the content of the backups via third party tools. If this is the case let us know.

    - Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 21, 2008 at 9:35 am

    #861373

    Even if it was not shut down cleanly, you can still recover your database without the log file. You may have some data loss and possibly some corruption. There was a recent post on the steps. If you cannot attach as previously suggested, post back here and I will try to find the article.

  • February 4, 2010 at 5:25 am

    #1113644

    Good day all.

    im having the same problem.

    I lost my drive due to some hardware fault.

    My log files were on the faulty drive.

    i do not have access to a backup now.

    i have the mdf file though,

    The ldf files are gone.

    Can i attach in sql 2005 to a single mdf file

    The DB was not detached clean,

    i tried the

    exec sp_attach_single_file_db 'XXX','H:\AAA\xxx.mdf'

    Msg 5173, Level 16, State 1, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    Log file XXX.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Msg 5173, Level 16, State 1, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    File activation failure. The physical file name "xxx.ldf" may be incorrect.

    Msg 1813, Level 16, State 2, Line 1

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

    i also cant rebuild the log as that feature is not in sql 2005,

    Please if someone had this problme , how did you get around, i need to attach my mdf , without ldf .

  • February 4, 2010 at 5:49 am

    #1113661

    Please post new questions in a new thread. Thank you

    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

  • February 5, 2010 at 4:51 am

    #1114326

    Hi,

    you just do the following steps.

    Attach the data base --> Right click on Data base and choose attach. before this we need to complete following

    1. Go to c:\Program files\Microsof Sqlserver\MSSQL.1\Data 2.place u r .mdf file

    3.Rename u r log file if exists in same pplace

    4.Then attach u r mdf file only.

    5.Select log file row in attch data base window

    6.click on bottom right corner Remove button.

    7.click on ok

    It goes smooth.

    Regards,

    Suresh

  • February 5, 2010 at 7:15 am

    #1114413

    suresh.maddali (2/5/2010)

    Attach the data base --> Right click on Data base and choose attach. before this we need to complete following

    1. Go to c:\Program files\Microsof Sqlserver\MSSQL.1\Data 2.place u r .mdf file

    3.Rename u r log file if exists in same pplace

    4.Then attach u r mdf file only.

    5.Select log file row in attch data base window

    6.click on bottom right corner Remove button.

    7.click on ok

    It goes smooth.

    That only works if the database was cleanly shut down before the log was deleted. If it was not, then the attach will fail.

    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

Viewing 9 posts - 1 through 8 (of 8 total)

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