Share via

How to Attach Corrupted Database in SQL Server?

  • Article

In this article, we will talk about corrupted database and how to attach a corrupted database.

Reasons for Database Corruption

Let's start with the basic question. Why does a SQL database get corrupted? There are several factors that may cause corruption in a database.

A database is nothing more than one or more physical files having their own structure that the database engine must recognize. Thinking about this makes it clear that a storage problem can cause the database to become corrupt.

In this case, the database engine tries to write one piece of information but due to problems, a different one is written. For example, we can have a problem with the disk controller, a problem with the drivers, or a problem directly with the disks.

One of the cause we can also include is the shutdown or the blocking of the system when SQL Server is writing to the disks.

So far, we have talked about the problems related to storage.

A database can get corrupted in other ways too but, in my experience, this happens much more rarely. For example; a memory (RAM) problem or a bug in the SQL Server code itself.

Attach a Corrupted Database

If you have an online database that has problems and is in Suspect state, avoid detaching it. Attaching a corrupt database can be problematic.

In case you have a database in Suspect state, the correct procedure to follow is to restore from backup. If you have no backup available, the next correct procedure is to put the database in Emergency mode and then extract as much data as possible.

Here we will discuss what to do if the only way available is to attach the corrupted database.

SQL Server has a stored procedure to attach a database. This stored procedure is **sp_attach_db.

**

You need to specify only the name of databases and the location of the files (mdf and ldf) to attach.

EXEC sp_attach_db @dbname = N'DatabaseTest',

    @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DatabaseTest.mdf',

    @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DatabaseTest_log.ldf';

But what if the database is damaged? Probably, you will get the error message 5172.

If this way didn’t work, we can try a different way.

If we are not interested in retrieving information from transaction log files (ldf), we can try the Create Database T-SQL Command, specifying the ATTACH_REBUILD_LOG option.

A new ldf file will be created. This is the syntax:

CREATE DATABASE [DatabaseTest] ON

    (NAME = N'DatabaseTest ',

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DatabaseTest.mdf')

FOR ATTACH_REBUILD_LOG

If the operation is not successful, depending on the version of SQL Server you are using, you will receive this message:

Or this message:

The first message is more useful and gives the real reason for the failure. The log cannot be rebuilt because the database was not cleanly shut down.

What is the problem now?

SQL Server knows** that there was an active transaction on the database **and this command works only if the database is cleanly shut down. Otherwise, the recovery process must be run. Without the original log, this is simply impossible.

How to Fix a Corrupted Database?

If we are in a situation when we have to attach a SQL Server database without a transaction log and with Open Transactions, then we need a workaround or a software like Stellar Repair for MS SQL.

Let's see the workaround. Follow these steps:

  • Create a new database with the same name and the same file path of the detached database.
  • Set the database offline.
  • Exchange the corrupt database file.
  • Set the database online.

If the procedure works, the database will be online again.

Otherwise, it is better to change the approach and try to recover the data using a dedicated software.

**How to Fix a Corrupted Database with Stellar Repair for MS SQL?

**

Let's see how to use Stellar Repair for MS SQL software to recover data but first I want to tell you why this software is so effective?

This software knows the internal structure of the mdf and ldf files. It tries to parse the corrupted mdf files to extract the data (in this phase there is no need to have SQL Server installed). Once the data is scanned, you can save it into a new SQL Server database or in other format.

Let’s now see how to use it.

Run the program. At its start, you are automatically asked to select the mdf file relating to the database to be recovered.

You can also choose to recover deleted records.

Then, press the Repair button.

There are two modes of recovery: Standard and Advanced.

A first phase of scanning of the structures to be repaired will be started and a progress bar will show the progress of the work.

The result of this scanning phase is saved so you can reuse it if needed.

For each table, you can see columns, foreign keys, unique keys, indexes, statistics, etc. 

You can also see views, stored procedure, function, trigger, etc.

 

Now save the recovered information.

You can choose whether to save it into a new database, an existing database, or in another format (CSV, XLS or HTML).

I chose to create a new database.

Then, press the Next button.

Now, insert username and password to connect to your SQL Server.

Press the Next button.

Choose the "save mode". I chose the Standard saving mode.

When the process ends, this message is displayed.

To Conclude

When it comes to recovering data from an mdf file, there are a couple of ways. If the mdf file is corrupt, you can try to recover it through SQL commands. If you are not an expert, I recommend using a SQL database repair tool, such as Stellar Repair for MS SQL. The tool helps repair .mdf file and restore the database to its original form.