In an SQL Server database, a detached or unattached Master Database file refers to the primary data file that is not associated with an SQL Server instance anymore. In any such case, the MDF file becomes inactive and out of use. At any point, if you want it back, it is still portable. The system list does not contain the database and its details after its detachment.
However, the MDF file remains stored on your computer or server disk. This allows you to easily relocate, upgrade, or attach the database file. Through this blog, we will dive deeper into this topic and find out ways to recover a SQL Server database from a detached MDF file. Besides, we will also try to understand the role of professional SQL database recovery software in serving this task.
Prerequisites for Recovering a SQL Server Database from a Detached MDF file
The question may arise in your mind if it is even possible to recover a SQL Server database from an unattached MDF file? If yes, how? The answer is yes. It is possible to execute this job, and you can do it with the help of the ATTACH command. This will require you to use T-SQL to recreate the database from the raw MDF file. Alternatively, you can also attach the file directly in the built-in GUI of SQL Server Management Studio. Nevertheless, before moving ahead, focus on the following major prerequisites for successful job execution.
1. Avoid currently running SQL Server instances
When moving a database between two instances, make sure you have detached the database from any current SQL Server instance to avoid getting an error.
2. Have all the files in one place
You must have all the MDF, NDF, and LDF files, along with the filegroups for FILESTREAM data, before attaching the database.
3. Mention the correct file path
Specify the database path correctly if it is different from the path when you created it or attached it the last time.
4. Grant the necessary permissions
Ensure to grant the required permissions to the database engine service account, so that it can read the files in their new destination.
5. Ensure MDF and LDF files are in the same directory
If MDF and LDF files are in different directories and one of the paths includes \\?\GlobalRoot, when you attach a database, the operation will fail. To resolve this issue, you will have to move both folders to a standard directory and then go on with the attachment process.
6. Attach the database file only from trusted sources
Avoid restoring or attaching a database from any unknown or untrusted sources. This may infuse malicious code in the database, which could change the schema or structure of the physical database.
Database Recovery from Detached MDF File Using SSMS
As mentioned, it is possible to recover a database from a detached MDF file by attaching it. Here are the necessary steps for the database recovery process:
- Open SSMS and connect to your SQL Server instance.
- Right-click Databases > Select Attach
- In the resulting Attach Databases dialog box, select Add to mention the database you want to attach.
- Next, in the Locate Database Files dialog box, choose the location of the database file
- Expand the directory tree to select the specific MDF file. For example:
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDB_Data.mdf
This will attach the database, but make sure to select only a detached database, because choosing an already attached database will create an error.
Database Recovery from Detached MDF File Using T-SQL Commands
As an alternative method, you may use the T-SQL commands to attach a detached database. Here are the steps:
- Connect to your Database Engine
- From the top of the window, on the standard bar, click SQL Query Builder
- In the resulting window, write a command to create a database with For Attach clause, as shown below:
CREATE DATABASE TestDB
ON (FILENAME = ‘C:\MySQLServer\TestDB_Data.mdf’),
(FILENAME = ‘C:\MySQLServer\TestDB_Log.ldf’)
FOR ATTACH;
You may also include additional MDF or NDF data files and any filegroups for FILESTREAM data in the T-SQL command.
Besides, the stored procedures sp_attach_db or sp_attach_single_file_db can also help to attach the database. However, it is better to avoid these procedures, as Microsoft SQL Server will discontinue them in its future versions.
sp_attach_db syntax:
sp_attach_db
[ @dbname = ] N’dbname’
, [ { @filename1 … @filename16 } = ] { N’*filename1*’ … N’*filename16*’ }
[ ; ]
sp_attach_single_file_db syntax
sp_attach_single_file_db
[ @dbname = ] N’dbname’
, [ @physname = ] N’physname’
[ ; ]
Here, physname refers to the physical name of the database file, including its path.
Limitations of the manual recovery methods
The method can help to recover the database, but it does accompany certain restrictions, immaterial of whether you do it through T-SQL commands or the SSMS GUI.
1. No transaction log backup
The method to detach and attach the database is not a recommended one for backup and recovery. This is mainly because of the absence of transaction log backups or point-in-time recovery when you detach the files that you want to back up externally from SQL Server.
2. Security concerns
During the detach or attach process, the database engine impersonates the connection’s Windows account. This enables the account to access the database log files. However, the impersonation may fail for mixed security accounts that leverage SQL Server logins.
3. Log File (LDF) issues
SQL Server can rebuild a missing LDF file by using the option: FOR ATTACH_REBUILD_LOG . However, if during the file separation, there remain some pending transactions, it may lead to data loss. Moreover, databases with memory-optimized tables do not support rebuilding the log file.
4. Database corruption
The success of the recovery method will depend on the consistency and health of the MDF file. Attaching a corrupt or damaged file is likely to fail.
5. Requires technical knowledge
Using SSMS to attach the SQL Server database and recover its content is an easy task, but it will still require you to have a sound knowledge of the entire interface and technical aspects. When using T-SQL, you need to be even more knowledgeable. In case you do not have that much knowledge or time, you need to flip through the book or scroll down the PDFs to find the right way. It will take even more time and does not guarantee complete resolution.
Professional SQL Recovery Software for Database Recovery from Detached MDF File
To avoid hassles during the process, admins prefer using a third-party automated tool. It makes things easier, quicker, and efficient. You may use any renowned software to recover the database from MDF and NDF files, currently not attached to a live SQL Server instance. Just to cite an example, Stellar Repair for MS SQL would be a feasible option.
Conclusion
If a primary database (MDF) file is not attached to a SQL Server instance any longer, it becomes inactive and is of no use. Still, it remains stored in the server disk, making it easier to attach it again if you want to recover the database. This is possible with the help of the built-in SQL Server Management Studio (SSMS) GUI interface or by using T-SQL commands.
Before trying the resolution methods, several prerequisite measures are crucial to consider. For example, avoid moving the currently running SQL Server instance, store all the database files in one place, specify the right database path, provide the required permission to the database engine service account, and so on. While the SSMS helps to recover a SQL Server database from a detached database, using T-SQL can be comparably complicated due to the involved transaction queries.
Besides, both methods have several limitations, such as no backup for transaction log, security concerns during SQL Server login, database corruption, need for in-depth technological details, and more. To bypass such issues, you may seek the help of professional SQL recovery software. Stellar Repair for MS SQL can be a suitable tool in this regard.
