{"id":8161,"date":"2019-07-24T11:29:16","date_gmt":"2019-07-24T11:29:16","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/microsoft-sql-server-advanced-corruption-and-recovery\/"},"modified":"2026-11-03T19:29:28","modified_gmt":"2026-11-03T19:29:28","slug":"microsoft-sql-server-advanced-corruption-and-recovery","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/de\/blogs\/microsoft-sql-server-advanced-corruption-and-recovery\/","title":{"rendered":"Microsoft SQL Server advanced corruption and recovery"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>This blog describes corruptions that can occur at the database level in&nbsp;Microsoft\u00a9 SQL Server\u00a9, how to detect them, and how to correct them by&nbsp;using advanced restore and repair techniques.<\/p>\n<p>Currently, SQL Server is one of the most popular and widely used relational&nbsp;database management systems because of its advanced internal structure and great&nbsp;reliability. Many organizations have opted for SQL Server databases to&nbsp;maintain and save critical business data.<\/p>\n<p>Companies expect database administrators (DBAs) to improve database&nbsp;performance, maintenance, and security continuously. When a database is corrupted and data is&nbsp;inaccessible, you might suspect a variety of causes such as hardware corruption,&nbsp;disk issues, a virus attack, or operating system (OS) failures. Repairing&nbsp;corrupted databases is not an easy task until you know the best technique.<\/p>\n<p>This post discusses some causes of database corruption, helps you to identify&nbsp;page corruption, explores the Database Consistency Checker (DBCC) CHECKDB&nbsp;command, and demonstrates advanced restore and repair techniques.&nbsp;<br \/>\n&nbsp;<\/p>\n<p><strong>Database corruption<\/strong><\/p>\n<p>\nSQL Server stores the user data in the form of pages. Those pages reside in&nbsp;<strong>.MDF<\/strong>&nbsp;(Primary) data files. Corruption of an <strong>.MDF<\/strong> file can lead to&nbsp;corruption of the entire database. The data file page is good when written out&nbsp;of SQL Server\u2019s memory (that is, onto a disk) but bad when read back into memory,&nbsp;as shown in the following image:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26616-body-Picture1_16.png\" alt=\"mssql recovery pic 1\" \/><\/p>\n<p><strong>Causes of database corruption:<\/strong><\/p>\n<p>Database corruption types include the following items:<\/p>\n<ul>\n<li>I\/O Subsystem (This is one of the most common reasons for database corruption.)<\/li>\n<li>Windows operating system<\/li>\n<li>File system drivers such as encryption or antivirus<\/li>\n<li>SAN or RAID controllers<\/li>\n<li>Disks<\/li>\n<li>Memory<\/li>\n<li>File Headers<\/li>\n<li>SQL Server bugs<\/li>\n<li>Human errors<\/li>\n<\/ul>\n<p><strong>&nbsp;&nbsp;Error messages:<\/strong><\/p>\n<p>When you access a corrupted database, you might see the following error messages:<br \/>\n&#8211;&nbsp; <strong>Msg 823 in SQL Server<\/strong><br \/>\n&#8211;&nbsp; <strong>Msg 824 in SQL Server<\/strong><br \/>\n&#8211;&nbsp; <strong>Msg 825 (read retry) in SQL Server<\/strong><br \/>\n&#8211;&nbsp; <strong>Error 9004 SQL Server<\/strong><br \/>\n&#8211;&nbsp; <strong>Metadata Corruption Error<\/strong><br \/>\n&#8211; &nbsp;<strong>Page Level Corruption Error<\/strong><br \/>\n&nbsp;<\/p>\n<p><strong>Tracing page corruption<\/strong><\/p>\n<p>SQL Server has a built-in mechanism to automatically identify and alert you&nbsp;when there is a corruption during I\/0 operations such as reading and writing&nbsp;pages to the disk.<\/p>\n<p>The following different types of page-level verifying options are available on&nbsp;SQL Server and help protect the pages on the disk:<\/p>\n<p><em>&#8211;&nbsp;&nbsp; &nbsp;TORN\\_PAGE\\_DETECTION<br \/>\n&#8211;&nbsp;&nbsp; &nbsp;Checksum<\/em><\/p>\n<p>When <em>TORN\\_PAGE\\_DETECTION<\/em>&nbsp;is specified, a bit is flipped for the 16 x 512-byte&nbsp;disk sector for an 8 KB data file page whenever the page is written to disk.&nbsp;After the page is subsequently read into memory, these values are compared.<br \/>\nIf the bits are found in the wrong state, the page likely was written incorrectly.&nbsp;In this case, the system generates an error message 824 (indicating a torn-page&nbsp;error).<\/p>\n<p>When <em>Checksum<\/em>&nbsp;is specified, the system calculates the checksum value over the&nbsp;contents of the page and stores that value in the page header when writing the page&nbsp;to the disk. After the page is loaded from disk later on, the checksum is&nbsp;recomputed and compared with the value stored at page header. If the value does not&nbsp;match, the system generates an error message 824 (indicating a checksum failure).<\/p>\n<p>Both errors, <strong>823 hard I\/O<\/strong>&nbsp;and <strong>824 soft I\/O<\/strong>, are severity 24 errors and&nbsp;are logged in the <strong>msdb.dbo.&nbsp;<\/strong><em>suspect_pagestable.<\/em><\/p>\n<p>Table <strong>msdb.dbo. suspect_pagestable<\/strong> is used for a single page restore&nbsp;operation and can be logged to the SQL Server error log and the Windows\u00a9 event log.<\/p>\n<p><strong>DBCC CHECKDB<\/strong><\/p>\n<p>DBCC CHECKDB checks the physical and logical integrity of all objects in a&nbsp;database.&nbsp;It is a resource-intensive operation and uses parallel processing, but you can&nbsp;run it in a single thread by tracing flag 2528.<\/p>\n<p><strong>&nbsp;Primitive critical system table checks<\/strong><\/p>\n<p>The primitive checks are designed to perform the check on critical system tables&nbsp;that hold the storage engine metadata and on allocation paths where data is&nbsp;stored in <strong>MDF<\/strong>&nbsp;files.<br \/>\nThe primitive checks are shown in the following list:<\/p>\n<p>&#8211;&nbsp; &nbsp; <strong>DBCC CHECKALLOC<\/strong>: Checks the allocation structure consistency in a&nbsp;database. It checks that the allocation structure is valid and that no single data&nbsp;file pages are allocated to two tables.<\/p>\n<p>&#8211;&nbsp; &nbsp; <strong>DBCC CHECKTABLE<\/strong>: Checks the consistency of tables and indexes. It&nbsp;&nbsp; &nbsp;validates the structure of the table and its related indexes. It determines&nbsp;whether the index data has matching rows in the table and examines the index&nbsp;order keys. If there is any FILESTREAM used in the table, it validates the&nbsp;link\u2019s existence.<\/p>\n<p>&#8211;&nbsp; &nbsp; <strong>DBCC CHECKCATALOG<\/strong>: Checks the consistency between system catalogs.<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;<strong>DBCC CHECKFILEGROUP<\/strong>: Similar to DBCC CHECKDB, this check performs a&nbsp;consistency check for a file group and an allocation check for the database.<\/p>\n<p><strong>Sample code<\/strong><\/p>\n<p>&nbsp;DBCC CHECKDB<br \/>\n&nbsp; &nbsp; &nbsp; [ ( database_name | database_id | 0<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; [ , NOINDEX<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]<br \/>\n&nbsp; &nbsp; &nbsp; ) ]<br \/>\n&nbsp; &nbsp; &nbsp; [ WITH<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; {<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ ALL_ERRORMSGS ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , EXTENDED_LOGICAL_CHECKS ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , NO_INFOMSGS ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , TABLOCK ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , ESTIMATEONLY ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , { PHYSICAL_ONLY | DATA_PURITY } ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ , MAXDOP &nbsp;= number_of_processors ]<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; }<br \/>\n&nbsp; &nbsp; &nbsp; ]<br \/>\n&nbsp; &nbsp; ]<\/p>\n<p><strong>Internal database snapshot<\/strong><\/p>\n<p>Using an internal database snapshot with CHECKDB to perform the checks and&nbsp;maintain transactional consistency prevents blocking and concurrency problems.&nbsp;If you cannot create a database snapshot, make sure you have an exclusive lock&nbsp;for the database and shares table locks because this is required to perform the&nbsp;table level checks. CHECKDB fails on the master if no snapshot is created.<\/p>\n<p>The following list contains the internal checks performed by DBCC CHECKDB:<\/p>\n<ul>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Critical system tables check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Critical system tables logical check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Other tables logical check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Allocation check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Metadata check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Service broker validation check.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Indexed view, spatial index check.<br \/>\n\t&nbsp;<\/li>\n<\/ul>\n<p><strong>CHECKDB errors&nbsp;<\/strong><\/p>\n<p>The following table shows some DBCC CHECKDB errors:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26616-body-Picture2_13.png\" alt=\"mssql recovery pic 2\" \/><\/p>\n<p><strong>&nbsp;Best practices<\/strong><\/p>\n<p>If the database <strong>VLDB<\/strong>&nbsp;has run time issues for CHECKDB, you should use the&nbsp;<strong>PHYSICAL_ONLY<\/strong>&nbsp;option often to reduce the run time for the <strong>VLDB&nbsp;<\/strong>production database. However, you should generally run DBCC CHECKDB with no options&nbsp;specified. Schedule your CHECKDB run depends on your individual production&nbsp;environments.<\/p>\n<p>&nbsp;<strong>Advanced restore options<\/strong><\/p>\n<p>While most people use the simple database restore technique for corruption&nbsp;issues, the following advanced restore techniques are available:<\/p>\n<p><strong>Page restore<\/strong><\/p>\n<p>You can restore one or more pages with this technique. Page-level restore is an&nbsp;online operation for the database enterprise edition, and you can use the offline&nbsp;operation for other editions, which implies that the database can be offline&nbsp;during the restore process.<br \/>\n&nbsp;<\/p>\n<p><strong>T-SQL script<\/strong><\/p>\n<p>&nbsp; &nbsp; <em>RESTORE DATABASE &lt;database_name&gt;<br \/>\n&nbsp; &nbsp; PAGE = &#8216;&lt;file: page&gt; [ ,&#8230; n ] &#8216; [ ,&#8230; n ]<br \/>\n&nbsp; &nbsp; FROM &lt;backup_device&gt; [ ,&#8230; n ]<br \/>\n&nbsp; &nbsp; WITH NORECOVERY<\/em><\/p>\n<p>To obtain the Page Id\u2019s, use different sources available like Error log,&nbsp;Eventtraces, DBCC, and&nbsp; <strong>msdb..suspectpages<\/strong>&nbsp;table records that list corrupt&nbsp;pages and their IDs.<\/p>\n<p><strong>Note:<\/strong>&nbsp;You cannot restore boot pages, file header pages, some pages in critical system tables,&nbsp;and allocation bitmaps as a page restore.<\/p>\n<p><strong>Piecemeal and partial restores<\/strong><\/p>\n<p>Similar to page restore, you can perform piecemeal and partial restores online&nbsp;for enterprise edition and offline for other editions that contain multiple&nbsp;files or filegroups.<\/p>\n<p>Every piecemeal restore starts with a partial restore sequence <em>RESTORE DATABASE&nbsp;<\/em>statement that restores a full backup with a <em>PARTIAL<\/em> option. When this restore&nbsp;completes, the database should be online partially, which implies that remaining&nbsp;files are in recovery pending mode because the recovery has been postponed.<\/p>\n<p>The piecemeal restore depends on the recovery model of the database and its&nbsp;sequence of recovery.<\/p>\n<p><strong>Restore sequence<\/strong><\/p>\n<p>To perform a partial restore of filegroups X and Z and the primary filegroup,&nbsp;run the following code:<\/p>\n<p><em>&nbsp; &nbsp; RESTORE DATABASE DB_XYZ FILEGROUP=&#8217;X&#8217;,FILEGROUP=&#8217;Z&#8217;<br \/>\n&nbsp; &nbsp; &nbsp; FROM partial_backup<br \/>\n&nbsp; &nbsp; &nbsp; WITH PARTIAL, RECOVERY;<\/em><\/p>\n<p>At the preceding point X, the following is true:<\/p>\n<ul>\n<li>&#8211; Filegroup Z and the primary filegroup are online.<\/li>\n<li>&#8211; Files in Filegroup Y are recovery pending.<\/li>\n<li>&#8211; Filegroup is offline.<\/li>\n<\/ul>\n<p>The run <em>RESTORE DATABASE DB_XYZ FILEGROUP=Y<\/em>&nbsp;<em>FROM backup WITH RECOVERY;<\/em><\/p>\n<p>Now, all filegroups are online.<\/p>\n<p><strong>Other advanced repair techniques<\/strong><\/p>\n<p>Does repair always give a guarantee of data recovery? &nbsp;The answer is \u201cNO.\u201d<\/p>\n<p>There could be a number of possible combinations that can corrupt the data, and&nbsp;it&#8217;s impossible to test all combinations. For instance, a system table&nbsp;is corrupted, and repairs won\u2019t work on pages like <strong>Boot<\/strong>&nbsp;and <strong>PFS<\/strong>.<\/p>\n<p>Following are some repair options:<br \/>\n&nbsp;<\/p>\n<p><strong>REPAIR_REBUILD<\/strong><br \/>\nThis option performs a repair, but it might cause data loss when rebuilding damaged NC indexes.<\/p>\n<p>\n<strong>REPAIR_ALLOW_DATA_LOSS<\/strong><\/p>\n<p>This option performs the repair, but it might cause data loss.<\/p>\n<p><strong>System table index rebuild<\/strong><br \/>\nYou cannot repair clustered system table indexes, but you can repair&nbsp;non-clustered indexes in some situations by checking the DBCC CHECKTABLE option.<\/p>\n<p><strong>Note:<\/strong>&nbsp;Always perform any sort of advanced repair techniques on a copy of the&nbsp;database and not on the original database to avoid dire situations.<br \/>\n&nbsp;<\/p>\n<p><strong>Rebuild date from a non-clustered index:<\/strong><\/p>\n<p>If a clustered index or heap is damaged, repair is the only option for&nbsp;recovering data from a non-clustered (NC) index. However, in some cases, repair won\u2019t&nbsp;work if the metadata is corrupted.<\/p>\n<p>You can use a select statement to force the selection of non-damaged NC indexes,&nbsp;but that might depend on column-level coverage of the NC index.<\/p>\n<p><strong>DBCC Page<\/strong><\/p>\n<p>Using <em>DBCC PAGE \u2026 WITH TABLERESULTS<\/em>&nbsp;can identify the key ranges. You can&nbsp;construct it from a non-clustered index and examine the corrupt pages to try&nbsp;to get data from the pages.<\/p>\n<p><strong>Conclusion<\/strong><br \/>\nAfter reading this post, you should have a better understanding of database&nbsp;corruption and how to recover your database by using both simple and advanced&nbsp;techniques. Additionally, these techniques help with bringing the database&nbsp;online after&nbsp; corruption. You should always have a robust backup plan to avoid&nbsp;any corruption outages.<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft SQL Server advanced corruption and recovery<\/p>\n","protected":false},"author":2,"featured_media":8163,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26616","content_type":"blog","created_by":"10130","authored_on":"2024-02-18T12:29:16+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Address database corruptions in Microsoft SQL Server using advanced restore and repair techniques to ensure data integrity and reliability.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26616-insights_image-microsoft-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Microsoft SQL","product":"","publisheddate":"2019-07-24T11:29:16","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Microsoft SQL Server Advanced Corruption and Recovery | Rackspace Technology","field_topics":"","field_slug":"microsoft-sql-server-advanced-corruption-and-recovery","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-8161","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/8161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=8161"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/8161\/revisions"}],"predecessor-version":[{"id":9921,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/8161\/revisions\/9921"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/8163"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=8161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=8161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=8161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}