RP Staff·

How To Repair Corrupt MS SQL Database?

When MS SQL database gets corrupted, we can have both data availability and performances issues. For example, if a table within the database is corrupted, some data can no longer be readable and when we try to write or read data from the database, we will get an error.

If an index is corrupted, then the application will run but slowly. In such cases, we need to recover the database. In this article, we will see how to repair and restore corrupt database using MS SQL native tools and a third-party SQL database repair tool.

Efficient Ways for Repairing and Recovering Corrupt SQL Database

Here are some effective ways to restore corrupt MS SQL database.

Restore from Backup

The simplest and most basic method is restore from database backup. For this, you need a backup database that is not corrupted. This method is useful only when you have a recent copy of your database and you can accept to lose all the data between the time of backup and the time of recovery operation.

You can restore a database backup using the T-SQL RESTORE command. Alternatively, you can do the same operation by using the Microsoft SQL Server Management Studio (SSMS).

Follow the below instructions to restore database from backup via SSMS.

Type “SSMS” to search the SQL Server Management Studio application and open it.

When the program starts, insert the credentials and press the Connect button.

In Object Explorer, locate the database and right-click on it.

In the menu, locate the Restore Database option and click on it.

The Restore Database window will appear. Select the Device option and then click on the three dots.

The Select backup devices dialog box will appear. Press the Add button and choose the backup file you want to restore. Then, press the OK button.

You will see the name of the database in the Database name field.

Press the OK button.

If the database does not exist, the restore operation will be executed and the database will be online.

Otherwise, the restore operation will return an error message.

To restore over an existing database, you should check the “Overwrite the existing database (WITH REPLACE) checkbox.

Use DBCC CHECKDB Command

MS SQL, like other RBDMS, has special commands to check the integrity of database. One of these commands is repairing the database using DBCC CHECKDB command. It is used to check both the logical and physical integrity of all the objects in a database. DBCC CHECKDB command has many options and parameters.

You can execute this command without any option. This will check the database you are connected to.

Otherwise, you can specify the name of database.

Some important options/parameters are:

NOINDEX

When you use this parameter, non-clustered indexes are not checked. Since this is an intensive operation, this allows you to save time. In case of corruption, you can delete the indexes and simply recreate them.

REPAIR_REBUILD | REPAIR_ALLOW_DATA_LOSS

When you specify the REPAIR_REBUILD option, the command will try to recover SQL objects that contain error. It performs only repair. There is no possibility of data loss. It is a safe to use option.

The REPAIR_ALLOW_DATA_LOSS is a more “aggressive” option. It tries to repair the error found even if this may lead to data loss.

TABLOCK

The DBCC CHECKDB command, to avoid locks, does not operate directly on the database, instead it works on a snapshot. By specifying the TABLOCK option, a lock on the processed SQL object is placed. This way, the check process becomes faster but the concurrency available on the database decreases.

ESTIMATEONLY

When you use this option, the estimated space needed in the tempDB system database is printed.

PHYSICAL_ONLY

When you use this option, only the integrity of the physical structure is checked. Logical checks are not performed.

DATA_PURITY

This option checks for data purity. A data purity issue refers to columns that are stored out of range value for the datatype. When executed, the DBCC CHECKDB command returns with each SQL object present, whether errors are found or not.

In the above example (see image), you can observe that:

  • Only one table (lastvin) is found in the database.
  • This table has 6859 rows in 57 pages.

At the end of the procedure, a summary is shown.

If you see the message “found 0 allocation errors and 0 consistency error”, it means your database is not corrupted. If errors are found, more details will follow. For example, you will see the message, “REPAIR_ALLOW_DATA_LOSS is the minimum repair level for the errors found by DBCC CHECKDB” (see below).

Use a Third-Party SQL Repair Tool

When you face corruption issues in SQL database, we suggest using a dedicated SQL database recovery tool. Among various great tools, we recommend Stellar Repair for MS SQL. This is a powerful, yet easy-to-use software that can directly parse the database (.mdf) file. So, it does not require MS SQL Server installation. It can operate even if your SQL Server is crashed.
Stellar Repair for MS SQL can fix errors that the DBCC CHECKDB command cannot fix. It can recover all the database objects, such as stored procedures, triggers, views, and so on. It can also recover deleted tables. It allows to export the recovered data to a new database or other formats such as CSV and XLS.

Let’s see how to use it.

Download the software and install it. Once the application is launched, you can see the Select Database button.

Select the database by pressing the Browse button. You can also choose to include deleted records.

After selecting the database, press the Repair button. When the process is finished, a dialog box appears. Click OK.

By default, the data is saved in MS SQL format. You can also choose other formats, such as CSV, Excel, and HTML.

Enter the login credentials (server name, user name, and password) and then press the Save button.

Conclusion

In this article, we discussed how to repair and recover corrupted SQL Server database. You can RESTORE the database from backup using the SSMS or use the T-SQL DBCC CHECKDB command to verify if the database is corrupted or not. If the DBCC CHECKDB command shows that the database is corrupted, you can repair it by using Stellar Repair for MS SQL to avoid data loss.

You may also like to check out:

You can follow us on Twitter, or Instagram, and even like our Facebook page to keep yourself updated on all the latest from Microsoft, Google, Apple, and the Web.