IAM Page Error – Fix it with a page restore

Today I have been playing around with situations, where your IAM page get corrupt, and I have tried to figure out what to do, and let me say it like this: If you have a valid backup then use that, and depending on the situation consider using page restore.

NOTE!! THIS BLOG IS TECHNICAL AND THERE ARE THINGS THAT YOU SHOULD NOT DO UNLESS YOU ARE 100% SURE ABOUT WHAT YOU DO. WE TAKE NO RESPONSIBILITY IF YOU TRY WHAT WE WRITE IN THE BLOG.

So, lets get started. I want to create a simple database, where I create one table, and I will corrupt that tables IAM Page. Basically that means: You can’t access your data.

So, lets start the blog by setting up a new database, a table and fill in 1.000 rows.

use master

go

 

if DATABASEPROPERTYEX(‘PageRestore’,‘Version’)>0

drop database pagerestore

 

create database Pagerestore

go

 

use PageRestore

go

 

create table LargeTable(id intidentityname char(4000))

 

go

 

insert into Large Table Values (‘Soak AS’)

go 1000

 

So, now we have a database called PAGERESTORE and a table called LARGETABLE with 1.0000 rows.

What I want to see now, is what pages the table LargeTable occupies:

dbcc ind(PageRestore largetable,1)

go

 

On my computer the result is something like.

Skærmbillede 2013 04 07 kl 17 54 51

I can see that page 119 in file 1 has Null in IAMFID and IAMPID (and that PageType = 10), and then I know that it is a IAM Page.

Before we start, lets do a backup (create the physical folder C:\SQL

BACKUP DATABASE [Pagerestore] TO  DISK=N’c:\sql\pagerestore.bak’ WITH FORMATINIT,  NAME=N’Pagerestore-Full Database Backup’,   STATS= 20

go 

After you have taken the backup, then we corrupt the IAM page. NOTE!!! DON’T DO THIS ON A PRODUCTSERVER!!!!!! NEVER!!!!

use master

go

alter database page restore set single_user with no_wait

 

dbcc writepage(PageRestore, 1, 119, 200, 1, 0x50,1)

alter database pagerestore set multi_user with no_wait

DBCC WritePage is an undocumented command, that writes values to the physical page. Imagine what that does to a database. Terrible thins, so don’t use it, unless you really want to get into troubles. I assume that your IAM page is at page 119. If not, then change the number 119. 200 is byte 200 on the page. It was just a random byte, to destroy the page.

Now….. try to run DBCC CHECKDB. It willnot look nice.

Skærmbillede 2013 04 07 kl 18 02 34

and at the bottom of the output, I can see the following:

Skærmbillede 2013 04 07 kl 18 03 10

I think the most important thing is the second line from the bottom. It says that the minimum repair level is Reparir_allow_data_loss, which means that if I run checkDB to fix the error, then I will loose the data in my table (basically).

If I try to run query against the table LargeTable, I will get an error:

Skærmbillede 2013 04 07 kl 18 05 13 

So basically… I am in deep trouble.

Lucky for me… I have a backup, so what I can do is to restore that backup, and it is very simple with SQL 2012. Right click the database –> chose tasks –> choose restore –> Choose page, and the following dialog will occur:

Skærmbillede 2013 04 07 kl 18 08 21

Choose Check database Pages, and if no backup is selected, then select it. PLEASE NOTE!!! There might be some difficulties with selecting backup’s but when you have the right backups, then just click OK.

What happens, and what does everybody with SQL 2012 need to do and understand?

Well, I could restore a single page by writing:

RESTORE DATABASE [Pagerestore] PAGE=‘1:119’ FROM  DISK=N’c:\sql\pagerestore.bak’ WITH  FILE= 1,  NORECOVERY

 

And what happens, then is that the page is marked with Recovery pending. That is because we must restore the necessary log backups, and before we do that we must start taking a backup of the tail of the database. So, to restore a page, we now know that we need these three steps:

1) Restore the page.

2) Backup the tail of the log.

3) Restore necessary log backups, including the tail of the log.

So the complete steps, if you don’t have SQL 2012 and a nice UI will be:

USE[master]

go

RESTORE DATABASE [Pagerestore] PAGE=‘1:119’ FROM  DISK=N’c:\sql\pagerestore.bak’ WITH  FILE= 1,  NORECOVERY

backup log[Pagerestore] to disk=N’c:\sql\pagerestore.trn’ with format

restore log [Pagerestore] from disk=N’c:\sql\pagerestore.trn’


If you have done everything correctly, then you should be able to run DBCC CHECKDB and to query the table 🙂

that’s it. Very easy, but of course there are difficulties. But now you have the general perspective and overview.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s