Microsoft SQL Server uses a Master Data File (MDF) to store its primary data files. Considered the backbone of SQL Server, it acts as an SQL database accommodated into a file. This MDF file comprises the schema and data that outline the core structure and contents of the database, accounting for its high importance.
Other than primary data files, SQL Server also includes Not Master Data File (NDF) and Log Data File (LDF). While NDF is a secondary data storage file that complements the MDF file, LDF records all the transactions/logs carried out on MDF files, like insertion, deletion, update, etc.
SQL database relies on these files for routine functioning. Any kind of problem in any of these files could halt the working of your databases and the software using them will stop working properly.
SQL Recovery – Challenges and Solutions
At times, several anomalies in the MDF and NDF files obstruct the functioning of the SQL Server, such as database corruption, virus intrusion, or a system failure. These factors necessitate carrying out SQL Server recovery from MDF and NDF files.
The best resolution would be to recover the database from a previously created backup. It will set you free from the cumbersome data recovery and restoration process that follows. Without any such recently taken backup, the method mentioned in this article could prove helpful.
You can perform SQL Server recovery from MDF and NDF files manually with the help of SQL Server Management Studio or T-SQL. Alternatively, there is also the option to restore the SQL Server database from MDF and NDF files by using a third-party SQL Recovery tool. We will discuss these ways right here.
Manual Process to Recover SQL Server From MDF and NDF Files
A recommended manual method to serve this task is to attach the MDF and NDF files. As a second option, you may also restore the files to a different server. Just proceed with these steps by seeking the help of SQL Server Management Studio (SSMS) or T-SQL commands.
One major check before SQL recovery is to find database corruption by using the DBCC CHECKDB command.
Use DBCC CHECKDB to detect corruption in database files before attempting recovery:
DBCC CHECKDB ('SQL_TEST_DB')
WITH NO_INFOMSGS,
ALL_ERRORMSGS;If the DBCC CHECKDB command does not give any error, carry on with the next steps.
Detach and Attach MDF Files Using SSMS
Please keep in mind that to attach a database, you first need to detach it from SQL Server, else you will keep getting errors while attaching the files. Let’s proceed with the steps.
Detach the database
- Launch SSMS and connect to the server with your credentials.
- On the left-hand side, under the Object Explorer section, right-click Databases.
- Select the database that you want to detach.
- Right-click the Database → Tasks → Detach (In this case, the database is SQL_TEST_DB)
- In the resulting Detach Database window, click OK.
- Alternatively, you may also use the below T-SQL command.
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQL_TEST_DB'
GO- As you can see, the selected database has been detached and is invisible in the Object Explorer.
Attach the database
The next move is to attach the database that you have just detached from SQL Server.
- Open SSMS → Object Explorer
- Right-click Databases → Attach
- In the Attach Databases dialog box, click Add to add to the MDF file you want to attach.
- Locate and select the database and then click OK.
- Notice the added MDF and NDF files of the attached database SQL_TEST_DB. The SQL Server shall select the MDF (or NDF) and LDF files automatically.
- Click OK.
Attach the database manually using T-SQL command
If SSMS is unable to do the task, you may follow the Transact-SQL command to attach MDF and NDF files.
CREATE DATABASE SQL_TEST_DB
ON (FILENAME = 'MDF location'),
(FILENAME = 'NDF file location')
FOR ATTACH;Mention the location of MDF and NDF files in the command. This will attach both files and restore the normal functionality of your database.
What if the DBCC CHECKDB command shows an error?
You may use MDF and LDF files for SQL recovery by using the Attach method as mentioned here. Nevertheless, if in your initial check through DBCC CHECKDB command, your database comes out corrupted, the Attach method won’t work anymore. An automated third-party SQL recovery tool such as Stellar Repair for MS SQL can resolve the issue in any such situation.
SQL Recovery with Stellar Repair for MS SQL
The SQL recovery tool is instrumental in recovering the database from several issues. For instance, you can use it to recover your SQL database from suspect mode, corruption, or hardware/server crash. It can also resolve the data loss issue arising due to the failure of the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command, resulting in data loss.
Features of Stellar Repair for MS SQL
Its prominent features include:
- Recovers triggers, indexes, tables, stored procedures, etc. with ease
- Recovers deleted or missing SQL database table records
- Extracts data from a corrupted backup file (.BAK)
Therefore, you can call it an ideal third-party tool to perform SQL server recovery from MDF and NDF files. It repairs corrupt MDF & NDF files and recovers all objects while ensuring complete safety and security of your data.
Different Editions
Depending on your suitability, you may buy any of its paid versions for enhanced protection of your database:
- Corporate: Ideal to repair MDF and NDF files, and recover all the components of the database.
- Technician: Going a step further, this edition of Stellar Repair for MS SQL can perform data extraction from a corrupted backup, apart from password reset and database repair.
- Toolkit: This edition is 5-in-1 software, which can do MS SQL repair, reset passwords, analyze logs, and convert database format and .BAK & Restore.
Apart from these, you may also download and install the free trial version of Stellar Repair for MS SQL, which will let you scan and preview the recoverable SQL server database objects.
The steps to use this software to perform SQL server recovery from MDF and NDF files are easy. Moreover, it will do the recovery task much quicker compared to the manual methods. To know more about the software or to buy it, contact the Stellar Info website today.
Conclusion
In several instances, you may run the need to recover your SQL Server database from MDF and NDF files. To perform this task, you can seek to use SQL Server Management Studio (SSMS) or Transact-SQL commands. Before proceeding with these tasks, it is imperative to check for a recently created healthy backup to restore the corrupted files.
These manual methods are time-consuming, especially in the case of large databases. They may also risk the corruption or loss of data. Moreover, when the DBCC CHECKDB command indicates corruption in your database, the manual techniques cannot do the recovery task.
You can opt for Stellar Repair for MS SQL to resolve these issues. It can perform SQL server recovery from MDF and NDF files at a quick speed, enabling you to resume working on your database swiftly. To buy any of the multiple editions of Stellar Repair for MS SQL, browse through the Stellar Info website today.