File Repair

How to Repair SQL Server 2016 Step by Step

Summary: This article serves as a step-by-step guide to repair SQL Server 2016 database. It discusses different types of instances that require performing SQL database repair. Further, the article explains step-wise instructions to repair the db manually or with the help of a professional repair tool.


You might need to perform SQL database repair operation in the following scenarios:

  • Repair a database that is marked as suspect or stuck in recovery pending state, preventing you from accessing the database files.
  • Repair a database that has turned corrupt due to events like server crash, I/O subsystem failure, software bugs, virus/malware intrusion, etc.
  • If you receive SQL errors like 824, 8992, 5172, etc.
  • If you have a corrupt log file that has rendered the db inaccessible.

Also read: SQL Server Production Database Corruption – Causes & Fixes

Normally any database administrator or SQL user will have a backup-and-recovery strategy in place. However, you can restore SQL database from a backup only if is healthy. Also, you must restore to the most recent backup to avoid data loss. But if any corruption exists within the backup or it is not updated, you will need to repair it.

Now, let’s discuss in detail how to repair SQL Server 2016 database step-by-step.

Repairing SQL Server Database Step by Step

Steps to Repair SQL Database via SSMS

You can repair a database using DBCC CHECKDB command with ‘REPAIR_ALLOW_DATA_LOSS’ by executing the following steps in SQL Server Management Studio (SSMS):

Step 1: Launch SSMS. Connect to SQL Server 2016 instance using Windows Authentication or SQL Server Authentication.  

Step 2: Click the New Query tab from the toolbar. In the query window that opens, copy and paste the following commands in the same sequence as below:


EXEC sp_resetstatus [DB_Name]

ALTER DATABASE [DB_Name] SET EMERGENCY

DBCC CHECKDB [DB_Name]

ALTER DATABASE [DB_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

ALTER DATABASE [Database Name] SET MULTI_USER


Note: Running the REPAIR_ALLOW_DATA_LOSS command can lead to data loss. If DBCC CHECKDB does not work or to prevent data loss, using a SQL repair tool may help.
   
Step 3: Hit the "Execute" button to begin repairing the database.
   
The database will get repaired and will open without any errors.

Steps to Repair SQL Database Using Stellar Repair for MS SQL

An advanced SQL repair tool recommended by MVPs and DBAs, Stellar Repair for MS SQL helps repair severely corrupted database files (MDF/NDF) in a few simple steps. Also, it helps recover all the database objects along with deleted records.

How to Repair SQL Server 2016 Database Using SQL Recovery Software

Step 1: Launch the software. Read the instructions displayed on the software main interface window, and then click OK.

Step 2: On the ‘Select Database’ screen, browse and select the database primary file (.mdf) you want to repair. Alternatively, click Search to locate an MDF file if its location is unknown.

Note: If you wish to recover deleted records, ensure that “Include Deleted Records” checkbox is checked.

Select SQL Database File

Step 3: After selecting an MDF file, click Repair to begin the repair process.

Repair Corrupt SQL Database File

Step 4: Choose an appropriate scan mode to repair the MDF file. Selecting the Standard scan option quickly scans the file for corruption errors. For thoroughly scanning the file, you may select Advanced Scan. Click OK to proceed.

Choose Scan Mode

Step 5: Click the OK button once the ‘Repair Complete’ message box pops-up.

Repair Complete

Step 6: Preview all the repaired file data. Next, select all or specific objects you want to save by clicking on the Save button under File.

Preview Repaired SQL Database File

Step 7: The software provides different options to save the repaired database:

  • Choose the file format (MDF/CSV/HTML/XLS) in which you want to save the repaired file.
  • On selecting the MDF file saving format, you can select to save the database as a New or Live db. Further, enter details to connect to your SQL Server instance.
  • Specify the location where you want to save the repaired db.
File Saving Options

Step 8: Click Save. After successful completion of file saving process, the following screen will appear.

Save Complete

The repaired database will get saved with the same name but with ‘Repaired’ prefix at the specified location.

Conclusion

In this article, we talked about common instances that might require you to repair a SQL database. Before trying to run a repair operation, you must first try to restore an updated backup. But if the backup is corrupted or obsolete, perform the steps discussed in the article to resolve the issue. You can run DBCC CHECKDB with the recommended repair option, usually REPAIR_ALLOW_DATA_LOSS, to perform the repair process. However, it involves risk of data loss. As an alternative, use Stellar Repair for MS SQL to fix common corruption errors that might be preventing you from accessing the db.

FAQs

Q. My database log file turned corrupted and made the database unusable. I needed to rebuild the DB. To do so, I executed the following command:

alter database DB1 SET single_user with immediate rollback

Subsequently, I ran the below command:

use master

dbcc checkdb('DB1',REPAIR_ALLOW_DATA_LOSS)

Executing this query is giving me an error message:

“Msg 924, Level 14, State 1, Line 2. Database 'DB1' is already open and can only have one user at a time.”article

After researching, I found that the database needs to be set up in EMERGENCY mode first and then in SINGLE_USER mode. What should I do next?

A. Try reverting your database to MULTI_USER mode, and then run the following:

alter database DB1 SET single_user with rollback immediate

dbcc checkdb('DB1',REPAIR_ALLOW_DATA_LOSS)

Chances are that some other process is using the database connection, before you try to connect to the db. Running the above commands together can help you get that connection.

76% of people found this article helpful