-- Demo script for Allocation Unit swap demo

-- Try accessing our table
USE [Company];
GO

SELECT * FROM [Sales];
GO

-- What's up with the database?
DBCC CHECKDB (N'Company') WITH NO_INFOMSGS;
GO

-- What's the broken object?
SELECT OBJECT_NAME (XX);
GO

-- Hmm - can we create tables?
USE [Company];
GO
CREATE TABLE [Dummy] ([c1] INT);
GO

-- Try to salvage our data!

-- First, back up the corrupt database!!!
BACKUP DATABASE [Company] TO
DISK = N'C:\SQLskills\PreFixCompany.bck' WITH INIT;
GO

-- There are no log backups, only an old copy of the database

-- Restore older copy of database
RESTORE DATABASE [Company_Old] FROM
DISK = N'C:\SQLskills\OldCompany.bck'
WITH MOVE N'Company' TO N'D:\SQLskills\Company_Old.mdf',
MOVE N'Company_log' TO N'D:\SQLskills\Company_Old_log.ldf',
REPLACE;
GO

-- Now script out and recreate the Sales table, changing the table
-- name and changing the database name to Company

-- VERY IMPORTANT! If there's a clustered index, we MUST create it
-- otherwise this method will not work as the column orders will be
-- different in the real table and this new one

-- Script out any clustered index and recreate, changing all names
-- and changing the database name to Company

-- Nonclustered indexes are irrelevant to data retrieval here

-- Now we have to swap the page IDs in the sys.sysallocunits table
-- so the fake Sales table points at the IAM chain for the real one

-- Get the page IDs
EXEC sp_allocationMetadata 'Sales';
GO
--replace with saved info

EXEC sp_allocationMetadata 'Sales1';
GO
--replace with saved info

-- Find the row location for the sys.sysallocunits row
-- for the dummy table
SELECT * FROM sys.sysallocunits
cross apply sys.fn_PhysLocCracker(%%physloc%%)
WHERE [auid] = xx;
GO

-- Change to DAC and re-enter Company
USE [Company];
GO

SELECT * FROM sys.sysallocunits
cross apply sys.fn_PhysLocCracker(%%physloc%%)
WHERE [auid] = xx;
GO
--replace with saved location info

-- Get the hex values of the page IDs from the real table
SELECT * FROM sys.sysallocunits
WHERE [auid] = xx;
GO
--replace with saved page ID info


-- DROP THE DAC CONNECTION

-- Let's look at that row
DBCC TRACEON (3604);
DBCC PAGE (N'Company', 1, XX, 3);
GO

-- Note the empty page IDs

-- Offset of that row is: 0x

-- Now swap the first and firstIAM page IDs

-- VERY IMPORTANT: For a clustered index, you must also swap the root page ID

-- First page: offset = row offset + 0x1b for page ID, then +4 for file ID
-- Root page: offset = row offset + 0x21 for page ID, then +4 for file ID
-- FirstIAM page: offset = row offset + 0x27 for page ID, then +4 for file ID

-- ******* Make sure to add in the small offset to the row offset ********
DBCC WRITEPAGE (N'Company', 1, page, offset, 4, 0xfirst8digits) -- then +6
DBCC WRITEPAGE (N'Company', 1, page, offset+4, 1, 0xfirsttwodigits) -- then +6
GO

-- Check that it worked, compare with the values
-- above for the real table
USE [Company]
GO
EXEC sp_allocationMetadata 'Sales1';
GO

-- Now try selecting from the dummy table
SELECT * FROM [Sales1];
GO

-- WHAT? The cached metadata entries still say no rows

-- Blip the database offline and online again
USE [master];
GO

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

ALTER DATABASE [Company] SET MULTI_USER;
GO
ALTER DATABASE [Company] SET OFFLINE;
GO
ALTER DATABASE [Company] SET ONLINE;
GO

-- Try selecting again
USE [Company];
GO
SELECT * FROM [Sales1];
GO

-- Let's look at that page
DBCC TRACEON (3604);
DBCC PAGE (N'Company', 1, XX, 3);
GO

-- We need to change the allocation unit ID on the data pages
-- Plug in the allocation unit ID of the dummy table
DECLARE @alloc BIGINT = dummytableallocunitID;
DECLARE @index BIGINT;
SELECT @index =
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc)
            * (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
    );
SELECT
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
            * (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
    ) AS [m_objId],
    @index AS [m_indexId];
GO

-- These numbers are in decimal
--replace with calculated values

-- Substitute those values on each data/index page
-- idobj at offset 24 for 4 bytes, idind at offset 6 for 2 bytes

-- Use a calculator to convert the numbers to hex, and then byte-reverse them
DBCC WRITEPAGE (N'Company', 1, page, 24, 4, 0xobjid8digits);
DBCC WRITEPAGE (N'Company', 1, page, 6, 2, 0xindid4digits);
GO

-- Try selecting again
USE [Company];
GO
SELECT * FROM [Sales1];
GO

-- This gets tedious, so write a script to follow the page ID links and 
-- do the DBCC WRITEPAGE changes en masse.

-- If you see no rows, try DBCC PAGE. If that shows rows,
-- you forgot to change the dummy table's root page ID

-- More complicated: when there is off-row LOB data:
--  - Need to plug in all the page IDs for those allocation units
--  - Need to change all those page's obj and ind IDs to the off-row
--    allocation unit IDs


