How to Recover a SQL Server Database from an Unattached MDF File?

The Unattached SQL Server database means the database is removed from the instance of the SQL server. However, the database is intact within its data files and transaction log files. So, you can use the database files and transaction log files to recover a SQL Server database from an unattached MDF file. In this article, we will discuss the step-wise guidance to re-attach the SQL Server database files in SQL Server to recover the SQL Server database. If attaching fails due to corrupt MDF files, then you can use a professional SQL repair tool to recover a SQL Server database. 

How to attach MDF files in SQL Server?

You can attach MDF files using SSMS and via T-SQL commands. Here’s how to do so:

Steps to attach MDF files in SQL Server:

• In SQL Server Management Studio Object Explorer, first connect to an instance of SQL Server Database Engine.
• Click on expand that instance view in SSMS, right-click on the Databases, and click Attach.

• In the dialog box named Attach Databases, click Add to specify the database to be attached. 

• Next, click on the location where your database is stored in the Locate Database Files dialog window. Also, expand the directory tree to search and click on the .mdf file of the database. Click OK.

Alternatively, you can use Transact-SQL command to attach the SQL Server database:

• In SQL Server Management Studio, click New Query.
• In the Query Editor window, you can use the CREATE DATABASE statement with the FOR ATTACH Clause. Here’s how:
• First, copy and paste the below command:

CREATE DATABASE MyAdventureWorks
ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
   (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
FOR ATTACH;

• Replace the name of the database and file name with the required file name.

Restore your backup file:

If attaching the database fails due to missing MDF files then you can recover a SQL Server database from your backup file. First, check whether your backup file is complete and accessible using the RESTORE VERIFYONLY statement. Here is the syntax:

RESTORE VERIFYONLY  

FROM <backup_device> [ ,…n ]  

[ WITH    

 {  

   LOADHISTORY   

-- Restore Operation Option  

 | MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'    

          [ ,…n ]    

-- Backup Set Options  

 | FILE = { backup_set_file_number | @backup_set_file_number }    

 | PASSWORD = { password | @password_variable }    

-- Media Set Options  

 | MEDIANAME = { media_name | @media_name_variable }    

 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }  

-- Error Management Options  

 | { CHECKSUM | NO_CHECKSUM }    

 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }  

-- Monitoring Options  

 | STATS [ = percentage ]    

-- Tape Options  

 | { REWIND | NOREWIND }    

 | { UNLOAD | NOUNLOAD }    

 } [ ,…n ]  

]  

[;]  

<backup_device> ::=  

{    

   { logical_backup_device_name |  

      @logical_backup_device_name_var }    

   | { DISK | TAPE | URL } = { 'physical_backup_device_name' |    

       @physical_backup_device_var }    

}

If your backup file is ready to restore, then restore your SQL backup file using the below steps:

• Start your SSMS and then connect to the SQL Server instance.
• Click on option labeled “New Query”
• In the Query Editor dialog box, run the below command:

USE [master];
GO

BACKUP DATABASE [testing]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\testing.bak'
WITH NOFORMAT, NOINIT,
NAME = N'testing-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

GO

Method 3-Use a Professional SQL Repair tool

If you have no backup file or the database attaching fails due to corruption in database files, then you can use a professional SQL repair tool like Stellar Repair for MS SQL. This is a reliable and helpful tool that can resolve issues related to data files in the SQL database. It can repair the MDF /NDF files and help you attach the SQL server database to recover a SQL Server database. The tool has an intuitive UI that can speed up the repair process. Also, it helps you perform specific object recovery without the need to sort each object individually. The tool can recover, indexes, stored procedures, triggers, and tables without any data loss. It allows the recovered data to be saved into a new database file, live database, or CSV, XLS, or HTML. The tool supports files created in SQL 2022, 2019, and earlier versions.  

Conclusion

When you face scenarios like your database is corrupted or inaccessible, or data in your database is accidently deleted, or system failures etc. in SQL Server, you can easily recover your database from an unattached MDF file by following the above steps.  You can attach the database using the MDF file and transaction log. However, if your MDF file was damage or corrupted and your backup file is obsolete then you can use a professional SQL recovery tool. Stellar Repair for MS SQL is one such tool that you can use to recover a SQL Server database. The tool can recover all data, including procedures, tables, indexes, etc., from damaged SQL databases with complete integrity and precision. 

Share Article

Follow Us

Return to Previous Page