如何在SQL Server中执行页面级还原

最新推荐文章于 2024-04-09 15:37:17 发布

culuo4781

最新推荐文章于 2024-04-09 15:37:17 发布

阅读量534

点赞数

文章标签：数据库 java python linux mysql

原文链接：https://www.sqlshack.com/how-to-perform-a-page-level-restore-in-sql-server/

版权

In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

在有关SQL Server备份和还原的楼梯系列的这篇文章中，我们将讨论对于数据库管理员来说，了解页面还原内部的重要性。在大多数情况下，执行页面级还原足以满足数据库可用性。良好的备份策略是恢复或还原的关键，而SQL Server为我们提供了一种用于更精细地修复数据库页面的选项。在SQL Server中执行页面级还原是一个聪明的选择，其中不需要还原整个VLDB数据库备份。

Identifying the corruption and finding a solution is the key to successful restoration or recovery of the database. If the number of corrupted pages seems too large, and if the administrator feels that restoring the entire database from the most recent backup is a viable option, only in that case should the option be full restoration.

识别损坏并找到解决方案是成功还原或恢复数据库的关键。如果损坏的页面数看起来太大，并且管理员认为从最近的备份还原整个数据库是可行的选择，则只有在这种情况下，才应选择完全还原。

Page restore is a technique used to replace corrupted pages of information in a database with uncorrupted data from a backup. Page-level restore can be performed via SSMS or T-SQL. The intent of the restore is to fix one or more corrupted pages from the available backups. To identify corrupted pages, look for pages marked as “suspect” in the table, msdb syspect_pages.

页还原是一种用于用备份中未损坏的数据替换数据库中损坏的信息页的技术。页面级还原可以通过SSMS或T-SQL执行。还原的目的是从可用备份中修复一个或多个损坏的页面。若要识别损坏的页面，请在表msdb syspect_pages中查找标记为“可疑”的页面。

In this series we will discuss the following topics:

在本系列中，我们将讨论以下主题：

Introduction to page-level restore
页面级还原简介
Different ways the data gets corrupted
数据损坏的不同方式
How to deal with corruption
如何应对腐败
Restore a page from a full backup using SSMS and T-SQL
使用SSMS和T-SQL从完整备份还原页面
And more…
和更多…

Let’s explore the ways to corrupt and restore a page using a demo.

让我们探索使用演示破坏和还原页面的方法。

This entire illustration is based on the ProdSQLShackDemo database. Let’s walk through the following SQL to get a better understanding of the page-level restoration concepts. Here are the steps involved:

整个插图基于ProdSQLShackDemo数据库。让我们遍历以下SQL，以更好地理解页面级还原概念。涉及的步骤如下：

Create the database, ProdSQLShackDemo
创建数据库ProdSQLShackDemo
Switch the database context to ProdSQLShackDemo
将数据库上下文切换到ProdSQLShackDemo
Change the recovery mode of the ProdSQLShackDemo to FULL
将ProdSQLShackDemo的恢复模式更改为FULL
Create the table, SQLShackAuthor, and load some test data
创建表SQLShackAuthor，并加载一些测试数据
Backup the database, ProdSQLShackDemo
备份数据库ProdSQLShackDemo

Let’s get straight to it….

让我们直接说吧。

CREATE DATABASE ProdSQLShackDemo;
GO
USE ProdSQLShackDemo;
GO
-- Set the recovery model of SQLShackDemo to FULL
ALTER DATABASE ProdSQLShackDemo SET RECOVERY FULL;
GO
 
USE ProdSQLShackDemo;
GO
-- Create the table SQLShackAuthor
CREATE TABLE SQLShackAuthor (
    ID int IDENTITY(1,1) PRIMARY KEY,
    AuthorName nvarchar(100) NOT NULL
);
GO
--Add records to SQLShackAuthor table
INSERT SQLShackAuthor
    VALUES  ('Brain Lockwood'),
            ('Samir Behara'),
            ('Ahmad Yaseen'),
			('Sifiso W. Ndlovu'),
			('Marko Radakovic'),
			('Bojan Petrovic'),
			('Robert Seles'),
			('Marko Zivkovic'),
			('Luna Cvetkovic'),
			('Prashanth Jayaram')
			;
GO
USE ProdSQLShackDemo;
GO
SELECT * FROM SQLShackAuthor;

-- Create ProdSQLShackDemo full backup to default path 
BACKUP DATABASE ProdSQLShackDemo
    TO DISK = 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\ProdSQLShackDemo.bak'
    WITH FORMAT;
 
GO

Corruption is often very arbitrary; there are many things that can be the cause. Let’s use a tool to identify the corruption, in this demo. There are two tools that can help us with this:

腐败往往是非常任意的。原因可能有很多。在此演示中，让我们使用一种工具来识别损坏。有两种工具可以帮助我们：

DBCC WRITEPAGE
DBCC写页面
Christian Mass, a simple Hex editor freeware tool. You just need to download, extract and run the XVI32.exe Christian Mass ，一个简单的十六进制编辑器免费软件。您只需要下载，解压缩并运行XVI32.exe

To understand the internals of how the data is organized, we can use the undocumented DBCC IND and DBCC PAGE database consistency checker commands.

为了了解数据的内部结构，我们可以使用未记录的DBCC IND和DBCC PAGE数据库一致性检查程序命令。

DBCC IND

This Database Consistency Checker command lists all the data and index pages. The command returns the information about the actual pages that are being used in the database.

此数据库一致性检查器命令列出所有数据和索引页。该命令返回有关数据库中正在使用的实际页面的信息。

This command requires three arguments:

此命令需要三个参数：

ProdSQLShackDemo in our case) ProdSQLShackDemo ）
SQLShackAuthor in our case) SQLShackAuthor ）
1. -1 list everything (indexes +Index Allocation Map, or IAMs)
  -1列出所有内容（索引+索引分配图或IAM）
2. -2 list only IAMs
  -2仅列出IAM

DBCC IND('ProdSQLShackDemo','SQLShackAuthor',-1)

We can see that this only returns two pages. For the demo, I will be focusing on the page ID 153. I’m going to deliberately corrupt page 153 with two bits of information. The table SQLShackAuthor contains a value, Prashanth Jayaram, which we will meddle with, to corrupt the page in this demo.

我们可以看到，这仅返回两页。对于该演示，我将重点介绍页面ID153。我将故意使用两点信息破坏页面153。表SQLShackAuthor包含一个值Prashanth Jayaram ，我们将使用该值进行干预，以破坏此演示中的页面。

Let’s enable the trace flag 3604 by running the following DBCC command. This option is used to redirect the output to the console.

让我们通过运行以下DBCC命令来启用跟踪标志3604。此选项用于将输出重定向到控制台。

DBCC TRACEON(3604)
GO

Execute the DBCC PAGE command to examine the internal page details of the data and indexes.

执行DBCC PAGE命令以检查数据和索引的内部页面详细信息。

DBCC PAGE('ProdSQLShackDemo',1,153,3) WITH TABLERESULTS

To turn off the trace flag

关闭跟踪标志

DBCC TRACEOFF (3604);

To corrupt the page, we need to identify the starting offset of the page. Multiplying the page ID (153) with a number of bytes per page (8192) gives us the offset value.

要破坏页面，我们需要确定页面的起始偏移量。将页面ID（153）与每页字节数（8192）相乘得出偏移值。

SELECT 153*8192 AS [Offset] ---1253376

Make the database offline. This option enables XVI32.exe tool to edit the mdf file.

使数据库脱机。该选项使XVI32.exe工具能够编辑mdf文件。

USE master;
GO
ALTER DATABASE ProdSQLShackDemo SET OFFLINE;

To use XVI32

使用XVI32

Open XVI32.exe
打开XVI32.exe
Browse to the path of the mdf file and select it
浏览到mdf文件的路径并选择它
Go to Search or Press Ctrl+G and then type offset 1253376

转到搜索或按Ctrl + G，然后键入offset 1253376
Locate the value Prashanth on the screen and replace the character ‘s’ with ‘a’ and ‘h’ with ‘a’

在屏幕上找到值Prashanth并将字符“ s”替换为“ a”，将“ h”替换为“ a”
You can see that value has been changed from ‘Prashanth’ to ‘Praaanth’. Save and close the file.

您可以看到该值已从“ Prashanth”更改为“ Praaanth”。保存并关闭文件。

Bring the database online.

使数据库联机。

USE master;
GO
ALTER DATABASE ProdSQLShackDemo SET ONLINE;

Run the DBCC PAGE command to view the modified value.
运行DBCC PAGE命令以查看修改后的值。

By default, checksums are enabled on the database. Therefore, we can see the consistency error by executing a select statement or even by running the DBCC CHECKDB command.

默认情况下，在数据库上启用校验和。因此，我们可以通过执行一条select语句甚至运行DBCC CHECKDB命令来看到一致性错误。

SELECT TOP (1000) [ID]
      ,[AuthorName]
  FROM [ProdSQLShackDemo].[dbo].[SQLShackAuthor]

The I/O error message is shown below

I / O错误消息如下所示

The expected and actual fields in the error message clearly define the expected values and the reason for the logical inconsistency, as shown below.

错误消息中的期望和实际字段明确定义了期望值和逻辑不一致的原因，如下所示。

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd8b40584; actual: 0xd8b0858d).
It occurred during a read of page (1:153) in database ID 26 at offset 0x00000000132000 in file ‘f:\PowerSQL\ProdSQLShackDemo.mdf’.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

消息824，第2级，第2行，第24级
SQL Server检测到基于逻辑一致性的I / O错误：错误的校验和（预期：0xd8b40584；实际：0xd8b0858d）。
它是在读取文件'f：\ PowerSQL \ ProdSQLShackDemo.mdf'中偏移量0x00000000132000的数据库ID 26中的页面（1：153）期间发生的。
SQL Server错误日志或系统事件日志中的其他消息可能会提供更多详细信息。
这是严重的错误情况，威胁到数据库的完整性，必须立即更正。
完成完整的数据库一致性检查（DBCC CHECKDB）。此错误可能是由多种因素引起的；有关更多信息，请参见SQL Server联机丛书。

Another way to check the results is to run the following:

检查结果的另一种方法是运行以下命令：

DBCC CHECKDB('ProdSQLShackDemo') with tableresults

The table results option allows an administrator to programmatically investigate the results of CHECKDB using SQL queries.

使用表结果选项，管理员可以使用SQL查询以编程方式调查CHECKDB的结果。

We can also query the msdb.dbo.suspect_pages table to get the details on the corrupted pages.

我们还可以查询msdb.dbo.suspect_pages表以获取有关损坏页面的详细信息。

SELECT * from msdb.dbo.suspect_pages

Next, let us change the recovery of the database in the database property page to NONE and then execute the select statement to view the modified data

接下来，让我们在数据库属性页中将数据库的恢复更改为NONE，然后执行select语句以查看修改后的数据

ALTER DATABASE ProdSQLShackDemo SET PAGE_VERIFY NONE

We can see the modified value in the following output

我们可以在以下输出中看到修改后的值

执行还原 (Performing a restore)

In this section, I will take you through the steps to restore the page. Let’s revert the corrupting changes we previously made.

在本节中，我将引导您完成还原页面的步骤。让我们还原先前所做的破坏性更改。

Before starting the restoration process, validate the consistency of every page by enabling the CHECKSUM along with PAGE_VERIFY database option. This allows the SQL Server database engine to calculate the checksum over the contents of the whole page.

在开始还原过程之前，通过启用CHECKSUM和PAGE_VERIFY数据库选项来验证每个页面的一致性。这样，SQL Server数据库引擎就可以计算整个页面内容的校验和。

ALTER DATABASE ProdSQLShackDemo SET PAGE_VERIFY CHECKSUM

With SQL Enterprise license, one could perform a page-level restore while the database is online. The process is nothing but actually swapping the corrupt data page(s) with a copy of data pages from the backup. On other editions of the SQL Server, the recovery would be an offline process, meaning, the database has to be offline during the restore. Let’s now perform the restore.

使用SQL Enterprise许可证，可以在数据库联机时执行页面级还原。该过程只不过是将损坏的数据页与备份中的数据页副本交换而已。在其他版本SQL Server上，恢复将是一个脱机过程，这意味着在还原过程中数据库必须处于脱机状态。现在让我们执行还原。

Object-Explorer, right-click the database Object-Explorer ，右键单击数据库ProdSQLShackDemo ProdSQLShackDemo
Go to tasks, select Restore and click Page….

转到任务，选择恢复，然后单击页面…。
In the restore page window, click the button at the top, Check Database Pages. This would identify the pages that it suspects are inconsistent, by executing the DBCC command, DBCC CHECKDB PHYSICAL_ONLY.

在“还原页面”窗口中，单击顶部的“ 检查数据库页面 ”按钮。通过执行DBCC命令DBCC CHECKDB PHYSICAL_ONLY ，可以确定怀疑的页面不一致。
Specify the backup files to pull the data from by taking a look at the files in the backup sets section. Press the Verify button. You’ll see at the top that the backup media was verified successfully.

通过查看备份集部分中的文件，指定要从中提取数据的备份文件。按下验证按钮。您会在顶部看到备份媒体已成功验证。
The database will go through the process of restoring the clean pages and a message on whether the process was successful or not would be displayed. we can see that the database restored successfully. Go ahead and press the OK button.

数据库将经历恢复干净页面的过程，并会显示有关该过程是否成功的消息。我们可以看到数据库已成功还原。继续并按确定按钮。

Now, let’s ensure that the data is correct by issuing the following SQL statement. You can even run the DBCC checkdb command to verify the data integrity.

现在，通过发出以下SQL语句来确保数据正确。您甚至可以运行DBCC checkdb命令来验证数据完整性。

SELECT TOP (1000) [ID]
      ,[AuthorName]
  FROM [ProdSQLShackDemo].[dbo].[SQLShackAuthor]

摘要 (Summary)

In this article, we looked at page-level restore, by corrupting the data and then performing a recovery. The intention was to prove that restoration of data into a corrupted table is possible—simple, even—provided the backup is good.

在本文中，我们研究了通过破坏数据然后执行恢复的页面级还原。目的是证明只要备份是好的，就可以将数据恢复到损坏的表中-甚至简单。

We could take advantage of options such as checksums and torn_page_detection.

我们可以利用诸如校验和和torn_page_detection之类的选项。

We also saw that only explicitly specified pages were restored and as such,this task would not pose any sort of resource overhead. The selected pages are replaced by the copy of the corresponding page from the specified data backup.

我们还看到仅还原了明确指定的页面，因此，此任务不会造成任何类型的资源开销。所选页面将替换为来自指定数据备份的相应页面的副本。