How to repair suspect database in SQL Server

Programs work with databases, and if the database crashes essential information can be lost and this will be disastrous to the user. So what are your options?

Programmers say that every program has an error, even if it’s just one and if you’ve ever been in close contact with computer software, you’ll understand that even the best software can malfunction sometimes.

Sometimes people’s jobs and life work depend on the functionality of the software, other times it may be their financial well-being or health. This is why it is so important that when software malfunctions, it is quickly returned to its normal working condition.

See: UK Police deleted 150,000 arrest records in a software glitch

Programs work with databases, and if the database crashes essential information can be lost and this will be disastrous to the user. Most databases operate on the Microsoft SQL server and it takes a lot of time and strength to recover if something happens to the server.

There are several ways to restore the database if anything happens to it, but the first one must understand the suspect pages table. The information in the suspect pages table is available to anyone with access to the MSDB database, and it can be updated by anyone with update permission. Members of the DB owner fixed database role in MSDB or sysadmin fixed server role can insert, update and delete records.

Methods of database recovery from suspect mode:

Reset database status + DBCC CHECKDB

DBCC CHECKDB

Use third-part software Recovery Toolbox for SQL Server 

The suspect pages table contains information about potentially corrupted pages and is used when deciding if to recover the pages. The suspect table page is located in the MSDB database.

A page is considered “suspicious” if one of the following errors is encountered when the SQL Server Database Engine attempts to read it.

• Error 823: Which occurs during a cyclic checksum (CRC) check launched by the operating system, such as a disk error (occurs with some hardware errors)
• Error 824: Such as page break (or any other logic error)

The ID of each “suspicious” page is recorded in the suspect pages table. In this table, the Database Engine records all suspicious pages that it encounters during processing, in particular:

• When processing a request, you must read the page.
• When DBCC CHECKDB is executed.
• During a backup operation.

During a restore operation, DBCC fix, or database drop, the suspect pages table is also updated as needed.

So, here are several ways to restore databases when they go into “Suspect Mode”.

Method 1.

In my work, I once encountered a situation where the working database at the end of the day went into “Suspect Mode“, and it was last archived many hours earlier. It was not possible to transfer back to its normal mode until the base was repaired. DBCC checkdb also refused to start.

This was very frustrating for me until I discovered a solution. Here are the steps to the first method in restoring a database.

First, you need to switch the database to the EMERGENCY mode by doing the following: 

• EXEC sp_resetstatus ‘yourDBname’;
• ALTER DATABASE yourDBname SET EMERGENCY

Secondly, you then test the database:

• DBCC checkdb (‘yourDBname’)
• ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
• ALTER DATABASE yourDBname SET MULTI_USER

Method 2. 

If the base cannot be restored by 1C

If you are faced with a damaged server database (MS SQL 2005), that is malfunctioning, and it is impossible to recover by testing-fixing (checksum error occurs). In this case, the database is not uploaded to a file – the same error. If you tried severally and it keeps failing, you need to try to restore the database by testing the SQL itself. 

Here are the commands:

• DBCC CHECKDB (‘database’, REPAIR_FAST)
• DBCC CHECKDB (‘database’, REPAIR_REBUILD)

If these two do not work, then the third can be used, but it carries with it a possible loss of data and it is advisable to use it as a last resort.

DBCC CHECKDB (‘database’, REPAIR_ALLOW_DATA_LOSS)

If the command is not executed due to non-single-user mode, then you can go by the command:

Alter database db-name set SINGLE_USER

!Before testing, you need to make a backup!

Method 3

Use Recovery Toolbox for SQL Server software

So what is this program? First of all, it is an important tool in the work of any system administrator. This software allows you to work with MS SQL Server files of any version.

The program allows you to restore database files in a complex. Unlike similar solutions, this software has its characteristics:

1. Data from unreadable databases can be restored in a suspended state;
2. The program works with all versions of Microsoft SQL Server;
3. The software allows you to restore the most important and valuable in the database;
4. If there are several units in the base, then this is not an obstacle either;
5. It is possible to restore tables while working with MDF files;
6. SQL MDF Recovery allows you to export data directly to Microsoft SQL Server;
7. Information can be saved as scripts;
8. The extracted data is directly exported to a new database;
9. The program works under any version of Windows;
10. The interface works in various languages;
11. Before recovery, the data can be viewed;

The worst dream of any system administrator is when the server crashes, and then they discover that the database has crashed. In such a situation, how does one restore the base if it is damaged?

To recover data after a database failure, a backup is usually used. But if for some reason, a copy was not made, you can try using Recovery Toolbox for SQL Server. Most likely, the program will be able to restore the working state of the database. 

See: Man accidentally destroyed production database on first day at job

To test this assumption, you need to:

1. Install Recovery Toolbox for SQL Server on your computer, it is not necessary to use the full version, the demo version is enough;
2. Select the file;
3. After that, the analysis of the database starts;
4. It is necessary to study the list of all tables that will be restored;
5. We look through the data in the tables;
6. We study the restored objects;
7. Configure saving parameters;
8. Selecting the required data;
9. Save them (this will require the full version)

Database recovery

To quickly fix an MDF file using the application, you need to press a few buttons. During recovery, all data is copied to a new database or in the form of scripts to disk. Thus, the program does not affect damaged files in any way.

How does it all work?

1. The damaged database is selected.
2. We look at what can be recovered from the data.
3. Decide on the export option.
4. Select the data for recovery.
5. Examining the report after saving.

The program is shareware. In other words, the program costs 99$ and you can download it here.

Did you enjoy reading this article? Don’t forget to like our page on Facebook and follow us on Twitter!