CHECKDB and Page Restoring from SSMS

Did you know that you can run DBCC CHECKDB (WITH PHYSICAL_ONLY) and issue page restores from SSMS (SQL Server Management Studio). I never, and I probably will never because I rather use TSQL – however I have only just seen this ( yes it’s been around for a while ) and felt like blogging about it.

I clicked on restore > page.

ssms

I corrupted page 126 and look it knows about it!

ssmsmain

Yes it’s in bad shape – Level 24 severity.

SELECT *
 FROM [ZoraDB].[dbo].[people]

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa154e798; actual: 0xa144e788). It occurred during a read of page (1:126) in database ID 11 at offset 0x000000000fc000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB.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.

So SSMS is so good it will restore for you – and build the restore chain.

Hitting the SCRIPT button gives us:

USE [master]
RESTORE DATABASE [ZoraDB] PAGE='1:126'
 FROM  DISK = N'C:\SQLSERVER\ZoraDB.bak' WITH  FILE = 1,
 NORECOVERY,  NOUNLOAD,  STATS = 5

-- Tail Log
BACKUP LOG [ZoraDB]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOFORMAT, NOINIT,  NAME = N'ZoraDB_LogBackup_2016-12-15_09-51-17', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 5

RESTORE LOG [ZoraDB]
FROM  DISK = N'C:\SQLSERVER\ZoraLog.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [ZoraDB]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH  NOUNLOAD,  STATS = 5

GO

ssms3

 SELECT *
 FROM [ZoraDB].[dbo].[people]

(21 row(s) affected)

How cool is that?

Carving up CHECKDB

This is a technique that I am using from Paul Randal – concept of splitting CHECKDB: CHECKALLOC, CHECKCATALOG and CHECKTABLE – here we will use “buckets” of tables split across the week.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx

Lets assume Adventureworks is a VLDB 🙂
Day 1 would be the below

 


USE AdventureWorks2012
GO
DBCC CHECKALLOC
GO

DBCC CHECKCATALOG
GO

Now lets take 1 bucket of tables for day 1 for CHECKTABLE.

 


SELECT ''+SCHEMA_NAME(schema_id)+'.'+name+''
AS TableName,	NTILE(7)
				OVER (ORDER BY Name)
				AS [BUCKET] INTO ##CHECKTABLE
FROM sys.tables  WHERE type = 'U'

DECLARE @TABLE  NVARCHAR(100)
DECLARE @SQL  NVARCHAR(MAX)

		DECLARE CHECKTABLE CURSOR FAST_FORWARD
		FOR
			SELECT TableName FROM ##CHECKTABLE
			WHERE BUCKET  = 1

			OPEN CHECKTABLE
			FETCH NEXT FROM CHECKTABLE INTO @TABLE
			WHILE @@FETCH_STATUS=0
				BEGIN
					BEGIN TRY
					SET @SQL = 	'DBCC CHECKTABLE(''' + @TABLE + ''') '
					PRINT @SQL
					EXEC SP_EXECUTESQL @SQL

					END TRY

					BEGIN CATCH

					DECLARE @ErrorMessage NVARCHAR(4000);
					DECLARE @ErrorSeverity INT;
					DECLARE @ErrorState INT;
					SELECT
						@ErrorMessage = ERROR_MESSAGE(),
						@ErrorSeverity = ERROR_SEVERITY(),
						@ErrorState = ERROR_STATE();

						 RAISERROR (
						 		@ErrorMessage, -- Message text.
						 		@ErrorSeverity, -- Severity.
								@ErrorState -- State.

								) WITH LOG;
					END CATCH

					FETCH NEXT FROM CHECKTABLE INTO @TABLE
				end

			CLOSE CHECKTABLE
	DEALLOCATE CHECKTABLE

DBCC CHECKTABLE('Person.Address')
DBCC CHECKTABLE('Person.AddressType')
DBCC CHECKTABLE('dbo.AWBuildVersion')
DBCC CHECKTABLE('Production.BillOfMaterials')
DBCC CHECKTABLE('Person.BusinessEntity')
DBCC CHECKTABLE('Person.BusinessEntityAddress')
DBCC CHECKTABLE('Person.BusinessEntityContact')
DBCC CHECKTABLE('Person.ContactType')
DBCC CHECKTABLE('Person.CountryRegion')
DBCC CHECKTABLE('Sales.CountryRegionCurrency')
DBCC CHECKTABLE('Sales.CreditCard')

Then the 2nd day you could do the 2nd bucket. etc…