Microsoft SQL Server is made up of a collection of tables that stores all the data in structured manner. Sometimes, data may be corrupted due to several reasons and in that case it becomes inaccessible to the user which is really a complex problem for the organization. So, it is important to identify the reasons for database corruption and different ways to handle it. Here in this article, we are covering the various commands and professional way to identify database Corruption in SQL Server.
Commands to Detect Database Corruption in SQL Server
The corruption in the database is more dangerous as it doesn’t give any notification or error messages regarding any corruption until the user tries to fetch the data. So, the user is unaware of any issues in the database. There is one single query to check corruption in the database-
SELECT * from msdb.dbo.suspect_pages
And if there is no error in the database then it will show no rows found. This means that it did not find any corrupted data. It will check for corruption in past queries, not in the newer inserted query.
Different ways to Check Database Corruption in SQL Server
There are several methods to check database corruption in SQL Server
Method 1– Monitor SQL Server Suspect Pages.
DBCC CHECKDB command is widely used by Database Administrators to locate corrupt files and fix them.
To check the corruption in the database execute the below command
DBCC CHECKDB (database_name)
Method 2- Using PAGE verify option.
CHECKSUM command is used as the “PAGE_VERIFY” option in the database which helps in identify page-level corruption caused due to I/O errors.
Databases on SQL server 2008 and above version CHECKSUM used as verify_page option by default.
Method 3 – Run DBCC CHECKDB
Run DBCC CHECKDB regularly to check for database integrity which is crucial to check database corruption in SQL server.
If it finds corruption, it will return all the error messages showing complete details of why database corruption occurred in SQL Server.
Method 4- Set SQL server Agent Alerts
While running DBCC CHECKDB commands can help check the integrity of the databases, without setting up alerts. But setting up Alerts with SQL server agent can help you Check SQL Database Corruption.
Follow these given steps to set up SQL Server Agent Alerts.
- In SQL Server Management Studio(SSMA), right-click on SQL Server Agent, and click on properties.
- On the properties page click on Alert System under ‘select a page. On the ‘Alert Systems’ page, check the Enable Mail Profile checkbox, and choose Database Mail from the mail system list.
- Click OK to configure.
- Now create an alert for the SQL Agent by clicking right-click on alert and select new alert.
Perfect Solution to Fix Database Corruption in SQL Server
Once you identify the SQL Database corruption in SQL Server now you can fix it by using the Trusted SQL Recovery Tool. It works to restore objects like tables, stored procedures, views, functions, and rules. And you can also export data directly into SQL Server Environment without losing any data. It can scan corrupt database files and repair them.
Step 1: Download and install SQL Recovery software and open it. The following screen will appear
Step 2: Open MDF File
From the software, the menu Click ‘Open’ and browse the desired MDF file from its location.
Step 3: Select Quick scan for normal corruption of database files and check the auto-detect option if you want to detect the SQL Server version automatically.
NOTE: If the Auto detects option fails then you can also try selecting the SQL server version manually
Select NDF option which is the second tab and check the first radio button to detect the NDF file Automatically.
Step 4: SQL Server Recovery Software will show the scanning report after performing the Quick Scan operation on MDF Files
Step 5: The software will show the Quick Scan results and recommend an Advanced scan if the expected results are not found by Quick Scan. Click OK to preview.
After previewing the database objects like table, view, Trigger, functions, rules, and stored procedure.
Step 6: Click on Export and start recovering the database.
So, here we have discussed all the possible reasons for database corruption in SQL Server and also check database corruption in SQL Server. Also discussed the methods to fix database corruption. Until a database is corrupted we can not get access to the database so here we have discussed the commands to fix the corrupted database files. We have discussed the tool to restore the corrupted database which is a reliable solution to recover any corrupted SQL database.