English
Русский
Español
Français
Italiano
Deutsch
Português
Nederlands
中国的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.
- Rating
- (3)
- (1)
- (0)
- (0)
- (0)
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
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
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.
-
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:
- 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.
- 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.
- 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
- 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
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).
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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.
Please select or enter the file name to recover. Source file name (.mdf)
Please, choose the recovery mode and destination properties
Content of source MSSQL Database file. Press Start Recovery to begin recovery process
