SQL Server Troubleshooting: How to detect and speedily fix SQL Database corruption

  • Article

Introduction

DBA life is not easy. All the days you must improve the performance, increase the security, comply security and DBA standards.

One day, even when you take care of a lot of your databases with maintenance and verify the performance all the time the database is corrupted. Many times, in suspected status.

Why?

Sometimes the database can fail due to a missing patch, a problem with the hardware. In most of the cases, it is a hard disk problem. Sometimes is due to hacker attacks.

If the database is corrupted, how can we fix it?

In this article, we will show different alternatives to speedily fix SQL Server Database corruption. First, we will talk about SQL Server backups and database restoration. Next, we will talk about DBCC commands to repair databases and database objects. Finally, we will talk about a third alternative to repair damaged databases.

You can check Suspected page using following command.

 SELECT * FROM msdb.dbo.suspect_pages

Getting started

If your database is corrupted and you want to fix it, the first alternative is to use your backup. There is a lot of information about backups to restore an SQL Server database.

 We will not cover database backups and restoration in this article, but you can find a lot of useful information here:

 Sometimes, you do not have a current backup or sometimes the backup is not the current one. Sometimes the virus and hacker’s attacks can damage not only the database but also the backups.

In these cases, you can try the DBCC CHECKDB command. This one is a special command to check the integrity of the database. It can also be used to detect database problems. To check the integrity, you can use the following T-SQL command:

DBCC CHECKDB;   
 
GO

This will check the integrity of the current database. 

In addition, to repair a database using the DBCC command line, you can use the following commands:

ALTER DATABASE  database_name SET  SINGLE_USER
GO
DBCC CHECKDB('database_name', REPAIR_REBUILD)
GO

The first line sets the database in single-user mode and the DBCC CHECKDB repairs the database.

If your database is too big, it is possible to repair individual database objects. For example, to repair an individual table, you can use the following T-SQL:

DBCC CHECKTABLE ('schema.tablename', REPAIR_REBUILD);   
 
GO

Make sure that your table is in single-user mode before repairing:

ALTER DATABASE  database_name SET  SINGLE_USER
GO

To set again to multiuser mode, run the following T-SQL Statement:

ALTER DATABASE adventureworks2016ctp3 SET MULTI_USER

GO

There are also commands to check or repair constraints, catalogs, filegroups, identity values and more. For a full list of these commands, you can refer to the following link:

Database Console Commands

Third-party solution

Another solution is to use special software to repair the database if it is damaged. In this occasion, we will talk about the Stellar Phoenix SQL Database Repair. This is a product from Stellar Information Technology Pvt. Ltd.

This company provides solutions to recover data and other administrative functions. Stellar Phoenix SQL Database Repair is used to recover damaged databases in SQL Server. To repair the database, you need to provide the mdf file.

In SQL Server, the mdf file contains the SQL Server Data File. The Data file is the file that contains the SQL Server Data of the database. Each database contains one or more data files.

The installer is small, and it takes a few minutes to download and install

This software allows to Repair a damaged database in case it is damaged by hardware failures, virus, hacker attacks or other external factors.

To repair a Database, you need to select the Select Database option. Then you will need to search your mdf file. What this software does is to create a copy of your mdf file and repairs it.

You can also export your data to other formats. The software is fast and simple to use. You can also select which databases you want to recover.    

Conclusion

When your database is corrupt, you have different options to recover the database. The first option that you should try is to recover your database from the most recent backup. If you do not have a backup, or if your backup is also damaged, you can use the DBCC CHECKDB to repair your database. There are different DBCC commands to repair different database objects like tables, indexes and other objects.

Finally, if none of these alternatives included in SQL Server work, you can try third-party tools. In this article, we mentioned the Stellar Phoenix SQL Database Repair that can help you to recover data by repairing the mdf file. If you do not know where your data file is, this tool can help you to find you and then creates a copy that is repaired. I hope you liked this article.

References

If you want to learn more about database corruption, database backups and restoration, DBCC commands to repair data, Stellar Phoenix SQL Database repair, you can find useful information using the following links provided: