[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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

Hello,

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.
0
Byas_Saha
Asked:
Byas_Saha
  • 2
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you may have to run a  'DBCC CHECKDB()' statement against the newly attached database
0
 
Brian CroweCommented:
You can copy the .mdf and .ldf files to a different server and just Attach them.

http://msdn.microsoft.com/en-us/library/ms190209.aspx

You will lose any server-wide logins created at the instance level but you will retain your database users.
0
 
Scott PletcherSenior DBACommented:
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).
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now