-- Setup script for Allocation Unit swap demo

-- Drop old database
USE [master];
GO

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

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

-- Create database
CREATE DATABASE [Company] ON PRIMARY (
    NAME = N'Company',
    FILENAME = N'D:\SQLskills\Company.mdf')
LOG ON (
    NAME = N'Company_log',
    FILENAME = N'D:\SQLskills\Company_log.ldf');
GO

USE [Company];
GO

-- Create a dummy table
CREATE TABLE [Filler1] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [FillerCI] ON [Filler1] ([SalesID]);
GO

-- Create real table
CREATE TABLE [Sales] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY DEFAULT CONVERT (MONEY, 100 * RAND ()));

CREATE CLUSTERED INDEX [SalesCI] ON [Sales] ([SalesID]);
GO

-- Populate the table
SET NOCOUNT ON;
GO

INSERT INTO [Sales] DEFAULT VALUES;
GO 300

-- Create some nonclustered indexes
CREATE NONCLUSTERED INDEX [SalesNCI_CustomerID]
ON [Sales] ([CustomerID]);

CREATE NONCLUSTERED INDEX [SalesNCI_SalesDate_SalesAmount]
ON [Sales] ([SalesDate]) INCLUDE ([SalesAmount]);
GO

-- Create more dummy tables to force another sys.sysrscols page
-- and another sys.sysiscols page
CREATE TABLE [Filler2] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler2CI] ON [Filler2] ([SalesID]);
GO

CREATE TABLE [Filler3] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler3CI] ON [Filler3] ([SalesID]);
GO

CREATE TABLE [Filler4] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler4CI] ON [Filler4] ([SalesID]);
GO

CREATE TABLE [Filler5] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler5CI] ON [Filler5] ([SalesID]);
GO

CREATE TABLE [Filler6] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler6CI] ON [Filler6] ([SalesID]);
GO

CREATE TABLE [Filler7] (
	[SalesID] INT IDENTITY,
	[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),
	[SalesDate] DATETIME DEFAULT GETDATE (),
	[SalesDat2] DATETIME DEFAULT GETDATE (),
	[SalesDat3] DATETIME DEFAULT GETDATE (),
	[SalesDat4] DATETIME DEFAULT GETDATE (),
	[SalesDat5] DATETIME DEFAULT GETDATE (),
	[SalesDat6] DATETIME DEFAULT GETDATE (),
	[SalesDat7] DATETIME DEFAULT GETDATE (),
	[SalesAmount] MONEY);

CREATE CLUSTERED INDEX [Filler7CI] ON [Filler7] ([SalesID]);
GO

-- Create a good backup
BACKUP DATABASE [Company] TO
DISK = N'C:\SQLskills\OldCompany.bck'
WITH INIT;

-- Now corrupt the sys.sysrscols page
ALTER DATABASE [Company] SET SINGLE_USER;
GO

DECLARE @offset INT;
SELECT @offset = 0;

WHILE (@offset < 8185)
BEGIN
	DBCC WRITEPAGE (N'Company', 1, 70, @offset, 8, 0x0000000000000000, 1);
	SELECT @offset = @offset + 8;
END;
GO

ALTER DATABASE [Company] SET MULTI_USER;
GO

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