Recovering the Database from an .MDF File

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Backup & Recovery

Recovering the Database from an .MDF File

In rare instances you might experience a situation where you don't have a valid SQL Server backup, but do have a .MDF file saved by Windows backup. The good news is that if the .MDF file is not damaged, you might be able to recover the database from it.

Suppose you've lost the pubs database and you have no backups, but you do have pubs.mdf which is safe and sound, saved to the backup directory. Here is how you should go about recovering your database:
  1. Create a database with the same name, using the following syntax (feel free to change the size and default file growth parameters): 
    USE master  
GO  
CREATE DATABASE pubs  ON   ( NAME = pubs_dat,     FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\pubs.mdf',     SIZE = 10,     MAXSIZE = 500,     FILEGROWTH = 10 )  LOG ON  ( NAME = pubs_log,     FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\pubs.ldf',     SIZE = 5MB,     MAXSIZE = 250MB,     FILEGROWTH = 10MB )
  2. Stop the SQL Server service.
     
  3. Copy the pubs.mdf file from the backup directory to e:\Program Files\Microsoft SQL Server\Data\.
     
  4. When asked whether you wish to overwrite the existing pubs.mdf file, choose "Yes".
     
  5. Delete pubs.ldf from e:\Program Files\Microsoft SQL Server\Data\ directory. SQL Server will recreate a log file at the next startup
     
  6. Start the SQL Server service.


At this point SQL Server will create a new log file for your pubs database, recover it and make it operational.

Although this method of recovering a database works, it is strongly recommended that you keep valid backups at all times and restore databases from backup files when needed.
Retrieved from "http://sqlserverpedia.com/wiki/Recovering_the_Database_from_an_.MDF_File"