Step-by-Step Guide to Fix Page Corruption in SQL Server

Causes of Page-Level Corruption in SQL Server Database

Each page in SQL has a 96-byte header that contains information related to page number, type, free storage, etc. Its corruption impacts the entire database and prevents user access. The root causes of corruption in SQL database file pages are:

• Problems with hardware that cause its failure. (Example: Power outages in hard drive.)
• Software issues damaging the SQL database. (Example: Bugs, Corrupt Files, etc.)
• Accidental deletion of data by the user or other issues due to human errors.
• Corruption due to virus or malware attacks in the SQL Server databases.
• Installation of unnecessary updates or some unidentified error in the server.

Note: These are the specific reasons for page corruption. Furthermore, immediately take a data backup if you find glitches in the SQL server. As you know, page corruption can hamper your crucial SQL Server data.

Troubleshoot Page Corruption in SQL Server Database

Page corruption is indeed a troublesome issue for database administrators and users. There is a manual solution and a professional tool to resolve it. However, before proceeding towards the methods to fix the issue, let’s learn how to identify page corruption. The various ways in which you can do so are:

• Run the query SELECT * FROM msdb…suspect_pages to identify corrupted pages in the suspect_page table.
• Execute DBCC CHECKDB ‘db_name’; query to find the physical and logical inconsistencies in the database causing corruption.
• Additionally, you can run the SELECT dbname, page_verify_option_desc FROM sys.databases query to find page-level corruption in SQL server.
• Lastly, you can execute the DBCC PAGE command after setting the Trace Flag 3604. It is:
  DBCC TRACEON (3604) DBCC PAGE (‘DBNAME’, FileID, PageID, PartitionID) WITH TABLERESULTS

  Copy Code

Now, continue to find the best solutions to combat page corruption in SQL.

Manually Rectify Page Corruption in SQL Server

We have different manual methods to fix the corruption and recover the data. These are explained in detail below:

Method 1: Restore Pages for Database Backup

1. Open SSMS and connect to the Server Instance. Then, move to Object Explorer.
2. Expand it, select the Database from the tree nodes, and right-click it.
3. Further, press Restore Database and choose the needed options. Follow the process and, finally, click OK.

Note: This method fixes SQL Server page-level corruption only if your database is small or a few pages are damaged.

Method 2: Selective Page Restore to Fix Corruption

1. In SQL Server Management Studio, select the Database from Object Explorer.
2. Next, right-click DB and choose Task and, later, Restore >> Page.
3. To find the corrupt pages, press on Check Database Pages.
4. Afterward, click Add and enter the File ID and Page ID that you need to restore.
5. Additionally, select the Backup file and press OK to complete the restoration.

Note: This method helps recover the damaged or corrupted pages individually from your large database. It is suitable if you do not want to restore the entire database. Also, it is necessary to have a full or bulk-logged recovery model.

Furthermore, there is an alternate way to this solution using the T-SQL commands. Restore Page Corruption in SQL Server with the command:

Method 3: DBCC CHECKDB for Page Level Corruption in SQL Server

1. First, open the Transact-SQL Query window in SSMS.
2. Then, run the given command and set the database to Single User mode:
   ALTER DATABASE Dbname SET SINGLE_USER

   Copy Code
3. Next, run the DBCC CHECKDB command to identify and repair the corruption:
   DBCC CHECKDB (N ’Dbname’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO

   Copy Code
4. In the end, run the command given below to reset data to Multi-User mode:
   ALTER DATABASE Dbname SET MULTI_USER

   Copy Code

Note: It is suitable if you have multiple pages corrupted in the database.

Method 4: DBCC WRITEPAGE to Fix Page Corruption in SQL Server Database

Ensure you create a backup of your physical MDF and NDF files before implementing this method. Also, download a text and data comparison tool. Then, follow these steps:

1. Foremost, check the differences between the original file and the corrupted file using a Text comparison tool.
2. Next, run DBCC CHECKDB to check physical and logical integrity and get minimal fixes.
3. After that, set Trace flag 3604 and run the DBCC Page command to identify corrupted data in the pages. The command is:
   DBCC TRACEON (3604) DBCC PAGE ( {‘namedb’ | db_id}, fileno., pageno. [, printopt={0|1|2|3} ])

   Copy Code
4. In addition, you can modify the file (if needed) with the command below:
   ALTER DATABASE dbname SET SINGLE_USER DBCC WRITEPAGE (‘dbname’, fileid, pageid, slot, offset, data, bytes) ALTER DATABASE dbname SET MULTI_USER

   Copy Code
5. For verification of physical offset and page number, run the query SELECT * from dbo.tablename.
6. After the page corruption location is confirmed, compare the damaged and the latest original backup file. Use the Text comparison tool for this task.
7. Finally, repair page level corruption in SQL Server with DBCC CHECKDB.

Challenges of the Manual Method

• It needs a lot of time and manual effort with high technical expertise.
• Chances of I/O and other errors if any step goes wrong in the procedure.
• The data can become even more difficult to reaccess, and one-page corruption can affect others, too.
• Deallocation of rows and pages can happen, resulting in data loss.

Automated Tool to Handle Page Corruption in SQL Server

To resolve SQL database page-level corruption in minimum time without much hassle, use the SysInfo SQL Database Recovery Tool. It is an efficient solution to recover the corrupted pages from any MDF/NDF file, small or large, at once. The tool has advanced features and allows saving data at two different locations as per the need. Additionally, it doesn’t require any SQL or technical knowledge, unlike the manual methods, and can be operated smoothly. Moreover, this tool has no risk for data loss and ensures 100% accurate and precise results. Furthermore, it can even handle other errors like SQL Server 18456, 5, and more.

How to Deal with Page Corruption and Recover Database?

Follow the steps below to utilize this amazing tool for instant page-level restore in SQL Server:

1. Download, install, and run the SQL Server Database Recovery Tool on the PC.
2. Click Open and (…) to browse and select the MDF file to add. Hit Open.
3. Then, choose Standard or Advanced mode for recovery and click OK.
4. Preview the data in the panel and select the corrupted pages from the tables.
5. Further, choose those to restore and click Save SQL Data from the top bar.
6. Opt for Save in Database or Save as Scripts and provide details accordingly.
7. At last, select the filters needed and click OK to fix page corruption.

Conclusion

To sum up, this blog specified various reasons for the occurrence of page corruption in SQL Server. We have five different ways to troubleshoot the issue and restore the data on the pages. The manual methods are lengthy, tricky, and time-consuming. Also, they contain risk and need expertise. Whereas, using a professional tool is quite easy and performs a quick recovery without worries or risks.

Frequently Asked Questions

About The Author:

Simran Bhatia

Simran Bhatia is a technical content writer engaged in writing clear, concise, and SEO-optimized content. With a background in computer science and a passion for writing, I thrive to deliver complex technical content in simple layman terms.