Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Columbus
    Posts
    4

    Unanswered: SQL Server database restore

    Hi, One of my developers had accidentally wiped out the data and when I tried to restore the database, it is complaining of the location of the file etc.
    Can we do a complete restore from .MDF and .LDF files? Please let us know.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: SQL Server database restore

    Usually, restores are done from .BAK (complete backup) and .TRN (transaction log backup) files. These are the default file extensions for SQL maintenance plans.

    Usually, you can't restore from the .MDF and .LDF files. The exception would be if you did an sp_detach_db, backed up the resulting files and then re-attached the files again.

    Regards,

    hmscott

  3. #3
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    There is a way around it I have used before - although no-one will support it!

    Make sure you have a safe copy of your mdf and ldf.
    Create a new database of the same name, using the same mdf and ldf file names.
    Shut down SQL Server
    Replace the new mdf and ldfs with the files from step 1
    Start SQL server

    Should work - on your own head be it though.
    Regards
    Dbabren

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Off course sp_attach_db will do the same job - and its much simpler from Enterprise Manager!

    (But if that doesn't work - then my prev post should!)
    Regards
    Dbabren

  5. #5
    Join Date
    Dec 2003
    Location
    Columbus
    Posts
    4

    Re: SQL Server database restore

    Originally posted by hmscott
    Usually, restores are done from .BAK (complete backup) and .TRN (transaction log backup) files. These are the default file extensions for SQL maintenance plans.

    Usually, you can't restore from the .MDF and .LDF files. The exception would be if you did an sp_detach_db, backed up the resulting files and then re-attached the files again.

    Regards,

    hmscott
    Do you mean, detach the database first, and reattach using the files I have, right? The current .MDF and .LDF are of no use to me.

    I will try it out this morning and post the results. Thanks

  6. #6
    Join Date
    Dec 2003
    Location
    Columbus
    Posts
    4
    Originally posted by dbabren
    Off course sp_attach_db will do the same job - and its much simpler from Enterprise Manager!

    (But if that doesn't work - then my prev post should!)
    I am going to try it out and post the results. Thanks,

  7. #7
    Join Date
    Dec 2003
    Location
    Columbus
    Posts
    4

    Re: SQL Server database restore

    Originally posted by hmscott
    Usually, restores are done from .BAK (complete backup) and .TRN (transaction log backup) files. These are the default file extensions for SQL maintenance plans.

    Usually, you can't restore from the .MDF and .LDF files. The exception would be if you did an sp_detach_db, backed up the resulting files and then re-attached the files again.

    Regards,

    hmscott
    Thank you, we have detached the database and tried sp_attach with .MDF (we did not have .LDF) and we were able to get the database in Read-only mode with the data. We have then exported the data to the original database and that took care of the problem. Thanks for all your help.

Posting Permissions

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