It was a sunny day and relaxing DBA day. I was sitting at my desk listening Nirvana music. Everything around was fine when suddenly I saw my colleague Pamela crying.
Pamela was a DBA in charge of the marketing database and she told me that some records were deleted by mistake. Also, I was told that pieces of very important information were deleted and if I could not recover, she would be fired.
In this new article, we will show how to recover deleted rows in an MS SQL Server database. The database rows can be deleted accidentally by an authorized user or virus attack and malicious user interference.
The SQL Server DBAs should store backups of the data. However, if you do not have a backup, or if the backup is corrupt or out of date, there are other possible solutions for this situation.
The requirements for this article are the following:
- Any SQL Server version installed.
- The SQL Server Management Studio (SSMS) installed on the machine.
- Stellar Repair for MS SQL installer.
In this article, we will show how to retrieve data from a SQL Server database. We will use the Stellar Repair for MS SQL. This is a third-party SQL Server database recovery tool that can be purchased from this site:
This wonderful software repairs not only a corrupt MS SQL Database but also can be used to recover deleted data. In order to start open the SQL Server Management Studio (SSMS) and stop the SQL Server service:
Once the SQL Service gets stopped, you can recover the deleted rows using Stellar Repair for MS SQL.
The software can search MDF files. The files with MDF extensions are the primary data files used to store information of the database. The database files are 3 types- The primary data file that contains the data, the secondary data files that are optional and can be used to split the data of the primary file and finally the transaction log files that contain the transaction information.
Search MDF files
The software allows searching the MDF files using the Search in Folder option. Optionally you can search in subfolders:
By default, the primary and secondary data files are stored in the program files, Microsoft SQL Server folder. In the MSSQLXX.MSSQLServer folder and also MSSQL in the Data folder:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA
Where MSSQL14 is the version of SQL Server 2017. For a complete list of SQL Server versions, check this link related:
In order to recover the deleted records, in Stellar Repair for MS SQL, select the option Include deleted records. This option will recover deleted records using the mdf file:
Stellar Repair for MS SQL cannot only recover damaged databases but also recovers deleted records. The Repair button will repair the data and also include the deleted records. If your SQL Database records were deleted by a delete T-SQL sentence (the query language used in MS SQL), your data will be recovered. The truncated data (data deleted with the truncate sentence) cannot be recovered with this option. If you lost data using the truncated sentence, you may need to try with a backup or maybe a previous database snapshot.
In this article, we learned how to recover deleted records in an MS SQL Database. To recover the data, we stopped the MS SQL Server service and then we used the Stellar Repair for MS SQL. This is an expert recommended software used to repair damaged databases and/or recover deleted records.