Archive for January, 2012

SQL Server 2012: Page restore from the SSMS UI

13/01/2012 1 comment

In my last blog post I wrote about various ways to detect damaged pages in the database and how to perform page restores in SQL Server 2005/2008. Same apply to SQL Server 2012. SQL Server 2008 doesn’t have UI support for page restore but SQL Server 2012 Management Studio (SSMS) brings us a nice UI support for page restore. If you prefer to do page restores with UI here are the steps:

    1. In SSMS Object Explorer right click on database -> Tasks -> Restore then click on Page…PR2012_1

Restore Page window will be opened.

2. In Restore Page window, chosen database is selected (1) and Pages grid (2) is automatically populated with damaged pages from msdb.dbo.suspect_pages table (see picture below).


From Restore Page window you can run DBCC CHECKDB (3) to find if there are more damaged pages in the database or you can add additional pages if you want using Add (4) button. You also need to set the location for the log backup (5). Backup sets part (6) shows all backups that will be used to bring data pages up to date with the current log file.

3. Final step is to click OK button and damaged pages are restored. Open-mouthed smile


SQL Server Page Restore

10/01/2012 2 comments

We all know how backup is important. I usually say that restore is more important than backup. We can create backup but if we don’t know how to restore it, or we cannot because it isn’t correct we have nothing. So always check your backups and restore it on different locations. In situation when you need to react quickly restore practice can save you a lot of times and nerves.

In this post I decided to show how to use page restore. Page restore isn’t something that has to be done on a regular basic, but every DBA should be familiar with technique and requirements in case page restore is needed.

Page restore requirements and limitations

Page restore is future available from SQL server 2005 version. Enterprise edition allows online page restore (restore of metadata pages require offline restore) while standard edition support only offline page restore.

Page restore is a nice future especially if you have a large database and a few damaged pages. As with all nice futures, page restore has some requirements and limitations. To be able to use page restore database must to be in full or bulk logged recovery model. Page restore must start with full, file or filegroup backup and there must be an unbroken chain of log backups up to the current log file. All log backups must be applied so that the page is brought up to date with the current log file.

Be aware that page restore cannot be used to restore: transaction log, full-text, allocation pages (GAM, SGAM, PFS, DIFF, ML), file header page (PageId =0 ), boot page (PageId =9).

How to identify damaged pages?

Damaged pages can be identified in multiple ways.

1. Sometimes, executing some queries on database can result in an error 824 (invalid page checksum error).

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbf649245; actual: 0xbf648ec5). It occurred during a read of page (1:80) in database ID 9 at offset 0x000000000a0000 in file ‘D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CorruptDB.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.

Analyzing the error we can see that database with databaseid = 9 has damaged page 80 on file 1 (page 1:80).

2. Damaged pages can be identified with DBCC CHECKDB command. Example results:

Msg 8928, Level 16, State 1, Line 2
Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:80) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:80). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘CorruptTable’ (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘CorruptDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptDB).

3. dbo.suspect_pages table on msdb database. It contains one row per page that failed with an 823 error or an 824 error.


4. Check the SQL Server Error Log, and look for errors related to corrupted pages

How to perform page restore?

To perform page restore we need full backup containing un-damaged pages, differential and all transaction log backups to the current time. In the RESTORE DATABASE statement, we need to use the PAGE clause to list the page IDs of all of the pages to be restored. If we use offline page restore we first need to create backup log with NORECOVERY option. In this example, I will use online page restore.

--Restore database
FROM DISK = 'D:\Backup\CorruptDB_full.bak'
-- Restore log
FROM DISK = 'D:\Backup\CorruptDB_log1.bak'

Processed 1 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE DATABASE ... FILE= successfully processed 1 pages in 0.023 seconds (0.339 MB/sec).
Processed 0 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).

In the example above, full backup with pageid = 80 is restored with option norecovery. Then we restored log with norecovery option. If we try to restore log with RECOVERY option without taking the last backup we would get an error:

Processed 0 pages for database ‘CorruptDB’, file ‘CorruptDB’ on file 1.
The roll forward start point is now at log sequence number (LSN) 43000000026100001. Additional roll forward past LSN 43000000042700001 is required to complete the restore sequence.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).

You can query database and all data are available except damaged page. If we try to query data from table containing damaged page after we restored it with NORECOVERY option we will get an error:


Msg 829, Level 21, State 1, Line 1
Database ID 9, Page (1:80) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

To finalize page restore we need to take log backup and to restore it with RECOVERY option.

-- Backup DB log
TO DISK = 'D:\Backup\CorruptDB_log2.bak'
-- Restore DB log with RECOVERY option
FROM DISK = 'D:\Backup\CorruptDB_log2.bak'

Now we can query our table without error.

Offline page restore procedure is slightly different. With offline restore we first need to take log backup with NORECOVERY option. It will put database to restoring state. Then we need to restore full database using PAGE clause and NORECOVERY option. Then differential and all transaction log backup need to be applied with NORECOVERY option. Last transaction backup, taken with NORECOVERY option, should be restored with RECOVERY option.