Summary: Pages in SQL databases can get corrupted due to several reasons, such as software bugs, sudden server shutdown, hardware failure, etc. In this post, we’ll discuss how to check page-level corruption in SQL database, and mention some solutions to fix page corruption and restore the corrupt pages. You’ll also find about an advanced SQL database repair software that can easily repair corrupt database and restore all its objects without any data loss.
Page is the primary storage unit in SQL Server. All the information in SQL Server database (MDF/NDF) files is stored in pages of 8 KB size. Each page starts with a 96-byte header that stores information, like page number, page type, free space etc. If a page gets corrupted, then it can impact the entire database. In this case, you may receive different errors when opening the database file or even not able to access the database. Therefore, fixing page-level corruption immediately is important. In this article, we will see how to fix page corruption in SQL Server database.
How to Detect Corruption in SQL Server Database?
Before proceeding to the solutions to fix corruption, you need to check the SQL database for corruption. There are various ways to check page-level corruption in SQL Server database. Some of them are explained below:
1. Run the SELECT Statement
SQL Server keeps a log of all the suspect pages in the suspect_pages table in msdb (system) database, containing all the corrupt pages that are reported by SQL queries. You can check the suspect_pages table to see the corrupted pages in the database. For this, you can run the SELECT statement as given below:
SELECT * FROM msdb..suspect_pages
The above command will return a grid with a row of each corrupted page. In our case (see the above image), you can see that the page, with id 368, is corrupted.
2. Execute DBCC CHECKDB Command
The DBCC CHECKDB command checks physical and logical integrity of database objects and index relationships. You can use the DBCC CHECKDB command, as given below, to check for corruption in the SQL database.
DBCC CHECKDB ‘database_name’;
If the above command finds corruption in the database, it will display consistency errors, along with the possible option to repair the database.
3. Use CHECKSUM Option
You can also use the CHECKSUM option to detect page-level corruption in SQL database, if the PAGE_VERIFY option is set to CHECKSUM. CHECKSUM is a database option that defines the process in SQL Server to check page consistency when it is written to the disk and read again from the disk. To check page-level corruption in the SQL database, run the following command:
SELECT name, page_verify_option_desc FROM sys.databases4. Run DBCC PAGE Command
The DBCC PAGE command is an undocumented command that is used to detect corrupt pages in a database. To get the result from the DBCC PAGE command, you need to set the Trace flag 3604. Here is the command:
DBCC TRACEON (3604)DBCC PAGE('Recovery_test',1,368,2) WITH TABLERESULTS
The command output will help you understand what kind of corruption has occurred and what type of data is affected.
Solutions to Fix Page Corruption in SQL Server Database
After checking the database by using any of the above methods, if you find any page-level errors or corruption in SQL database, then follow the below methods to repair the database and recover the data.
1 – Perform Page Level Restore
You can perform “Page Restore” to restore only the corrupt/damaged pages, without restoring the entire database. This is a faster way when you need to restore only a few individual pages. The page restore applies to databases that use full or bulk-logged recovery system. You can restore pages by using the SQL Server Management Studio or T-SQL commands. To restore pages using SSMS, follow the below steps:
- Open SSMS and connect to your SQL Server instance.
- In the Object Explorer, expand the Databases node, right-click on the affected database, and select Tasks > Restore > Page. This will open the Page Restore window.
- In the Page Restore window, check the selected database, backup file, backup set, and other required details.
- To identify the corrupt pages, click Check Database pages. This may take some time.
- When the process is finished, it will display the pages grid. Here, you can identify the pages to be restored.
- Use the Add and Remove button to add or delete the pages.
- Next, click OK to restore the pages listed in the pages grid.
Alternatively, you can use the T-SQL command – RESTORE DATABASE (see the below example) to restore the page
Note: You must know the File ID where the page is located and Page ID of the corrupted page.
RESTORE DATABASE <database_name> PAGE = '<file: page> [ ,... n ] ' [ ,... n ] FROM <backup_device> [ ,... n ] WITH NORECOVERYLimitations of the above method:
- Restoring multiple pages using Page Restore is a time-consuming process.
- It can restore only database pages. It does not restore Transaction log, Allocation pages, Page 0 of all data files (the file boot page), Page 1:9 (the database boot page), and Full-text catalog.
- It only supports databases that use the full or bulk-logged recovery model.
2 – Use DBCC CHECKDB Command
If multiple pages are corrupted, then you can use the DBCC CHECKDB command to repair the corrupt SQL database. You can use the REPAIR_ALLOW_DATA_LOSS option with the command.
First, you need to set the database in single-user mode by running the below command:
ALTER DATABASE Dbtesting SET SINGLE_USERThen, run the below command to repair the database:
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;GOAfter repairing the database, set it to multi-user mode by using the below command:
ALTER DATABASE Dbtesting SET MULTI_USERLimitations of the above method:
- It may deallocate rows, pages, or series of pages, when repairing the database. This may result in some data loss.
- You may require to run this command several times to resolve the issues, which is a time-consuming process.
3 – Use a Professional SQL Repair Tool
If the above methods fail, you can use a professional SQL database repair tool to repair the corrupt database. Stellar Repair for MS SQL is one such tool that can quickly repair the corrupt SQL database without any data loss. It can repair and restore all the database objects, including tables, pages, views, keys, and indexes, with complete precision.
Some key features of Stellar Repair for MS SQL:
- Supports PAGE compressed data.
- Repairs both MDF and NDF files.
- Restores all the data from damaged SQL database with complete integrity.
- Supports repairing SQL databases on both Windows and Linux systems.
- Supports MS SQL Server 2022 and lower versions.
- Allows to save the repaired file in multiple formats – live database, new database, CSV, and XLS.
- Helps to resolve complex corruption-related errors.
Watch the below video to know how the software works:
To Conclude
There are various reasons that can cause page-level corruption in SQL Server databases. Above, we have discussed how to detect page-level corruption in database. If there is corruption in a few pages, you can use the page restore method to restore the corrupted pages. If multiple pages are corrupted, you can use the DBCC CHECKDB command to repair the corrupt database. However, both these methods have some limitations. Alternatively, you can use a professional SQL repair tool, such as Stellar Repair for MS SQL. It can easily repair highly damaged SQL database files and restore all the data, including pages, with complete precision.
