Shulou(Shulou.com)06/02 Report--

This article analyzes "how to perform page-level recovery in SQL Server". The content is detailed and easy to understand. Friends who are interested in "how to recover at the page level in SQL Server" can follow the editor's train of thought to read it slowly and deeply. I hope it will be helpful to everyone after reading. Let's follow the editor to learn more about "how to perform page-level recovery in SQL Server".

In today's article, I want to talk about an important topic that every DBA should know: how to perform page-level restore operations in SQL Server. Suppose you have a corrupted page in SQL Server and you want to restore only the original problem page from the most recent database backup instead of restoring the entire database.

Let's break a page.

The first step I want to show you how to create a table (or index) there is a specific page corruption scenario, here we will do some magic, because out-of-the-box (out-of-box) SQL Server itself will not introduce any corrupted pages (if any, congratulations on finding a BUG). We start by creating a new database and inserting some records into the newly created table.

1 USE master 2 GO 3 4 CREATE DATABASE PageLevelRestores 5 GO 6 7 USE PageLevelRestores 8 GO 9 10-- Create a table where every record fits onto 1 page of 8kb11 CREATE TABLE Test12 (13 Filler CHAR (8000) 14) 15 GO16 17-- Insert 4 records18 INSERT INTO Test VALUES (REPLICATE ('Atoll, 8000)) 19 INSERT INTO Test VALUES (REPLICATE (' Bamboo, 8000)) 20 INSERT INTO Test VALUES (REPLICATE ('Che, 8000)) 21 INSERT INTO Test VALUES (REPLICATE (' dating, 8000)) 22 GO23 24-- Retrieve the selected records25 SELECT * FROM Test26 GO

Next I will make a full database backup. This means that the backup contains all the pages belonging to the Test table. This is very important because next we will break a specific page of the table. To find out which pages belong to the Test table, I use the DBCC IND command to return all pages belonging to this table.

1-- Perform a full database backup2 BACKUP DATABASE PageLevelRestores TO DISK = NumC:\ Backups\ PageLevelRestores.bak'3 GO4 5-- Retrieve the first data page for the specified table (columns PageFID and PagePID) 6 DBCC IND (PageLevelRestores, Test,-1) 7 GO

To break a particular page, I use the undisclosed DBCC WRITEPAGE command. Yes, there is a DBCC WRITEPAGE command available in SQL Server, but please don't tell anyone...

1 ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE 2 GO 3 4-Let's corrupt page 90. 5 DBCC WRITEPAGE (PageLevelRestores, 1,90,0,1, 0x41, 1) 6 DBCC WRITEPAGE (PageLevelRestores, 1,90,1,1, 0x41, 1) 7 DBCC WRITEPAGE (PageLevelRestores, 1,90,2,1, 0x41, 1) 8 GO 9 10 ALTER DATABASE PageLevelRestores SET MULTI_USER11 GO

In order to use DBCC WRITEPAGE, the problem database must be set to single-user mode (Single-User mode) as shown in the code. Here I simulated a storage error and wrote some garbage to the storage page (yes, this will also be encountered in your work! ). Now when you read the database from the table again, SQL Server will return you with an 824 Icano error because the check for the damaged page failed.

1-- Retrieve the selected records2 SELECT * FROM Test3 GO

Once SQL Server detects a corrupted page during access to msdb.dbo.suspect_pages, the corrupted page is also logged in the msdb.dbo.suspect_pages, as shown in the following figure.

1 SELECT * FROM msdb.dbo.suspect_pages

It's a good idea to monitor specific tables in msdb to see if there are corrupted pages in your database. Now that we make things worse, the following code inserts another record into the table.

1-- Now we have additional transaction that we don't want to loose...2 INSERT INTO Test VALUES (REPLICATE ('eBay, 8000)) 3 GO

Let's restore the damaged page.

Now that you are DBA, you want to restore the database to the correct state without losing data (like the records we inserted in the * * step). What would you do? First of all, you need to do a so-called tail-log backup (Tail-Log Backup): you need to back up transactions that have occurred since the last transaction log backup.

1-- Backup the transaction log2 BACKUP LOG PageLevelRestores TO3 DISK ='C:\ Backups\ PageLevelRestores_LOG1.bak'4 WITH INIT5 GO

There is no transaction log backup here, so our backup will contain all transactions that have been executed since the full backup. Now we can restore the operation at the initial page level in SQL Server. Here you use the traditional RESTORE DATABASE T-SQL command, but you only need to specify the page you want to restore, not the entire database, we just need to restore the page of the original problem. If you are dealing with a large database, it will make a big difference.

1 USE master2-- Restore full database backup3 RESTORE DATABASE PageLevelRestores4 PAGE ='1 FROM DISK 90: 5 FROM DISK ='C:\ Backups\ PageLevelRestores.bak'6 WITH NORECOVERY7 GO

Now comes the tricky part: after RESTORE DATABASE's T-SQL command, you need to do another transaction log backup. This additional log backup is required because next you need to make sure that all changes made on this available page are used for restore. Without this extra log backup, SQL Server can't get your page back online.

1-- Backup the tail of the log...2 BACKUP LOG PageLevelRestores TO3 DISK ='C:\ Backups\ PageLevelRestores_LOG_TAIL.bak'4 WITH INIT5 GO

After doing this extra log backup, you can restore all the log backups in the correct order and bring the database online.

1-- Restore all available log backups in the correct order 2 RESTORE LOG PageLevelRestores FROM 3 DISK ='C:\ Backups\ PageLevelRestores_LOG1.bak' 4 WITH NORECOVERY 5 GO 6 7-Finally restore the tail log backup 8 RESTORE LOG PageLevelRestores FROM 9 DISK ='C:\ Backups\ PageLevelRestores_LOG_TAIL.bak'10 WITH NORECOVERY11 GO12 13-- Finally finish with the restore sequence14 RESTORE DATABASE PageLevelRestores WITH RECOVERY15 GO

Now when you look up the table again, you will see that the SELECT statement was executed successfully without any Imax O errors and no data was lost in this table. It's still simple, isn't it?

1 USE PageLevelRestores2 GO3 4-Retrieve the selected records5 SELECT * FROM Test6 GO

On how to do page-level recovery in SQL Server to share here, I hope that the above content can make you improve. If you want to learn more knowledge, please pay more attention to the editor's updates. Thank you for following the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.