SQL server 2005 administration (restoring mdf file without ldf)

  • Here is the situation:

    > C drive containing the SQL server 2005 is gone

    > D drive for data is available

    > E drive for logs is GONE

    > no backup of mdf nor the ldf file

    current dondition:

    > a new sql needs to be rebuilt

    > the MDF files need to be restored

    All the attach various commands are not the options because the DBs where shutdown ungracefully.

    So, here is the Q.

    How, to restore an MDF file on the new sql server without the original ldf file.



    I don't think there is such a command as DBCC REBUILD_LOG in 2005...just to make sure we are on the same page.


    John Esraelo

  • sp_attach_single_file_db @dbname= 'dbname' ,@physname= 'physical_name'

    but, make sure database got only one .mdf file or say datafile. This command doesnot work with multiple data files.

    SQL DBA.

  • Hi,

    thank you for the quick reply.

    I don't think this would work since:

    > new sql server and master or msdb have not clue about the mdf

    > the mdf was ungracefully shutdown without explicit "detach" information


    sp_attach_single_file_db @dbname = 'tool' , @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Tool.mdf'


    File activation failure. The physical file name "E:\Tool_log.ldf" may be incorrect.

    The log cannot be rebuilt because the database was not cleanly shut down.

    Msg 1813, Level 16, State 2, Line 1

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


    John Esraelo

  • 1. What about restoring the old backup from tape or server will u tried this ? may be it v'l work !

    2. Dont u have a dr for the server which shutdown ungracefully.! if u have any Dr(disaster recovery ) server u can restore from that ihope so.


  • Option 1: Restore from backup.

    Option 2: Restore from backup.

    Option 3: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/ and then spend some time examining the DB for consistency errors (in data, not structure) and fixing them

    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 5 posts - 1 through 5 (of 5 total)

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