Warning, this is a longer post from me than usual.
The title is a mouthful and so is this post. In the past I have linked to blog posts from Microsoft that say consistency checks for Azure SQL Database is the responsibility of Microsoft. (https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/)
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.
I corrupted page 126 and look it knows about it!
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
SELECT * FROM [ZoraDB].[dbo].[people] (21 row(s) affected)
How cool is that?
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.
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…
All About Data 