All the information in SQL Server database is stored in 8 KB-sized pages. If any page gets corrupted, server fails to read the data in the database and displays different errors. Some common errors that can occur due to page corruption in the database are 823, 824 and 825. In this article, we will discuss the common causes behind corruption in pages and the methods to resolve the SQL Server errors 823, 824, and 825.
Reasons Behind Page Corruption in MS SQL Server
The SQL Server errors 823, 824, and 825 are primarily the result of corruption in database pages. Page corruption can occur due to the following reasons:
- Problems in hardware
- Virus and malware infection
- Software problems
- System is suddenly stopped while opening the database
How to Fix 823, 824, and 825 Errors in SQL Server
The MS SQL Server errors 823, 824, and 825 indicate that there are inconsistencies in the file system or the database file. So, first check the SQL Server error log and variable text in the error message events. These will help you find the exact cause behind the problem. If there are any page-level issues or corruption in the SQL database, then follow the below methods to repair the database and recover the data, thus resolving the errors.
Method 1: Restore Database from Backup
The first and basic thing to do is restore the database from backup, if you have an updated database backup file. To restore the backup file, run the below query:
RESTORE DATABASE databasetest13
FROM DISK = 'Z:\SQLServerBackups\databasetest13.bak' ;
Then, try connecting to the database and see if the problem is resolved.
Method 2: Perform Page Level Restore
If the error message displays the information related to problem with pages, then you can perform Page-level Restore. This will help you to restore only the corrupt pages. However, it does not restore the Transaction logs, Allocation pages, Page 0 of all data files (the file boot page), Page 1:9 (the database boot page), and Full-text catalog.
Here is the T-SQL command to perform Page-level Restore in SQL Server:
Note: You must know the File ID where the page is located and the Page ID of corrupted page.
RESTORE DATABASE
PAGE = ' [ ,... n ] ' [ ,... n ]
FROM [ ,... n ]
WITH NORECOVERY
Alternatively, you can also use the SQL Server Management Studio (SSMS) to restore pages.
Method 3: Use DBCC CHECKDB Command
You can use the DBCC CHECK DB command to repair the corrupt SQL database. Here’s how to use the command with REPAIR_ALLOW_DATA_LOSS option:
- First, set the database in single user mode by running the below command: ALTER DATABASE Dbtesting SET SINGLE_USER
- Then, run the below command to repair the database: DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
- After repairing the database, set it to multi user mode by using the command: ALTER DATABASE Dbtesting SET MULTI_USER
Note: Repairing the database with the REPAIR_ALLOW_DATA_LOSS option may deallocate rows, pages, or series of pages. This may result in some data loss.
Method 4: Use a Third-Party SQL Repair Software
To avoid data loss and quickly repair the corrupt SQL database, you can use a professional SQL database repair tool. Stellar Repair for MS SQL is one of the best tools to repair corrupt SQL database and recover all its objects with complete precision and integrity. It can help fix common SQL database corruption errors, including the 823, 824, and 825 errors. The software is compatible with SQL Server 2022, 2019, 2017, and earlier versions.
key features of Stellar Repair for MS SQL:
- Repairs both MDF and NDF files
- Restores all the data, including tables, pages, views, keys, and indexes, from damaged SQL database with complete integrity
- Supports compressed data
- Supports repairing of SQL databases on both Windows and Linux systems
- Ability to save the data from the repaired file in multiple formats – live database, new database, CSV, and XLS
- Helps to resolve complex corruption-related errors
Conclusion
The SQL Server errors, like 823, 824, and 825, usually occurs due to corruption in pages in SQL database. In this article, we’ve covered some methods that can help you troubleshoot and resolve such errors. You can restore the data from the backup file.
If the backup is unavailable or obsolete, you can use the DBCC CHECKDB command to repair the database.
However, this approach is time-consuming and may also result in data loss.
Alternatively, you can use an advanced third-party SQL repair tool, like Stellar Repair for MS SQL to repair the database and restore all the data with complete integrity.
You can download the free trial version of the software to repair the corrupted SQL database and preview the data.
