The epidemic of viruses that encrypt files and then extort money from users has already become global. One of the first was the WannaCry virus, released in 2017. Its clones and modifications followed. What if your files got affected by this plague (Ransomware)? It is unlikely that you can decrypt the files, even after paying a ransom. The only option is to find a workaround and
- Restore data from backup,
- Extract files from emails
- Search for contacts and information in messengers.
Do software developers have a solution to the problem? For example, what can Microsoft offer? The answer is NOTHING.
The most effective option is to restore the SQL Server database from the last backup. However, what if the server could not be restored from the backup? What to do if the ransomware also encrypts the backup?
Ransomware Virus Bug
Viruses spread due to various bugs in programs, operating systems, and through social engineering. However, the viruses themselves are programs, and they have bugs and hidden vulnerabilities, too. What vulnerability or vulnerabilities do ransomware viruses have? The virus needs to encrypt the maximum number of files quickly. For small files, such as Excel, Word, PowerPoint, Project, AutoCAD, CorelDRAW, and other data files, the encryption process is swift. Hundreds and thousands of files are encrypted immediately after infection. However, what happens if a virus encounters a large file, for example, a FoxPro or Microsoft Access database? In this case, the virus usually encrypts the beginning of the file and moves to the next one in the folder or on the disk.
This nuance gives users a bit of hope. Microsoft SQL Servers store data in files with MDF and NDF extensions. The sizes of these files are impressive: gigabytes and terabytes. For their storage, large disk storages are used, usually based on RAID controllers. Each file starts with a small header, followed by large amounts of service information for quick access to the data, service information describing user data, and the like.
In most cases, the virus encrypts the bulk of the service data in the MDF and NDF files. Nevertheless, the actual pages containing data in the file remain unencrypted.
Indirect SQL Server Data Recovery
If there is data, then how can we read it? How can we put together all pages and data blocks if service information is no longer available due to a virus attack? Sophisticated tools are required to solve a non-trivial task.
Therefore, the next step is to search for a tool (utility, service, or another method) that can analyze data in MDF and NDF files; the recovery utility can do this for damaged Microsoft SQL Server databases. They solve similar problems under the same initial conditions when a part of the database file is missing or corrupted.
The most accessible and simple utility for these purposes is Recovery Toolbox for SQL Server ). Recovery Toolbox for SQL Server was developed over ten years ago and restores data from the very first versions of Microsoft SQL Server (6.5, 7.0, 2000) up to the most recent ones (2017, 2019).
How to Recover SQL Server Database after the Ransomware Virus Attack
Since Recovery Toolbox for SQL Server was developed for only one purpose (recovering damaged Microsoft SQL Server databases), it contains a bare minimum of settings. The utility is made in the form of a step-by-step assistant, and at each stage, the user performs simple actions:
- Select encrypted MDF file
- Choose a recovery method and location to save recovered data
- Select data to be saved
- Start data recovery and backup
At the initial stage, the utility reads and analyzes the encrypted SQL Server database over a long period; this is the most crucial stage in the program’s operation. The larger the size of the source file and the more complex the data, the longer the program runs at this stage. If there is a powerful server available, it can take up to one day for the program to go through the files with the sizes of 1-5 Tb.
All that was restored from the encrypted MDF file is presented to the user in the form of convenient tables and lists inside Recovery Toolbox for SQL Server. The user can browse through pages containing data, tables, user-defined functions, and procedures.
If the encrypted MDF file was successfully analyzed, then the lists and tables on the 2nd page of the program are not empty. Otherwise, there is nothing to view, and there is no need to pay in this case, since the DEMO version of Recovery Toolbox for SQL Server is free.
If the analysis of the encrypted file was successful, then you should choose how to save the data:
- As SQL scripts in many separate files
- Alternatively, export data to a new Microsoft SQL Server database
Attention: when restoring a SQL Server database after a ransomware virus attack, there is a chance of losing a part of the data; also, the integrity of the data may be compromised. As a result, Primary keys and Foreign keys will most likely not work; this is revealed at the stage of executing SQL scripts with Primary keys and/or Foreign keys after the data has already been imported into the database. Scripts with Primary keys and/or Foreign keys are not executed or are executed with errors.
The sequence of execution of SQL scripts when exporting data to a new database:
This sequence of scripts is presented in the Install.bat file, which Recovery Toolbox for SQL Server saves in the folder with all other SQL scripts. Just execute this file with the necessary parameters (Server name, Database Name, Username, and Password) in the command line. You may need to run this script several times to import the data into the database most fully.
How to Recover Microsoft SQL Server Database after a Ransomware Attack
If a ransomware virus encrypted your SQL Server database, then you can try to recover the data without payment. To do this, you must:
- Restore data from backup
- Extract data from .MDF/.NDF files using Recovery Toolbox for SQL Server as SQL scripts
- Import data from SQL scripts into a new SQL Server database
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Hello sir,
one of our clients database was hacked by this ransomware. MDF and LDF files were encrypted and asking for ransom money. Our client paid them money and they gave decryption tool. with that tool our client decrypted all files including mdf and ldf. Now problem is when we take backup from that database then we can’t restore it again. may be these two (mdf,ldf) files not decrypted properly. our client loosing ten days data due to this problem. We tried all the possible solution given by Experienced people. Any help can I get on this ?
Hi
I hope you have solved your problem. If may ask, did ransomware encrypted your transaction log files?
Hi,
All the files, including transaction logs were encrypted by ransomware. Do you think there is any way of decrypting those files with RecoveryToolbox? Or any other way?
Thanks.
Same thing here. RW attack. Client paid. Ran decoder, everything seemed to be unlocked, but the production db showed 3kb, even the BAK files, very small. Data appears gone. Absolute devastation to business, and I have no solution for them.
If Ransomware attack in primary server of logshipping db then will it be impact on secondary server how can we say to client to explain