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
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.
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:
The SQL Server will create a new log file for your library database, recover it and make it operational.
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.
This can be done by following code:
sp_configure 'allow_updates', '1' RECONFIGURE WITH OVERRIDE
|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
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.