How to restore a database using mdf and ldf files in Sql 2008

Posted on 2014-08-19
Last Modified: 2014-08-24

Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent the disk to a data recovery place and looks like they can recover  some of the mdf and ldf files. We are particularly interested in 1 particular database. My questions are:

 1. Is this possible to restore a particular  database using corresponding mdf and ldf files?
 2. Do we need to have any mdfs from the  system databases? Any other files we need to ask data recovery company t look for?
3. Steps and/or or best practices to restore that database into a new machine.

Thanks for your help in advance.
Question by:Byas_Saha
    LVL 75

    Accepted Solution

    You can attach the database files to another instance if you have the mdf' and ldfs ( even with mdf you will be able to attach )
    System database master contains the  user info, and msdb contains the jobs and other info;  if you couldn't recover these files, you need to add them manually

    ssms -> rightclick database -> attach  ; choose the files and attach
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    you may have to run a  'DBCC CHECKDB()' statement against the newly attached database
    LVL 34

    Assisted Solution

    by:Brian Crowe
    You can copy the .mdf and .ldf files to a different server and just Attach them.

    You will lose any server-wide logins created at the instance level but you will retain your database users.
    LVL 68

    Assisted Solution

    1.  Yes.  If there are multiple mdf's, it's easiest to restore them all, but you should eventually be able to do partial restores as well.  If a db has only .ldf file, which is normal, then you can restore the db even if you don't have the .ldf file (assuming the .mdf(s) are undamaged).

    2.  If you can recover the master db, that will make full recovery easier, but it's not absolutely required to restore the user dbs.

    3.  Use a "CREATE DATABASE ... FOR ATTACH" statement for each db (attach is technically obsolete, you should use create for attach).

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now