How to Repair Corrupted SQL Server Database?
When faced with a corrupted SQL Server database, it can feel overwhelming, but having dealt with such situations firsthand, I’ve learned the steps to address this issue effectively. DBCC CHECKDB reporting consistency errors, queries failing with high-severity errors, or backup jobs failing are all signs of a corrupted SQL Server database. Errors like 823, 824, or 5172 also point to corruption. In these situations, accessing your data isn’t possible until the database is repaired. Let me share my experience and tips on how to handle this.
What Causes SQL Database Corruption?
Through my work, I’ve observed that database corruption can occur for various reasons:
- Power Failures: A sudden power outage that crashes the server, especially while processing data, is a common cause of corruption.
- I/O Subsystem Failures: Believe it or not, 99% of database corruption cases are attributed to issues with the I/O subsystem. Faulty disk drives, controllers, or SAN controllers can repeatedly lead to corruption.
- SQL Server Bugs: I’ve seen how certain bugs in SQL Server, like the online index rebuild issue in SQL Server 2014 and 2012 (see KB2969896), can trigger corruption.
- Malicious Attacks: SQL Server databases can also fall victim to malware or malicious programs that corrupt or render them inaccessible.
- Abrupt System Shutdowns or changes made to SQL Server accounts can also contribute to database corruption.
What Not to Do When You Detect Corruption?
From experience, I’ve learned the hard way what actions to avoid when dealing with a corrupted SQL Server database:
- Don’t Shut Down the SQL Server: The database goes into recovery mode during corruption. Shutting it down can leave the database in an inconsistent state.
- Avoid Restarting SQL Server: Restarting may worsen the situation and cause additional damage.
- Never Detach the Database: Detaching a corrupt database can complicate the recovery process. Reattaching it might fail, especially if it’s in SUSPECT or RECOVERY_PENDING mode.
- Don’t Use ‘REPAIR_ALLOW_DATA_LOSS’ Without Backups: This repair option risks data loss, so always restore from a recent backup first.
How to Repair a Corrupted SQL Server Database?
Here are the steps I follow to repair a corrupted SQL database:
Step 1: Set the Database to Emergency Mode
If the database isn’t accessible, you need to put it into EMERGENCY mode to gain read-only access. Open SQL Server Management Studio (SSMS) and run:
ALTER DATABASE [Test_Database] SET EMERGENCY;
Replace ‘Test_Database’ with the name of your database.
Step 2: Check for Corruption Errors
Next, I check for errors using DBCC CHECKDB:
DBCC CHECKDB (Test_Database) WITH NO_INFOMSGS;
The ‘NO_INFOMSGS’ argument suppresses unnecessary messages, focusing only on errors.
Step 3: Switch to SINGLE_USER Mode
Before repairing, I ensure no one else can modify the database by switching it to SINGLE_USER mode:
ALTER DATABASE Test_Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Step 4: Repair the Database
Based on the DBCC CHECKDB recommendations, I use the appropriate repair option. For example:
DBCC CHECKDB (Test_Database, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Note: REPAIR_ALLOW_DATA_LOSS is risky and should only be used as a last resort.
Step 5: Set the Database Back to MULTI_USER Mode
Once the repair is complete, I restore multi-user access:
ALTER DATABASE Test_Database SET MULTI_USER;
A Better Alternative: Stellar Repair for MS SQL
Over the years, I’ve discovered that relying solely on manual methods isn’t always sufficient, especially for severe corruption. That’s when I turn to tools like Stellar Repair for MS SQL. It’s been a lifesaver for repairing large, severely corrupted databases while ensuring zero data loss. Here’s what I love about it:
- Repairs both MDF and NDF files.
- Fixes various corruption types, from file header to page-level issues.
- Recovers deleted records.
- Saves the repaired database in multiple formats like CSV, XLS, or directly to an existing/live database.
Wrapping Up
Database corruption can disrupt operations and lead to data loss if not handled properly. While tools like DBCC CHECKDB can help, they have limitations. That’s why I recommend trying Stellar Repair for MS SQL for a more reliable and efficient repair process. Download the free demo version to see how it works for yourself.
Stay in touch to get more updates & news on hdhub4u.co.uk!
