Microsoft SQL Server repair tool

How to repair a damaged Microsoft SQL Server MDF file?

Recovery Toolbox for SQL Server

Recovery Toolbox for SQL Server helps to fix the corrupted MDF files of an MS SQL Server for all versions.

4.8 (6)

Rating: (5); (1); (0); (0); (0)

See all reviews Post review User’s stories

How to repair a Microsoft SQL Server database

How to repair a corrupted or suspended Microsoft SQL Server database. How to repair data from a damaged *.mdf file - step-by-step instructions:

Download the Recovery Toolbox for SQL Server here: https://recoverytoolbox.com/download/RecoveryToolboxForSQLServerInstall.exe
Install the Recovery Toolbox for SQL Server
Start the Recovery Toolbox for SQL Server
Select the corrupted *.mdf file
Preview the data that can be retrieved from the corrupted Microsoft SQL Server database
Choose the method of the data export
- Save as SQL scripts to disk
- Execute the SQL script directly on the database
Choose the information that should be repaired and saved
Press Start Recovery

Recovery Toolbox for SQL Server helps to repair corrupted MDF files of Microsoft SQL Server databases. The SQL MDF repair program can fix many errors, including:

The FILE SIZE property is incorrect. (Microsoft SQL Server, Error:5172)
SQL Server Detected A Logical Consistency-Based I/O Error: Incorrect Checksum. (Microsoft SQL Server, Error:824)
The Index Allocation Map (IAM) Page is Pointed to by the Next Pointer of an IAM Page.
I/O error (bad page ID) detected when reading at offset 0x###### in file FileName.mdf.
File Appears To Have Been Truncated By The Operating System.
During the Redoing Of A Logged Operation In Database 'DatabaseName', An Error Occurred At Log Record ID.

Features of MS SQL Server database repair tool:

Repair SQL Server database and *.MDF files of all versions of Microsoft SQL Servers: 7/2000/2005/2008/2008 R2/2012/2014/2016
Repair all objects of corrupted .mdf files: data types, table cell data, views, stored procedures, custom functions, triggers, indexes, primary and foreign keys, restrictions and other
Repair SQL database stored in several files (*.mdf + *.ndf files)
Export repaired data into Microsoft SQL Server databases directly
Save fixed data as SQL scripts
Preview of recovered data and structures
The SQL Repair tool successfully tested under Windows 98/Me/2000/XP/Vista/7/8/10 or Windows Server 2003/2008/2012/2016 and above
Multi-language interface included in the MDF repair tool
Recovers Your Data after a Ransomware Encryption Attack
MDF Viewer

Recovery Toolbox for SQL Server is an all-in-one MS SQL database repair solution for corrupted MDF/NDF files. Recovery Toolbox for SQL Server helps to repair mdf files and recover data from suspended databases

Requirements:

Windows 98/Me/2000/XP/Vista/7/8/10 or Windows Server 2003/2008/2012/2016 and above
Microsoft SQL Server: 7/2000/2005/2008/2008 R2/2012/2014/2016

How to import the saved SQL scripts into database?

Answer:

There are two ways to save data, supported by Recovery Toolbox for SQL Server:

You can save SQL scripts to hard drive, they will create database objects, tables, for example, and insert data to these tables.
You can also launch scripts in databases, defined by users, these scripts are written in SQL language.

Please note, that SQL scripts can be different, in spite of the fact, that they base on the same files of database. It happens because of syntax particularities both in queries, that are executed for direct server connection by means of ADO and in SQL queries, performed in the Query Analyzer environment that is supplied with MS SQL Server (the use of ":", the Go command, etc.). The first way is more reliable, what about the second way, it is more convenient.

Converting data to scripts and save it to disk

1. Saved data mapping
If you've chosen to save data on disk, Recovery Toolbox for SQL Server will create a subdirectory, that includes name of the source MDF file, this subdirectory is created in the user-defined directory and all scripts will be placed there. All scripts are named by rule, names consist of a word and a digit. Word indicates the script's role, digit indicates its number. There are many script types, for example:
- Types*.sql - scripts will create data types, defined by users.
- Tables*.sql - scripts will create tables.
- Indexes*.sql - scripts will create primary keys and indexes.
- ForeignKeys*.sql - scripts will create foreign keys.
- Procedure*.sql - scripts will create stored procedures.
- Function*.sql - scripts will create functions, defined by users.
- View*.sql - scripts will create views.
- Triggers*.sql - scripts will create triggers.
- Data*.sql - scripts will insert data into tables.
Script sequence number does not contain any useful data, it does not point at script execution sequence or any other information. These numbers are used only to split data and save it to a variety of small documents instead of one large file. Users can define the maximum size of file with SQL script. Moreover, users should take notice of the numbering of Data files. It should be noted, that each file of the Data type can contain data for one table only. Files with sequence numbers contain all data for each table.

Note: If numbers are missing for some files, it means that some tables do not contain any data.
2. Script execution sequence
In this figure, you can see the recommended order of script execution:

Script execution sequence depends on existing restrictions for existing data and structures of the table. Please pay particular attention to these factors:
1. Please specify corresponding primary key, if you'd like to create a secondary key. Owing to this fact, these operations are saved to different files, which should be launched in this sequence.
2. Before the creation of indexes and secondary keys, the program fills tables. It is obligatory, because referential integrity check is performed, when a table with defined secondary key is being filled. When tables with secondary keys are filled before tables with corresponding primary keys, there's an error. Moreover, this method features another particularity. If recovered data conflicts with the primary key, unique indexes, etc., in this case, the table will be filled with data anyway, but restrictions will not be created (index, primary key, etc.). If users require another behavior, they can select script execution sequence themselves.
3. Procedures, Functions, Views and Triggers object creation in a database has some difficulties, because these objects may have sophisticated and tricky dependencies. Procedures may refer to other Procedures, Views may refer to Functions, Triggers may refer to all objects. That is why, a program cannot help to solve the tangle. Users should find all dependencies manually and launch SQL scripts in the needed sequence. Another possibility consists in executing of all scripts in this group several times, until all error messages will disappear.
3. Install.bat file
Install.bat file is created to ensure automated execution for all scripts. This file executes all scripts, that were previously saved by the tool. Script execution sequence will be the same, as when applying to database. This batch file requires the isqlw utility to be installed, it is a part of MS SQL Server client part. Please specify Server name, Database name, Username and Password parameters for the file install.bat in the command line. For example: install.bat SQLServer SQLDatabase SQLUser SQLUserPassword
Attention:
1. Parameters should be separated with a space. If a parameter contains space itself, you should enclose it with double quotation marks.
2. Parameter sequence cannot be modified.
3. Please import data into NEW EMPTY database. Database must not contain any tables, indexes, stored procedures and so on before data import.
4. If install.bat file will be executed without parameters, you can see a brief guide, how to launch this file.
4. InstallTrusted.bat

If you want to log in to Microsoft SQL Server via your Windows account, then use InstallTrusted.bat file. InstallTrusted.bat file has two parameters: Server name, Database name.

Example of use: installtrusted.bat SQLServer SQLDatabase For details on logging in via Windows account see here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-2017

How to recover database from several source files?

Answer:

Repairing a database in MS SQL Server format from several source files

A database in MS SQL Server format contains one or several files. There are several types of files: main data file (with *.mdf default extension), secondary data file (with *.ndf default extension) and a transaction log file (with *.ldf default extension). If you'd like to repair data with Recovery Toolbox for SQL Server, you should specify the path to all secondary data files and the main data file. It can be made with Open File dialog box.

If corrupted database contains the main data file, a secondary data file(s) and a transaction log file(s), you should select the main data file of *.mdf format and all secondary data files of *.ndf format (see fig. 2).

A message about the lack of physical memory appears while the program is running.

Answer:

A lot of RAM is required to recover large MS SQL Server databases. The developers of the program recommend that you recover large databases on computers with enough free physical memory. The physical memory used by the program must not be less than the size of the file you are recovering divided by 500 plus 20-100 MB more for the program to run. That is, if you try to recover an .mdf file with the size of 100 GB, the computer must have at least (100 GB / 500) + 120 MB = 320 MB of free physical memory.

When I preview data in Recovery Toolbox for SQL Server, I see recovered procedures, views and other data, but after I execute the saved scripts, this data is not displayed in the Microsoft SQL Server database.

Answer:

To fix this problem, you should:

Save the data as scripts
Run the file install.bat with all the necessary parameters from the command line
See response files ( *????.sql.txt) to find out which scripts have not been executed successfully
Load scripts executed with errors into Query analyzer from Microsoft SQL Server and try to execute them there. After you get an error message, you should detect which command causes the error. If this command writes data that is of no value, you can simply remove it from the file. Otherwise you should send the script executed with errors and a screenshot from Query analyzer to the developers. Besides, it may later be necessary to send the source *.mdf file to the developers for them to study the problem.

When I recover data with Recovery Toolbox for SQL Server, excessive or incorrect values appear in Primary Key fields.

Answer:

If a value in a Primary Key field is incorrect (for example, it is negative or several dozen times greater than all other values), it means that the source data were seriously damaged and it is impossible to recover it completely.
If duplicate values appear in Primary Key fields, it means that the program has recovered sheets with data that should have been marked for deletion in the database (mdf, ndf files). But actually there is no mark that the sheet with data should be deleted. And therefore the program recovers data from all sheets. It results in excessive recovered data and the developers of Recovery Toolbox for SQL Server could not find criteria for filtering out excessive entries. In this case, database administrators and/or software developers make the final decision about excessive entries in the recovered database that should be deleted.

Recovery Toolbox for SQL Server needs the tool isqlw.exe in order to import saved scripts to the database. But I do not have it because I use SQL Express 2005 (SQL Express 2000). How do I import recovered data to the database?

Answer:

The tool isqlw.exe is a component of the MS SQL Server package. You can always download the latest version of MS SQL Server from the Microsoft site and install the necessary components on the computer. The demo version of MS SQL Server downloaded from the Microsoft site allows you to use the components within the period specified in the EULA.

If you can find the appropriate tools on Microsoft.com then you can download and install Query Analyzer from our website. The link for download is: https://recoverytoolbox.com/download/isqlwInstall.exe

This time I was able to save the results but ALL the tables were empty and I couldn't see any of the information.

Answer:

It is possible that something additionally happens with your MDF between tryout and registration (recovered by other software or something else). You can check it by running the demo version against this file on another computer.

There has been an error converting into datetime format while importing scripts into SQL SERVER 2000.

If you see the following error after running your scripts:

Server: Msg 242, Level 16, State 3, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server] The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated.

Answer:

It means that you should change the server language settings by doing the following:

Start Enterprise Manager.
Select the necessary server and connect to it.
Open its properties window by clicking the right mouse button on it and selecting Properties.
Open the Server Settings tab.
Select English in the Default Language for user dropdown list and press OK.
Stop SQL server. To do that, select Stop in the context menu and answer Yes to the question Are you sure you wish to stop SQL Server service. in the confirmation window.
Start SQL Server. To do that, select Start in the context menu.
Now you can run scripts saved on your hard drive by Recovery Toolbox for SQL Server. You can also run the program in the database scripts execution mode.
Now you need to restore the settings. To do that, open the properties window of the necessary server in Enterprise Manager.
Select the default language in the Default Language for user list and press OK.
Stop SQL server. To do that, select Stop in the context menu and answer Yes to the question Are you sure you wish to stop SQL Server service. in the confirmation window.
Start SQL Server. To do that, select Start in the context menu.

There has been an error converting into datetime format while importing scripts into SQL SERVER 2005.

If you see the following error after running your scripts:

Server: Msg 242, Level 16, State 3, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server] The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated.

Answer:

It means that you should change the server language settings by doing the following:

Start Management Studio.
Choose the necessary server.
Go to Security\Logins.
Choose the user that will be using the database and open the user's properties window.
Select English in the Default Language for user dropdown list and press OK.
Now you can run the script saved on your hard drive by Recovery Toolbox for SQL Server. Alternatively, you can start the program in the database scripts execution mode.
Now you need to restore the settings. To do that, open the properties window of the necessary user Management Studio.
Select the default language in the Default Language for user list and press OK.

After migrating the MSDB database on the new server via restoring, DTS Packages cannot be opened and executed. An error message appears: The system cannot find the file specified.

Answer:

Simply copying or restoring data tables from an MSDB database on a new server does not allow to restore DTS Packages because they have interior connections to the execution context of the server on which they were created and on which they were used on it prior to migration. To copy DTS Packages between servers, VB scripts must be employed. Therefore, the program Recovery Toolbox for SQL Server is not suitable for restoring DTS Packages.

What to use for importing data to MS SQL Server: sqlcmd or isqlw?

Answer:

Recovery Toolbox for SQL Server automatically detects the server version the database was used on. Therefore, if the database was used on MS SQL Server 2000, the program writes the isqlw tool to the saved scripts for importing into the new database. If the database was used on MS SQL Server 2005 or higher, the sqlcmd tool is used in the scripts. If you want to import data to a database on a server with a version different from the original one, you should use the tool for the corresponding server:

Isqlw - for MS SQL Server 2000
Sqlcmd - for MS SQL Server 2005 and higher

How to connect the MDF, LDF files to the server?

Answer:

Start SQL Server Management Studio
In Object Explorer right click on Databases
Click on Attach
Click Add button
Select the mdf file and click Ok button
Click Ok button
Wait for operation complete.

Can Recovery Toolbox for SQL Server recover data after an attack by a ransomware encryption?

Answer:

Recovery Toolbox for SQL Server can partially or fully recover information from .MDF/.NDF files of Microsoft SQL Server database files encrypted by ransomware viruses.

Recovery Toolbox for SQL Server does not decrypt data.

In general, data decryption is not applicable in these cases.

However, Recovery Toolbox for SQL Server can be useful in cases when the MDF file was partially encrypted. Typically, viruses only encrypt the header and/or beginning of files because of their large size. In these cases, if only the header or part of the MDF file was encrypted, Recovery Toolbox for SQL Server can be helpful. The program analyzes all the data blocks and collected data structure in the file. Then, it recreates the original data structure and the data itself, if possible.

Microsoft SQL Server MDF file structure

Is it Possible to Recover Data from an MDF File after an Attack by a Ransomware Virus?

There is no guarantee that you can recover 100% of the data or some part of it by using the program. To get a definite answer to that question, you need to download the DEMO version of Recovery Toolbox for SQL Server, specify the encrypted MDF file and wait for the analysis of the file to complete.

Upon completion of the analysis of the .mdf file in the DEMO version of the program, a full preview of the data that can be extracted from the file is available.

Note: Recovery Toolbox's customer support department informs that usually ransomware programs only encrypt the header of the database file because of its large size. As a result, it is often possible to recover almost the entire data structure and almost all information from the source file.