-- Demo script for DBCC WRITEPAGE Examples demo.

-- Demo databases can be downloaded from
-- http://bit.ly/10fKpbS (that's a zero).

-- Download the 2008/2014 SalesDB Sample Database from the link above

-- Restore as follows:
USE [master];
GO

IF DATABASEPROPERTYEX (N'SalesDB_Snapshot', N'Version') > 0
BEGIN
	DROP DATABASE [SalesDB_Snapshot];
END
GO
IF DATABASEPROPERTYEX (N'SalesDB', N'Version') > 0
BEGIN
	ALTER DATABASE [SalesDB] SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [SalesDB];
END
GO

RESTORE DATABASE [SalesDB]
FROM DISK = N'D:\SQLskills\DemoBackups\SalesDB2014.bak'
WITH
    MOVE N'SalesDBData' TO N'D:\SQLskills\SalesDB.mdf',
	MOVE N'SalesDBLog' TO N'D:\SQLskills\SalesDB.ldf';
GO

-- Change the column type and rebuild the index
USE [SalesDB];
GO

ALTER TABLE [products] ALTER COLUMN [price] FLOAT;
GO
ALTER INDEX [productsPK] ON [products] REBUILD;
GO

-- What pages are there?
DBCC IND (N'SalesDB', N'Products', -1);
GO

-- How to engineer a data purity error

-- Pick the page two up from the bottom
-- Check slot 23 is product ID 306
DBCC TRACEON (3604)
DBCC PAGE (N'SalesDB', 1, xx, 3);
GO

-- Look for the float offset: 8

-- Take the slot 23 offset and add 8, convert to an int
SELECT CONVERT (INT, 0xYYY + 8);
GO

-- Corrupt slot 23's float value record
DBCC WRITEPAGE (N'SalesDB', 1, xx, offset, 8, 0xFFFFFFFFFFFFFFFF);
GO

DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;
GO

-- Now let's mess with a page ID in the page header
-- What pages are there?
DBCC IND (N'SalesDB', N'Products', -1);
GO

-- Pick a page to corrupt
DBCC PAGE (N'SalesDB', 1, xx, 2);
GO

-- Pick a page and figure out its hex page ID using calc
-- Where's the page ID in the header?

-- Corrupt the page ID
DBCC WRITEPAGE (N'SalesDB', 1, xx, offset, 2, 0xzzzz);
GO

-- Did we corrupt it?
DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;
GO


-- Optional for you to play with
-- What about something nasty?
DBCC WRITEPAGE (N'tempdb', 1, 9, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 2, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 3, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 1, 0, 4, 0x00000000);
GO

-- We have to execute this twice
DBCC WRITEPAGE (N'tempdb', 1, 9, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 2, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 3, 0, 4, 0x00000000);
DBCC WRITEPAGE (N'tempdb', 1, 1, 0, 4, 0x00000000);
GO

USE [tempdb];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

-- Prior to latest SQL Server 2014 release, this will crash SQL Server
-- If this doesn't work, try refreshing Management tab in Object Explorer
-- Randomly, SQL Server will crash.
CREATE TABLE [tempdb].[dbo].[test] ([c1] INT);
INSERT INTO [tempdb].[dbo].[test] VALUES (1);
GO

CREATE TABLE [tempdb].[dbo].[test] ([c1] INT);
INSERT INTO [tempdb].[dbo].[test] VALUES (1);
GO

DBCC CHECKDB (tempdb);
GO

-- Restart and look in the error log