Learn How to Restore Database from .mdf File Only

Introduction

As we already know, every component in SQL server is saved in a particular file. If it is a transaction log information then it is saved in .ldf file and if it is not a transaction log information or if it is any other information related to database like table, Row or any other information which is stored in a particular manner in the database then it is saved in .mdf file of the database. If one of these files goes corrupt then the other components of the database also don’t remain useful anymore. In such cases, we have to use any of these files to make the database useful again. In this section we will learn how to get back database from an .mdf file

Why MDF File is Important?

The MDF file is the Master Database File of the database. It is a primary file of the database, because it stores data physically. The operations performed in MDF file can view easily. The MDF file is the primary file of the database. All the database objects are stored in the MDF file. It is not only the primary file of the database also it indicates to other files in the database.

Because MDF files are very important files of the database in case if a user loses his database due to unknown fault, there are some manual methods using which the database can be recovered. There are few manual methods available that I have mentioned below with which one can recover database from .mdf file.

Restoring the Database from .mdf File

First Method :

Sometimes users face situations in which they do not have a valid backup in SQL server, but the .mdf file is available, saved by Windows backup and the best part is that the available .mdf file is not corrupted and there is still scope of recovery.

Suppose you have lost your college’s library database and no backup is available with you have, except the .mdf file of that database. Now the recovery of your college’s library database is starting from here

Use the following syntax and create a database with the same name

USE master
GO
CREATE DATABASE  library  ON   ( NAME = library_dat,     FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\
library.mdf',    
 SIZE = 10,    
MAXSIZE = 500,     
FILEGROWTH = 10 
LOG ON  
(NAME = library_log,
FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\
library.ldf', 
SIZE = 5MB,
MAXSIZE = 250MB,  
FILEGROWTH = 10MB)
Go

Follow the below steps to restore database from MDF file:

  • Now Stop your SQL Server service.
  • Now from the backup directory (E:\Program Files\Microsoft SQL Server\Data\) Copy the library.mdf file
  • If you are asked to overwrite the existing library.mdf file, choose "Yes".
  • Delete library.ldf from E:\Program Files\Microsoft SQL Server\Data\ directory. The log file will be recreated by the SQL server at the next startup
  • Now again Start your SQL Server service.

The SQL Server will create a new log file for your library database, recover it and make it operational.

Second Method:

You can use this method if you don’t want to STOP and RESTART your SQL server service

WARNING: Be careful when attempting the following code. It could crash your Server.

  • Copy the .mdf file and then paste it to the SQL Server data directory.
  • Now, configure the server for the changes in the system tables.

This can be done by following code:

          sp_configure 'allow_updates', '1'
           RECONFIGURE WITH OVERRIDE
  • In the third step, you can add an entry to the sysdatabases system tables in the master database.

Consider the following columns in the below table:

COLUMN MEANING
Name Name of the database
Dbid Used for Internal recognition for every database
Sid Sid is a hexadecimal value that is used as a security id for the database
Mode It is used internally and always have a fixed value 0
Status It is additional info and is also in bit format
Reserved It is reserved for the future use and it Contains '1/1/1900' for all the databases excluding model.
Crdate It is the creation date of the database
Cmptlevel The compatibility level of the database
Filename The path where the file is saved

The snippet of sysdatabases table with a new entry for the new database:

INSERT master..sysdatabases (name, dbid, sid, mode, status, reserved, crdate, cmptlevel, filename)
VALUES ('northwind', 10, 0x01, 0, 24, 1090519040,
getdate(), '1/1/1900', 0, 80,’
d:Program FilesMicrosoft SQL ServerDatanorthwind.mdf')

Choose another identifier, if a dbid is already taken. You should check sysdatabases table before adding a new record to it.

Now, reset the Server to disallow updates of system tables:

sp_configure 'allow_updates', '0'
RECONFIGURE

Bottom Lines

The first method of database recovery is easy and there are fewer chances of a database crash taking place because there is a simple syntax, from which we can easily recover the database from an MDF file. But the second method is risky, because this method is performed when the SQL server is in a running state and in that case there are so many chances, in which a Server may crash, but both the methods are too lengthy as a result time consuming as well. So it is better to use SQL Recovery Program, which is easy to use and recover your database quickly as well from an MDF file.