Restore SQL Database with .MDF File Only

Every new version of Microsoft SQL Server comes with interesting features and these features make it very easy and powerful. As a SQL server user I love to explore new things. I read about different types of database corruption and try to solve them. SQL Server database has a primary file called .MDF (Master Data File) file which contains schema and data. When corruption occurs then, experts suggest to restore the database from clean and updated backup and it should be the primary step that we can follow. In this post we will see the different ways to restore the SQL database with.MDF file only.

I had faced this problem when my friend sent me.MDF file and forgot to send .LDF file along with it. I used the first method to solve my problem.

First Method: Restore by GUI

It is very easy approach to attach .MDF to SQL Server. Simply follow these steps:

• Open SSMS.
• To attach the database, Right-Click on the database and select Attach option.
• In Attach Databases dialog, click on Add option and navigate to the location of your .MDF file.
• As we have only .MDF file then it will show the Not Found along with .LDF file.

• When you will click on the OK button then, It will raise an error:

• Now you can take the help of T-SQL to achieve this. This method will also work when more than 1 log file is missing.

        CREATE DATABASE [TestDB]ON
        (FILENAME = 'C:\123\TestDB.mdf')
        FOR ATTACH_REBUILD_LOG ;

Note: After successful completion, you will get the below message:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_1.ldf" may be incorrect.

New log file 'C:\123\TestDB_log.ldf' was created.

Second Method: This method is very useful when a single log file is missing:

EXEC sp_attach_single_file_db @dbname='TestDB',

@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL \MSSQL\DATA\ TestDB.mdf'

GO

Third Method:

CREATE DATABASE TestDBON

( FILENAME =N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\DATA\TestDB.mdf')

FOR ATTACH

GO

It is only to attach a single log file.

Conclusion: It is the very common problem for SQL Server users andI hope this post will help you to fix this issue. If you have any other option to fix this problem then share with me.