The SQL database (.MDF and .NDF) files can get corrupt due to various reasons like virus attacks on the system, software issues, hardware failure, sudden power failure, etc. If there is corruption in these files, you may fail to run queries associated with them like SELECT, INSERT, UPDATE, RESTORE Statements, etc
Opening a corrupt database may trigger different errors like SQL error 926, 963, SQL error 3624. Repairing the database is essential to restore data access. The corruption in database can occur in any version of SQL server you’re using. In this article we’ ll learn the steps to repair SQL server 2016 , 2019, 2014 ,2012, 2008 or 2005
Why you Need to Repair SQL Server Database?
Before proceeding, let’s understand the scenarios where you require to repair the SQL Server database.
- If your database is marked as suspect or stuck in recovery pending state, preventing you from accessing the data.
- If database 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 corrupt log file that has rendered the database inaccessible.
Methods to Repair SQL Server Database
Every database administrator or SQL user must have a backup-and-recovery strategy in place. If you have a healthy backup file, then you can easily restore the SQL database from it. However, if the backup file is also corrupted or not updated, then you can follow the below methods to repair the corrupt database.
Method 1: Use DBCC CHECKDB Command to Repair SQL Database
DBCC CHECKDB command is a database console command used to identify the integrity of SQL Server database, check issues and errors, and repair the database. It checks the physical and logical integrity of all objects within the SQL database, including index pages, system tables, and other allocation structures. If any of these checks fail, it reports consistency errors.
You can use SQL Server Management Studio (SSMS) to run the DBCC CHECKDB command by following the steps below:
First, run the DBCC CHECKDB command to detect corruption in the database. If you are not able to access your database, then change the status of the database to Emergency using the below command:
This state provided read-only access to the database. Now, you can easily run the DBCC CHECKDB command as shown in the example.
If it detects corruption in the database, it will recommend one of repair options:
- REPAIR_FAST
- REPAIR_REBUILD
- REPAIR_ALLOW_DATA_LOSS.
Here’s the step-by-step process to repair the database using the REPAIR_ALLOW_DATA_LOSS option:
- First, launch SQL Server Management Studio (SSMS) and then connect to the SQL Server instance
- Locate to the toolbar and then click New Query .
- In the displayed query window, copy and paste the following commands as it is:
EXEC sp_resetstatus [DATABASEName]
ALTER DATABASE [DATABASEName] SET EMERGENCY
DBCC CHECKDB [DATABASEName]
ALTER DATABASE [DATABASEName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('DATABASEName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
ALTER DATABASE [Database Name] SET MULTI_USER
- Next, click on the Execute option.
- It will start repairing the database.
Method 2: Use SQL Repair Software to Fix Database
If the DBCC CHECKDB command fails to repair or recover the database files, then you can use the Stellar Repair for MS SQL. It is a comprehensive SQL repair tool to repair and recover corrupt SQL database files quickly and with no data loss. To use this tool, follow the steps below:
- Download, install, and launch the latest .exe of Stellar Repair for MS SQL.
- On the Select Database window, click Browse and select the corrupt database file you want to repair. If you don’t know the location of the database file then click Search.
- After selecting the desired database file, click Repair to start the repair process.
- Choose an appropriate scan mode like Standard Scan or Advanced Scan.
- Once the Repair Complete message is displayed, click OK option
- Next, select all or specific objects you want to save and click on the Save button under File to preview all the repaired data in file.
- Next, save the repaired database using one of the following options:
- Choose the desired file format like MDF/CSV/HTML/XLS to save the repaired file.
- You can select to save the database as a New or Live Database.
- Specify the location where you want to save the repaired database.
- After the selection, click Save.
- After successful completion of file saving process, the Save Complete screen will appear. Click OK.
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 SQL databases. If you have an updated backup try to restore it to recover the corrupt MDF/NDF files. But if the backup is corrupted or unreadable, you can run the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS, to repair the database. However, it does not guarantee the complete data recovery. You can opt for the Stellar Repair for MS SQL as an alternative to repair corrupt database and its associated errors while maintaining complete data integrity.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
5 min read
