Nowadays, desktop database software such as Microsoft SQL Server which provides multiple benefits as it keeps all records fast, flexible and secure for professional users. It is a database application designed to provide e-commerce applications, data mining, and large-scale transactions, etc. It is widely in use on an Organization platforms for data analysis, processing components and integrated data. The SQL server database consists of three files:
- Primary Database File (MDF file)
- Secondary Database File (NDF file)
- Log file (LDF file)
Among these three, one database file is Primary File which contains each information for database and point out to other files within database. A user data and object can be saved in this file and the recommended file extension is .mdf. Each database has separate primary data file. Another one data file is Log File. The file maintains a log of each the transactions done in SQL Server Database so that the relevant information can be later accessed to recover the SQL database. There must be at least a single log file for database. The file extension for transaction logs is .ldf. In the following section we will discuss the need and several methods which helps to restore SQL database from .mdf file.
Need to Restore SQL server Database from MDF file
Sometimes, a user doesn’t have valid SQL server backup but only MDF file is saved through Windows backup. On this situation, if .mdf file doesn’t become damaged or corrupted then, a user must be able to recover the database easily from it.
Various methods to Recover SQL database from MDF file
There are two methods to restore or repair sql server database from MDF file:
- First, click on Start button. Move cursor to All programs and then, choose Microsoft SQL Server for viewing the SQL server database program.
- Go to SQL Server Management Studio application then, open tab Connect to Server.
- Now, choose server on Web. Set the Authentication and click Connect tab.
Restore SQL Database along With Graphical Interface
The following steps (mentioned below) restore SQL database using mdf file with Graphical Interface:
- First, right click on Database and press Attach option.
- Now, click Add tab to view the locate Database files.
- Enter the full name of .mdf file. For example c:\data files\my_data.mdf. Click on OK. After this SQL Server Management Studio load the database from .mdf file.
Restore SQL Server Database File with Transact SQL script
The following steps are to restore SQL database from .mdf file by using Transact SQL script:
- Click on New Query from the Server Management Studio toolbar.
- Now, create the Database with same name by using the following code:
- Next, stop the SQL Server Database Service.
- Copy the mydata.mdf file from backup directory to respective path as e:\Program Files\Microsoft SQL Server\Data\.
- Choose Yes option While asking to overwrite the existing mydata.mdf file.
- Delete mydata.ldf file from path e:\Program Files\Microsoft SQL Server\Data\ directory. Again, SQL Server will recreate a log file.
- Now, start the SQL Server services.
Use master
GO
CREATE DATABASE mydata ON (NAME = mydata_dat, FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\mydata.mdf', SIZE = 10, MAXSIZE = 500, FILEGROWTH = 10) LOG ON (NAME = mydata_log, FILENAME = 'e:\Program Files\Microsoft SQL Server\Data\mydata.ldf', SIZE = 5MB, MAXSIZE = 250MB, FILEGROWTH = 10MB)
By using these two methods, a user able to recover and repair the damaged SQL Server database from MDF file.
Conclusion
Here we are ending with various methods to restore SQL server database from .mdf file, which provides several benefits for businesses as well as professional purpose. The best possible solutions and its needs are also discussed. Well, these two methods are recommended to restore SQL database when it is needed.
EmoticonEmoticon