SQLTeam.com | Weblogs | Forums

Restoring DB from a .mdf and .ldf file

Hi all - is it possible to restore a Data Base in SQL Server 2005 by using .mdf /.ldf files. I don't have a backup of the database but do have backups of the .mdf and .ldf files that get captured every night going back several months - Question is - Can I just find the location of the current .mdf and .ldf files for this particular Database and replace them with say the .mdf and .ldf from 2 weeks ago ?? - Is it a simple copy and paste.

Yes, you can restore a db using mdf and ldf files. Be sure you restore a copy of the files and not the original files -- the recovery process changes the files, and it fails, you won't be able to use the same copy again.

It's safer to restore to a different db name. In theory you can just overlay the db, but it's best not to.

  1. copy the mdf / ldf backups to a folder -- keep these copies safe;
  2. copy those files to a different folder to to the actual restore;
  3. attach the db to a dummy name:
    EXEC sp_attach_db 'your_db_name_ATTACH', '<d:\full\path\to\mdf\file\backup\filename.mdf'

Try out the new db. If it works correctly, then you can DROP your original db and rename the attached db to the original db name.

1 Like

Hi Scott - Thanks for the reply - I am hoping it wont come to having to do a complete restore but fantastic to know I have the option (and files) if it comes to it - Thanking you

It will depend on how those files were backed up. What utility was used to create the backups of the files?

I would test that process to make sure the files are able to be attached - and would also recommend that you setup daily SQL backups, and transaction log backups.

1 Like

Hi Jeff - Thanks for the reply there is a Datto snapshot taken of the complete server every night so the .mdf and Ldf files which are located in a folder on the D drive would be captured as part of that - would those be good enough to use ?? - Also we have a backup taking place every night but the backup is then transferred to a Nasbox - this Nasbox is not backed up and it filled up a few nights ago and someone deleted the information on it to free up space

I am not familiar with Datto snapshots - but it seems they have to be setup correctly for SQL Server. I would verify with the group that manages that process to make sure.

Are your databases in full recovery model - or simple recovery model? If full - do you have transaction log backups configured?

Hi Jeff - sorry about the delay in coming back to you but it has been crazy - its in full recovery model and transaction log backups are enabled

So did you verify with the group that manages that process? If it is setup correctly then you can use the mdf/ldf files to restore - and if Datto is also performing log backups those should also be available for recovery.