How to restore Microsoft SQL Server 2022 database files (MDF, LDF, and BAK)
-
Author:
Vladimir Artiukh
-
Editor:
Oleg Afonin
In this article, we will examine how to recover Microsoft SQL Server 2022 database files. We will show what to do if a database or its files are accidentally deleted, as well as how to use an available backup to return the database to a working state.
- SQL Server features
- How to access the server disks
- How to recover a deleted SQL Server 2022 database
- Conclusion
- Frequently Asked Questions
- Comments
To recover the files, we will use Hetman RAID Recovery. The program can locate MDF, LDF, and BAK files on physical drives, RAID arrays, and Dynamic Disks, as well as work with sector-by-sector disk images. The recovered files can later be used for Restore or Attach by means of Microsoft SQL Server.
Hetman RAID Recovery supports Windows, Linux, and macOS.
How to Recover a Deleted SQL Server 2022 Database | MDF, LDF & BAK
SQL Server features
Which files Microsoft SQL Server uses and why they are important during recovery:
- MDF – the primary database file. It contains the database structure, tables, records, indexes, and other objects. This file is the main data source when you need to return a deleted database to SQL Server.
- LDF – the transaction log file. SQL Server records database changes in it. The log is used to maintain data consistency and is an important component when attaching a database from its physical files.
- BAK – the backup file. In our test, this is a full database backup created by SQL Server. If such a file is available and not damaged, it can be used for Restore without the need to attach the original MDF and LDF files.
Therefore, the recovery scenario depends on which files were preserved or found:
- If a BAK file is available, the database is restored from the backup.
- If there is no backup, but MDF and LDF files were recovered, they can be used for Attach, provided the files remain readable and consistent.
| Feature | Description |
|---|---|
| Relational data model | SQL Server stores information in tables related to one another through keys and constraints. |
| T-SQL query language | An extension of SQL used to create queries, manage data, write procedures, and perform administrative operations. |
| MDF and NDF database files | MDF is the primary database data file, and NDF is an additional data file. They contain tables, indexes, and other database objects. |
| LDF transaction log file | Stores records of transactions and changes in the database. The log is used for crash recovery and data consistency. |
| Backup and restore | SQL Server supports full, differential, and transaction log backups, which makes it possible to restore a database to a required point in time with the appropriate configuration. |
| Transactions and ACID | Ensure atomicity, consistency, isolation, and durability of data operations. |
| Indexes | Speed up searches and query execution, but can become damaged or require rebuilding after certain failures. |
| Integrity control mechanisms | Primary and foreign keys, constraints, and checks help maintain data correctness. |
| Recovery models | Simple, Full, and Bulk-logged recovery models determine transaction log backup capabilities and database recovery options. |
| Diagnostic tools | The DBCC CHECKDB command checks the logical and physical integrity of a database and helps identify corruption. |
For testing, we prepared Windows Server 2022 with Microsoft SQL Server 2022. The TestDB database is located on a Dynamic Spanned Volume with the NTFS file system. It contains tables, test customer records, and files stored in the VARBINARY(MAX) field. The MDF file is stored in the SQLData directory, the LDF log file is in SQLLogs, and the full TestDB_Full.bak backup is in the SQLBackup directory.
How to access the server disks
METHOD 1. Connecting drives to a workstation
If the SQL Server files are stored on a separate physical disk, it can be connected to a workstation via a SATA adapter, USB adapter, or a dock station.
If the database was stored on a Dynamic Spanned Volume or another multi-disk configuration, it is preferable to have access to all drives that were part of it.
If it is not possible to connect all disks at the same time, you can create sector-by-sector images of them. To do this in Hetman RAID Recovery, select the physical drive, launch Save disk, specify the save location, and wait for the copying process to complete.
After that, the disk can be disconnected and an image of the next drive can be created in the same way. Mount the ready images via Mount disk and use them for analysis together with the physical disks.
This approach also makes it possible to continue working with copies without placing additional load on the source drives.
METHOD 2. Configuring SSH access
There is another way to access the drives without physically removing them. If Windows Server 2022 is still running and is accessible over the network, the drives can be connected to Hetman RAID Recovery remotely via SSH.
To do this, configure OpenSSH Server on Windows Server in advance.
Start the OpenSSH SSH Server service – sshd – and, if necessary, set it to start automatically.
Also verify access through TCP port 22 and make sure that the account used for the connection has the necessary permissions to access the server drives.
After configuring the server, open Hetman RAID Recovery. In the top menu, go to Tools and select Connect via SSH.
In the Remote server field, enter the IP address of the Windows Server, and in the Port field, enter 22. Below, in the Credentials section, select the required Authentication method and enter the account username and password. If necessary, you can enable Save server details and Save authentication data so that these parameters do not need to be entered again. Then click Next.
After a successful connection to the server, the next window will display the available physical disks and logical arrays. Select the drives that belong to the required JBOD and continue.
After confirmation, they will appear in the main window of Hetman RAID Recovery, where they can be worked with in the same way as locally connected drives.
Pay attention to the network equipment. Since operations with the drives are performed over the network, for a stable and comfortable connection it is preferable that the workstation, Windows Server, and the local network support data transfer speeds of 1 Gbps and higher. This will help speed up drive analysis and transfer of recovered files.
This method is especially convenient if the server is still running and physically removing and connecting a large number of drives to another computer is undesirable or difficult.
How to recover a deleted SQL Server 2022 database
METHOD 1. Restoring a database from a BAK file
Let us move on to the first scenario. In this case, the full backup file TestDB_Full.bak is available after recovery and can be used to restore the database.
Open Microsoft SQL Server Management Studio and connect to the required SQL Server instance.
In the Object Explorer tree, open the context menu for Databases and select Restore Database.
As the source, select Device, specify the TestDB_Full.bak file, and verify that SQL Server has correctly identified the backup.
To avoid changing the original TestDB database, restore it under a separate name, TestDB_Restored.
In the restore settings, specify separate names and paths for the database physical files so that they do not conflict with the files of the original TestDB. For the new instance, use TestDB_Restored.mdf and TestDB_Restored_log.ldf.
Start Restore and wait for the operation to complete. After a successful restore, the TestDB_Restored database appears in Object Explorer.
Open it and verify the tables and records. Also make sure that the data stored in the VARBINARY(MAX) field is available. In this way, we verify not only the creation of the database, but also the availability of its contents after restoration from the BAK file.
METHOD 2. Restoring a deleted database
In the next case, the TestDB_Restored database was accidentally deleted and is no longer displayed in Microsoft SQL Server Management Studio. Its physical files TestDB_Restored.mdf and TestDB_Restored_log.ldf also became unavailable.
The Dynamic Spanned Volume itself remains accessible, so reconstructing the JBOD again is not necessary.
Open Hetman RAID Recovery, select the NTFS logical volume where the SQL Server files were located, and start a Fast scan.
After the analysis is complete, go to the directories where the MDF and LDF files were stored and check the results. Find the deleted TestDB_Restored.mdf and TestDB_Restored_log.ldf, and note their names, size, and location.
If Fast scan does not find the required files or the result is incomplete, return to the analysis mode selection and start Full scan. It performs a deeper analysis of NTFS and may find more deleted data.
After the required MDF and LDF files are found, add them to the recovery list and save them to another physical drive.
It is not recommended to recover the files back to the original Dynamic Spanned Volume, since new writes may overwrite other data that could still be recovered.
After recovering the physical files, return to Microsoft SQL Server Management Studio. In the context menu for Databases, select Attach.
Add the recovered TestDB_Restored.mdf and verify that SQL Server sees the associated TestDB_Restored_log.ldf log file.
If both files remain readable and consistent, perform Attach. After attachment, the database appears again in the SQL Server list.
Open the tables and verify the records. Additionally, check the availability of the VARBINARY(MAX) data used in the test database. If the structure, records, and stored files are available, the recovery can be considered successful.
Conclusion
We examined two main scenarios for working with Microsoft SQL Server 2022 database files:
- In the first case, the available BAK backup file was used to restore the database with Restore.
- In the second case, after the database was deleted, its physical MDF and LDF files were found and recovered using Hetman RAID Recovery, after which they were attached back to SQL Server via Attach.
Hetman RAID Recovery allows you to work with physical drives, sector-by-sector images, and remote disks over SSH, and also supports Windows, Linux, and macOS. This makes it possible to organize recovery even when SQL Server files are stored on multi-disk configurations or when server drives are inconvenient to remove and connect to another computer.
