SQL Server Automatic Page Repair Script

The estimated reading time for this post is 3 minutes

Database page level corruption in SQL Server is the worst trouble that any SQL Server professional can face. In any environment, either small or enterprise, the integrity and availability of the data cannot be compromised. This is especially the case in those organizations that totally rely on their OLTP systems, for example a high-volume website. SQL Server database corruption can cause business repercussions such as financial losses, a drop in reputation or contractual SLA problems due to disruption of the transaction processing system.

Fortunately, there are a few was to identify corruption in the database such as DBCC CheckDB, sys.suspect_pages view and error message 824 which is related to Logical I/O Error in SQL Server. Take note that DBCC CheckDB is able to repair the corrupted page with REPAIR_REBUILD option, in case it is not able to rebuild, then DBA can use REPAIR_ALLOW_DATA_LOSS option IF THERE IS NOT A VALID DATABASE BACKUP.

“Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.” — MSDN

SQL Server Automatic Page Repair Script

I have prepared a script to restore corrupted page from the valid backup automatically without needs of DBA action. Below TSQL statement accepts the Full backup, transaction log backup filenames and then generate and execute the page recovery statements.

-- Automatic Page Repair Script
IF OBJECT_ID(N'#header') IS Null
	DROP TABLE #header;
DECLARE @taillogFile NVARCHAR(400);
SET @taillogFile = N'<Tail Log Backup filename>';
DECLARE @dbName sysname;
SET @dbName = N'<Corrupted DB Name>';
DECLARE @backupFile NVARCHAR(400);
SET @backupFile = N'<Full Database Backup Filename>;;
If @taillogFile is Null
	Raiserror('The @taillogFile VARIABLE should not be Null.',16,1);
create table #header(BackupName nvarchar(128),
					BackupDescription nvarchar(max),
					BackupType int,
					ExpirationDate datetime,
					Compressed int,
					Position int,
					DeviceType int,
					UserName nvarchar(max),
					Servername nvarchar(max),
					DatabaseName nvarchar(128), 
					DatabaseVersion int,
					DatabaseCreationDate datetime,
					BackupSize bigint,
					FistLSN nvarchar(max),
					LastLSN nvarchar(max),
					CheckpointLSN nvarchar(max),
					DatabaseBackupLSN nvarchar(max),
					BackupStartDate datetime,
					BackupFinishDate datetime,
					SortOrder int,
					[CodePage] int,
					UnicodeLocaleId int,
					UnicodeComparisonStyle int,
					CompatibilityLevel int,
					SoftwareVendorId int, 
					SoftwareVersionMajor int,
					SoftwareVersionMinor int,
					SoftwareVersionBuild int,
					MachineName nvarchar(128),
					Flags int,
					BindingID nvarchar(max),
					RecoveryForkID nvarchar(max),
					Collation nvarchar(128),
					FamilyGUID uniqueidentifier,
					HasBulkLoggedData bit,
					IsSnapshot bit,
					IsReadOnly bit,
					IsSingleUser bit,
					HasBackupChecksums bit,
					IsDamaged bit,
					BeginsLogChain bit, 
					HasIncompleteMetaData bit,
					IsForceOffline bit,
					IsCopyOnly bit,
					FistRecoveryForkID nvarchar(max),
					ForkPointLSN nvarchar(max),
					RecoveryModel nvarchar(max),
					DifferentialBaseLSN nvarchar(max),
					DifferentialBaseGUID uniqueidentifier,
					BackupTypeDescription nvarchar(max),
					BackupSetGUID uniqueidentifier,
					CompressedBackupSize bigint ,/*SQL Server 2012 Only*/
					Containment bit /*SQL Server 2012 Only*/);
Insert Into #header Exec('RESTORE HeaderOnly FROM DISK = '''+@backupFile+'''');
-- Generate Restore Log Statement.
Declare @restoreLogStmt nvarchar(max), @position int;
Declare Cur Cursor For Select Position From #header where BackupType = 2 and DatabaseName = @dbName order by Position Asc;
Open CUR;
Fetch Next From CUR Into @position;
While (@@Fetch_Status = 0)
	If(Len(@restoreLogStmt) Is Null)
		Set @restoreLogStmt = N'
RESTORE LOG '+@dbname+' FROM DISK = '''+@backupFile+''' WITH FILE = '+Cast(@position as nchar(4))+',norecovery;';
		Set @restoreLogStmt = @restoreLogStmt + N'
RESTORE LOG '+@dbname+' FROM DISK = '''+@backupFile+''' WITH FILE = '+Cast(@position as nchar(4))+',norecovery;';
Fetch Next From CUR Into @position;
Close CUR;
Deallocate CUR;
--For Debug Purposes
--print @restoreLogStmt;
-- Generate Restore Database Statement
Declare @pages nvarchar(max);
Declare @fileid int,@pageid int;
Declare CUR2 Cursor For select file_id,page_id from msdb..suspect_pages where database_id = db_id(@dbName);
Open CUR2;
Fetch Next From CUR2 Into @fileid,@pageid;
While(@@Fetch_Status = 0)
	If(Len(@pages) is Null)
		Set @pages = Cast(@fileid as nvarchar(5))+':'+Cast(@pageid as nvarchar(5));
		Set @pages = @pages+ ','+Cast(@fileid as nvarchar(5))+':'+Cast(@pageid as nvarchar(5));
	Fetch Next From CUR2 Into @fileid,@pageid;
Close CUR2;
Deallocate CUR2;
Declare @stmt nvarchar(max);
Set @stmt = N'
USE Master;
RESTORE DATABASE '+@dbName+' page = '''+@pages+''' FROM DISK = '''+@backupFile+''' WITH norecovery;'+@restoreLogStmt+'
BACKUP LOG ' + @dbName + ' TO DISK = '''+@taillogFile+''';
RESTORE LOG '+ @dbName+ ' FROM DISK = '''+@taillogFile+''';
DELETE FROM msdb..suspect_pages WHERE database_id = DB_ID('+'N'''+@dbName+''')';
select @stmt;
Exec (@stmt);



Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:

Leave a Comment

Be the First to Comment!

Notify of