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.
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.
Step 3: After selecting an MDF file, click Repair to begin the repair process.
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.
Step 5: Click the OK button once the 'Repair Complete' message box pops-up.
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.
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.
Step 8: Click Save. After successful completion of file saving process, the following screen will appear.
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.
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.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
4 min read
4 min read
4 min read
