SQL Database Repair Tool

Problem

I have been giving solution to various forums and found that the Database in Suspect Mode is one of the common problem user or DBA faces.

The database becomes inaccessible to the user and user is not able to perform any operation to the SQL database. This is the worst situation any DBA could face and he need to bring that database in normal mode as soons as possible.

First of all Don’t Panic. We are here to help you in recovering database from Suspect Mode.

I will show you how to create SUSPECT database and then recover it from two different methods. Both process will be done in front of you practically. You can pick the best solution to repair your database from Suspect mode.

Solution

Finding the Root Cause?

If you want to find out the root cause of the issue, then check your SQL Server error logs. Error logs will give you the clear picture of the database and give you the exact reason why database get in to the Suspect mode.

Use Filter while viewing SQL log and use Database name as filter criteria. From this, you will be able to see the logs relating the particular database. To find the root cause.

Reasons?

Might be possible that your database goes in Suspect Mode due to accidental deletion of log file when SQL Server is offline, may be Exclusive lock placed on data or log file due to Antivirus, corruption in Transaction, corruption in data file or any Hardware/OS level failure. The reason could be many but these are the common reasons any SQL user faces while having Database in Suspect Mode.Now, you are aware of the reason and you need to take quick action to recover the database from Suspect mode.

Creating Suspected Database:

Step-1 I am going to create database Test_Db and going to make it Corrupt.

Step-2 Now ,shut down the SQL Server and Turn off the services.

Step-3 Open HexEditor and load Test_Db.mdf file to the editor.

Step-4 Make changes to the strings of Test_Db.mdf file and Save them

Step-5 Now Start the services of SQL Server and Open SQL Server

Step-6 When you Open the SQL Server and make Use of database Test_Db you will get the following error. You check the status of SQL Server by using DATABASEPROPERTYEX.

SQL Error 945 means the database get into the Suspect Mode.

Now the next step is to Detach the database. How would you do it?

Step-7Detach it by using sp_detach_db as executed on the below screenshot.

Step-8Set the database offline and Copy the database files as a backup. Now Drop the database .

Your database is successfully Detached from SQL Server.

Step-9Now,Reattach SUSPECT Database by sp_attach_db.

Run following command:

EXEC sp_attach_db @dbname = N'Test_Db',
    @filename1 = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Db.mdf',
    @filename2 = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Db_log.ldf';
GO

You will get SQL Error 5172. The error arises when header information of .mdf file gets corrupted.

Step-10Create a new log file by using ATTACH_REBUILD_LOG.

Now create a test dummy database and apply same process to this. Set the database Offline.

If incase you forgot to delete corrupted files then you will get the error shown above.

Now check the status of that database.You will get the database in Suspect Mode.

How to Repair Suspect Database

Well if you dont have backup then the only option you have is to set the database in Emergency Mode. Emergency mode help you to let in to the database. It does not means that your recovery is completed.

Put the database in Emergency Mode and Single_user mode

Try regular ATTACH_REBUILD_LOG and incase, if it get fails, then go for DBCC CHECKDB.

"Note: Choose REPAIR_ALLOW_DATA_LOSS as a last resort." 

Check the status and you will get the status Online.

What if it get Fails in Repairing SQL Server database?

Well I am quite happy that I am the lucky one, my database get repaired successfully in one shot. But!! its not pretty easy, you have to be very carefull. As you see the DBCC should be taken as a last resort, data might get lost while repairing database in Suspect Mode.

The best strategy you can opt to recover it quickly is Having good BACKUP STRATEGY! So that you can quickly restore from the last backup.

But what if you are in worst situation?

Having No Backup? Failed in recovering SUSPECT database?

Make Savvy Move!

SQL Repair tool will help you out. Just load your corrupted MDF file and attach to this tool. Want to know how? See here;

• Open the software and Click Open to load the inaccessible file.

• Select the corrupted file.

• It will get scanned successfully and you will be able to preview all the database objects without any data loss.

• Now click to Export to save your database in either Live SQL database or as sql compatible script.

Conclusion

Database in Suspect Mode is one of the scariest scenario any SQL user or DBA faces. Why? Because the database becomes inaccessible. So, here we have tried to give you the scenario by creating database in Suspect mode and repair it manually. We have tried to figure out the root cause, why database goes in Suspect state. We have also discussed various possibilities of getting SQL error 945. The blog also covers an automated approach to repair Database in Suspect Mode. We have tried to give you best possible solution by resolving it in real-time scenario.

Header Corruption in SQL Server? I have seen number of forums resulting the SQL Server error 5172. It is one of the most common query user faces in SQL databases. Error message looks like:

Well this error mainly result when your MDF File got corrupted. Other possibility of getting this error is when you try to attach MDF or LDF Database from the existing database to the new system. Or you are trying to attach the high version of database to lower version.

Here I am going to tell you the solution on how to fix SQL Server header error 5172.

What will I do?

Let us discuss what I am gonna do? I am going to create the database and make it corrupted resulting SQL Server header error 5172 and then repair it.

Fix SQL Server Header Error 5172 - Let get started!

• I created the SAMPLE2 database in SQL Server and insert data in it.
• I detached the mdf file in SQL Server using sp_detach.
• Now I made it corrupted by changing strings in the HexEditor so that we can get SQL server error 5172.
• Now I re-attached the file to the SQL Server and What I get is SQL Server header error 5172.

What Happens to the database and What it Indicates:

First of all, you can check this in SQL ERRORLOG also. It will give you the complete description of the database. The message indicates the serious corruption in the header which SQL Server is not able to understand. As a result, SQL will not be able to get started, hence the database becomes inacessible.

Possible Options You have?

Since your database is corrupted, you can try the two possible solution

• Run DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS command to recover your database from corrupted state. Since, REPAIR_ALLOW_DATA_LOSS is the minimum level of corruption and it does not guarentee the recovery of your database. It might result in data loss also.
• Other is Restoring the database from healthy state.

So we have discussed the two possibilities you can do while having SQL Server error 5172.

Fix SQL Server error 5172 Without any Data Loss

Let’s get started

I am going to show you the sure shot solution to fix SQL Server error 5172. I am using SQL Recovery tool to repair SQL Server error 5172.

• I am going to copy damaged .mdf file from the path and load it to SQL repair tool.
• Now Open the sofware, Click on Open to add the file from the desired locations

• Now Click on Advance Scan mode and Either choose the SQL version manually or Auto-Detect the version.

• You will get the preview of your recovered database items.

• Now Click on Export.

• You will get two different options for exporting the database, Either export in SQL Server database or in .csv script.

• I am going to save my database in SQL Server database. For saving you have to give details of your SQL Server like the Server name.

• Select where you want to export your recovered data: either in existing database or want to create new database for exporting the recovered items.

• Choose With Schema & Data and Click on Export.

• Start the SQL Server and see your successfully recovered data in SQL Database.

Conclusion

The blog is for all those user who are facing SQL Server header error 5172. I have created a scenario in which I created SQL Server error 5172 and repair it in possible way. Choose any of the option wisely !

“In today’s arena the most common query is how can I convert MDF to SQL file for free? As there are no such details provided over the internet to fix this unanswered puzzle. So today, with this article I am going to discuss two different ways to easily convert MDF files into SQL script of SQL Server database.”

Quick Glance on SQL Server and MDF File

SQL Server is a relational database management system in the technology field. It is compatible with more than one transactions processing. Master database file (MDF) and Log transaction files (LDF) are the main files used by SQL Server for storing the database contents. Due to the functionalities it facilitates, it has become one of the sought-after programs. MDF is a foremost database file of SQL Server is used to save components like views, stored information etc. A user is unable to open the MDF files which ultimately provides a complete security on data breaching and much more. Sometimes, the user wants to convert their MDF to SQL file. We come up with appropriate ways to do same.

Why user Export MDF to SQL File?

There are several reasons behind the MDF to SQL file conversion. Mainly, we describe some of them have a look:

1. By migrating .mdf file to SQL script, a user can use these files to multiple servers.
2. Due to this fact that MDF file become unreadable. A user cannot open, read and view their .mdf files. Than, there is only one way to read or open MDF files is by converting it into SQL scripts.
3. Often times, users attach MDF files to the database by some untrusted sources, which may result in error as it could includes malicious information. So execute these scripts on SQL Server so that we can convert MDF to SQL file.

Effortless Way to Convert MDF to SQL File Without Any Hindrance

The above-mentioned manual solution is very cumbersome and complex to the non-technical user. So, it is better to switch on the third-party tool such as “SQL Repair Tool”. With the help of this tool a user can easily convert .mdf to SQL file without any data loss. This utility facilitates an understandable interface so, a novice user can also perform the conversion without taking an expertise help. The tool is equipped with advance technologies that supports ASCII and Unicode both XML data types. Software recovers SQL Server 2019, 2017, 2016, 2014 and below database files versions.

Guide to Perform MDF to SQL File Conversion

• To convert MDF to SQL, Run SQL recovery software.
• Select “Open” and the required MDF files from its location

• Select “Advanced Scan” scan option.

• Software will review all database items like tables, triggers, stored procedures and views etc.

• SQL Script files are stored as .sql files. To generate SQL script from MDF file, go to “Export” option to begin the export procedure. After that, opt “SQL Server Compatible SQL Script” option.

• The software will create the script of the selected database and now this format is ready to migrate.
• You have options to selectively select the database objects you want to export. Select it accordingly.

• Software gives you two options to generate SQL file from MDF. One is 'With Schema' or 'With Schema and data'. Check the option 'With Schema and Data'option

• Select the option to export deleted SQL Objects and Deleted SQL Table records and Click on Export button.

• In this step, a wizard will appear that display “Export Completed”. To move forward click on “OK”

Final Word’s

Most of the database admin tasks can be performed through SQL scripts. Throughout this article, we have gone through the super instant tips to convert MDF to SQL file. And discussed basic steps to perform the conversion manually also. User can opt any method as per their desire.