Best Way to Repair Metadata Corruption in SQL Server
Know what Metadata is ?
Metadata is simply defined as “Data about data”. You can get all the information of SQL Server Database by running sys.databases.
Select * from sys.databases;
SQL Server has number of functions that return the information about the database and its database objects known as Metadata Functions.
Some of the function are given below:
INDEX_COL, OBJECT_ID, OBJECT_NAME, COL_NAME, OBJECT_NAME etc.
How Metadata corruption occurs ?
Metadata Corruption occurs when your system views, procedures, functions, stored procedure get damaged due to some malicious attack, hardware failure, accidental shutdown, fail to update operation etc.
It also occurs when you update index statistics with INCREMENTAL=ON. You will get Error Msg 211 and it is generic metadata corruption.
Detect Metadata Corruption In SQL Database:
Run DBCC CHECKDB command
DBCC CHECKDB (corrupt) WITH NO_INFOMSGS;
DBCC CHECKDB command is used to check the consistency of the database which help you to prevent from the high level corruption. It checks the physical as well as the logical integrity of the database objects.
Error message will show as below:
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After the database consistency check, Run DBCC CHECKCATALOG to detect metadata corruption .
DBCC CHECKCATALOG (corrupt) WITH NO_INFOMSGS;
DBCC CHECKCATALOG checks catalog consistency for the specified database. You will get a warning message showing corruption in metadata.
How to Fix Metadata corruption ?
If your metadata is not corrupted, you can recover it by restoring backup using BACKUP and RESTORE command. You also need to backup your corrupted database before restoring the previous one to prevent data loss.
Sometimes along with the metadata, the database backup has corruption in too. For this, you have two options, Either export everything present in corrupted database to a new database. Or by manually fixing the system tables.
By doing manually means, you need to alter ‘hidden’ system tables. For this,
- Shutdown the server
- Go to the Advanced Tab in Configuration Manager, add ‘-m;’ to the start of startup parameter string.
- Start the server
- Connect using DAC
The system tables maps three hidden system tables:
- Sysindexes is sys.sysidxstats
- Sysobjects is sys.sysschobjs
- Syscolumns is sys.syscolpars
By running these command you can fix metadata corruption, after this Run DBCC CHECKDB to again check database consistency.
Quick Way to Recover MetaData Corruption:
The safest and quickest way to recover Metadata corruption, is to use SQL Server Database Repair Utility. The software recovers corrupted database when the backup as well as meta data is corrupted. It also recovers deleted database records in SQL Server. You just need to add corrupt .mdf database file to the software to repair metadata corruption from the SQL Server Database. You can recover functions, views, triggers, stored procedures, tables etc. The tool provides two different option for exporting. One is SQL Server Database & other as SQL Server Compatible Script. The SQL Repair tool supports SQL Server 2016, 2014 & its below versions.
The blog discusses what metadata corruption is and how to deal with it. Metadata corruption can be caused due to various reasons like hardware failure, malware attack, virus etc. The blog covers different ways to recover metadata corruption.One easy way is to migrate your corrupted database to new database, but this could lead some data loss. So, another utility is to use SQL Database Recovery Software for quick repairing of database files.