Summary: This article discusses about Microsoft SQL Server metadata corruption. It begins by explaining what is metadata in a SQL database and how you can detect metadata corruption in the database. It then describes ways to recover a database in the event of metadata corruption. These include restoring the database from a backup or creating a database and migrating user tables in it. You can also use SQL recovery software to restore the database when the metadata or the database becomes corrupted.
What is Metadata in a database?
Metadata is information about other data. In SQL Server, you can get SQL Server Database information using system views or functions. For example, the system view sys.databases can show information about the SQL Server databases.
Let’s take a look at a simple query:
Select * from sys.databases
The query will show the database information including the name, id, owner sid, creation date an more information:
Here you can find a list of several system views in SQL Server:
- Lists of SQL Server views
- Object Catalog Views (Transact-SQL)
There are also metadata functions in SQL Server that get database information. For example, the function db_name will return the database name using the database ID as the input parameter.
This example illustrates the usage. You specify the database id (1) and the function will return the database name:
Select db_name(1) as dbname
For a list of other functions or metadata stored procedures in SQL Server, check these links:
- Metadata Functions
- System Functions for Transact-SQL
- System Stored Procedures
Microsoft SQL Server Metadata Corruption – What Is it?
Metadata corruption in SQL Server means your system views, procedures, functions are damaged due to a blackout, a virus, hacker attack, hardware failure, failed upgrade, insufficient disk space, shutdown problems, or other reasons.
How Can I Detect Metadata Corruption in my Database?
The DBCC CHECKDB can be used to detect any database corruption. The following command shows how to detect errors in the current database:
DBCC CHECKDB;
GO
To detect metadata corruption errors, you can use the DBCC CHECKCATALOG. Here you have an example:
DBCC CHECKCATALOG;
GO
DBCC CHECKCATALOG will detect the corrupt objects in your current database.
How Can I Recover my Database from Metadata Corruption?
The first step would be to use a backup and restore it from a status where the Metadata is not corrupted. However, you may need to backup your corrupt database before restoring the old one to avoid data loss.
In some scenarios, the database backup is corrupt, or the metadata in the backup is also corrupt.
In those scenarios, you could create a new database and migrate the user tables using T-SQL, SSIS, or other migration tools of your preference.
Another option that can help you when you do not have a valid backup is to use SQL Recovery Software. Stellar Repair for MS SQL software is used to repair a corrupt database when the metadata or the database itself is damaged. Select the corrupt.MDF file to repair the metadata corruption from the SQL Server database.
Note: If you have NDF Files associated with your Database, the keep all the NDF files at the same location from where you have selected the MDF file.
You need to specify the.MDF to the software. If you do not know where your data files are, you can use the Search option:
You can also find the location of the file by using SSMS. Right-click on the database that you want to repair and select Properties:
Go to the Files page and you will be able to find the path of the .MDF files there:
If you do not have SSMS (or you do not want to use it), you can use the sp_helpdb system stored procedure to get the information about the data files path.
You just need to use a command similar to this one:
Sp_helpdb ‘databasename’
You will also need to stop the SQL Server, because the file is in use. You can use the Windows Search and search for Services. In services look for your SQL Server service instance and stop it:
If you have SSMS, you can also use it to stop the SQL Server service as an alternative:
Another option is to stop in the cmd. In the cmd use the following command:
net stop mssqlserver
Now, open the SQL Recovery Tool, select your data file and press the Repair button:
You will receive a success message:
Once done, you can restart your SQL Server and the metadata will be repaired. To verify that the metadata is fine, you can run the DBCC CHECKCATALOG to verify that there are no new errors.
Something that I really like about this software is that it supports any SQL Server version. It supports SQL Server 2019 and lower versions.
You can install this software on Windows Vista, XP, 7, 8 and 10. In the Windows Server versions, it supports 2003, 2008, 2012 and 2016.
Conclusion
In this article, we learned that metadata in a database can be obtained in SQL Server using system views, functions, and stored procedures. We also learned that the metadata can be corrupted by a virus, hardware failure, hacker attack, or other reasons. We learned different ways to restore the data loss. One way is to migrate to another database and the other alternative was to use Stellar Repair for MS SQL. We need to find the data files, stop SQL Server and then repair the metadata
If you have questions, feel free to write your comments. Thanks!
