A Complete Guide for SQL Database Recovery

Overview

During the evidence collection process, investigators often encounter situations where SQL Server records have been deleted. To avoid interruptions during the investigation, it’s essential to use SQL database recovery software to recover the deleted records. This article will share the method for SQL database recovery with a professional database forensics tool DBF.

3 Deletion Methods in SQL Server

There are generally two files in SQL Server , one with the extension “mdf”, which is a data file, the other with the extension “ldf”, which is a log file. Normally three main ways can delete records in SQL Server:

1. DELETE FROM table_name WHERE condition ——Delete content without deleting definitions and without freeing space. The deletion method is to delete one by one, and a log of each deletion operation will be recorded.

2. DROP TABLE table_name ——Delete the entire table ( including the table structure definition), which is the fastest and most thorough way. No specific logs are saved.

3. TRUNCATETABLE table_name——Delete the entire table contents without saving a specific deletion log( delete only the data and keep the table structure), which truncates the data and frees up space.

3 Recovery Methods for SQL Server

There are usually 3  SQL database recovery methods after the records of SQL Server database are deleted.

1. By obtaining the operation recordsin the log(addition, deletion, modification , etc.), convert the log records into SQL statements and write back the database records.

2. Rolling back through event logs which has the following two prerequisites:

1 ) At least one full backup of the database which was accidentally deleted previously.

2 ) The Recovery mode of database is “Full”.

3. Use professional software for SQL database recovery – DBF Database Forensic Analysis System.

Log recovery for database needs to meet specific log mode settings and find a suitable version of software to recover through specific operation steps. The data recovery is time-sensitive and needs to be done in time. Recovering database data through the log method requires very complicated steps. It is recommended for those who are not familiar with databases to use DBF Database Forensic Analysis System to perform recovery&analysis operations.

Case Study

Take the following case as an example. In early 2021, a law enforcement agency raided a massage center, but the front desk instantly cleared the business data from SQL Server, making it impossible for law enforcement officers to view the cash register records. Data recovery is now required. For this case, let’s take a look at the method for SQL database recovery step by step.

1. Open DBF Database Forensic Analysis system, click “Create” to create a new case and then input the basic information.

2.Select the database file of SQL Server in “mdf” format.

3. View record recovery results for each table.

4.Export all recovered records to local computer in “CSV” format

Notes

1 . When fixing the database on site, the investigators need to pay attention to whether the SQL server is stored on the local computer. And there are following three steps to find the SQL server database.

1 ) Check the program list to see if the SQL server service is installed.

2 ) Since the SQL server files are not all saved in the default installation directory, the investigators can search the entire disk based on the extension and pay attention to the modification time of the SQL server file.

3 ) If it is not found, please check the registry or configuration file to check the remote connection address.

2.It is important to stop the SQL server service firstly and then fix the database file.

1 ) If the SQL server service is not stopped, the copy operation will report an error and the database file fixing cannot be completed.

2 ) If the SQL server service is not stopped and the database file is forcibly copied using a tool. It may cause the database incomplete and additional analysis cannot be performed.