I am in a fix-it mood so for this blog I am going to corrupt a page and then show you how to recover it using a page restore.
So let’s begin.
This is a very basic setup just to highlight the steps involved.
CREATE DATABASE [fixit]; GO USE [fixit] GO ALTER DATABASE [fixit] SET RECOVERY FULL GO CREATE TABLE [xbox] ( [c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a'); GO INSERT INTO [xbox] DEFAULT VALUES; GO
Let’s do some backups
USE master GO BACKUP DATABASE [fixit] TO DISK = 'C:\sqlserver\fixitfull11.bak' GO BACKUP LOG [fixit] TO DISK = 'C:\sqlserver\fixitLOG11.bak'
Let’s look at DBCC IND to get some pageIDs
DBCC IND (N'fixit', N'xbox', -1); GO
Trash it
I am going to trash data page 78 (type 1) using DBCC WRITEPAGE – This is a pretty dangerous command – DO NOT USE IT IN PRODUCTION. Actually, don’t use it all if you are not comfortable with it! This is going to be executed on my laptop, my hardware using my software – so I accept any consequence…..
ALTER DATABASE fixit SET SINGLE_USER; GO DBCC WRITEPAGE (N'fixit', 1, 78, 4000, 1, 0x45, 1); GO ALTER DATABASE fixit SET MULTI_USER; GO
SELECT * FROM [fixit].[dbo].[xbox]
Msg 824, Level 24, State 2, Line 37
SQL Server detected a logical consistency-based I/O error:
It occurred during a read of page (1:78) in database ID 22 at offset 0x0000000009c000
DBCC CHECKDB ('Naughty') WITH NO_INFOMSGS, ALL_ERRORMSGS
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘xbox’ (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘fixit’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (fixit).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As a side note this will get recorded into the suspect_pages table within msdb.
SELECT * FROM [msdb].[dbo].[suspect_pages]
Some more general activity.
USE [fixit]
go
INSERT INTO [dbo].[xbox]
([c2])
VALUES
('b')
GO
We want the data back!
Recovery time
First step is to issue a tail-log backup.
USE master
GO
-- tail
BACKUP log [fixit] TO DISK = 'C:\sqlserver\fixitLOG12.bak'
--So now we start the page recovery
RESTORE DATABASE [fixit]
PAGE = '1:78'
FROM DISK = 'C:\sqlserver\fixitfull11.bak'
WITH NORECOVERY
GO
RESTORE LOG [fixit] FROM
DISK = 'C:\sqlserver\fixitLOG11.bak'
WITH NORECOVERY
GO
RESTORE LOG [fixit] FROM
DISK = 'C:\sqlserver\fixitLOG12.bak'
WITH NORECOVERY
GO
-- Finally bring it back
RESTORE DATABASE [fixit] WITH RECOVERY
DBCC CHECKDB('Fixit')
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fixit’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
USE [fixit] GO SELECT * FROM [dbo].[xbox]
Just be aware of certain limitations such as that allocation pages Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages cannot be recovered. More information can be found at https://msdn.microsoft.com/en-us/library/ms175168.aspx.
Happy fixing!
All About Tech 