/*============================================================================
  File:     CreditPTUSB-QueryDamageAccessRepair.sql

  Summary:  This script takes roughly 5-15 mins to run, 
	    depending on hardware. This script will setup 
	    for the Damaged Database using Partitioned Tables. 
	    Please review this script if you plan to try to 
	    setup/configure this!

  Date: November 2005

  Version: SQL Server 2005 RTM
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [CreditPTUSB]
GO

----------------------------------------------------------------------------------------------
------------------------------ Access Data - Query -------------------------------------------
----------------------------------------------------------------------------------------------
-- Show data access...
-- June 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040601'
go
-- July 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040701'
go
-- Aug 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040801'
go
-- Sep 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040901'
go

--SELECT * FROM ChargesPT
--WHERE Charge_dt >= '20040914 10:50:02' AND Charge_dt < '20040915 10:50:05' 
-- 13424 rows

----------------------------------------------------------------------------------------------
------------------------------ Damage and Access Data ----------------------------------------
----------------------------------------------------------------------------------------------
-- Now - damage the harddrive......
-- First, I'll remove the USB Key

-- June 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040601'
go
-- July 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040701'
go
-- Aug 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040801'
go
-- Sep 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040901'
go

-- Why aren't my queries failing? 
-- Remember, SQL Server **ALWAYS** goes to MEMORY first.
-- If it finds the data necessary, then it returns that data from memory. What will 
-- cause us to be aware of the problem? A write to that portion of the database?
-- Right?

UPDATE ChargesPT
	SET provider_no = 111 
	where charge_no = 611015
go










-- NOPE, even that hasn't failed.....
-- Hmmmm, what's going on????















CHECKPOINT

-- OK, CHECKPOINT generates:
-- Msg 823, Level 24, State 3, Line 1
-- The operating system has reported I/O error 1006(The volume for a file 
-- has been externally altered so that the opened file is no longer valid.) 
-- to the SQL Server process during a write at offset 0x0000000000c000 in 
-- file 'G:\CreditPTUSBFG3File1.ndf'. Additional messages in 
-- the SQL Server error log and system event log may provide more detail. 
-- This is a severe system-level 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.

----------------------------------------------------------------------------
-- Ironically (or is it?) queries STILL work!
-- Aug 2004
SELECT TOP 100 * 
FROM dbo.ChargesPT 
WHERE Charge_dt >= '20040801'
go
-- Notice that the provider_no for charge_no 611015 is correct at 111!!!!

----------------------------------------------------------------------------------------------
------------------------------ Repair Database -----------------------------------------------
----------------------------------------------------------------------------------------------

-- So, the database seems somewhat unaffected except for checkpoint (to that portion
-- of the database, etc.). But we definitely need to recover. Turns out that a full 
-- database backup was done at the end of the setup script (and the assumption is that
-- you would have a regular backup strategy in place.....?). So how about we recovery
-- and restore the damaged file from our backup(s)!

-- Reviewing system tables to see filenames, paths, etc.
USE CreditPTUSB
go

SELECT * FROM sys.filegroups
SELECT * FROM sys.database_files -- looking for STATE
go

-- Nothing seems to know about this error? But - they do!
-- All damaged pages are logged to a system table in msdb

--SELECT * 
--FROM msdb.dbo.suspect_pages
--file_id	database_id	page_id	event_type	error_count	last_update_date
--      5	         12	      8	         1	          1	2005-07-07 03:57:23.780

--SELECT db_name(database_id), file_name(file_id)
--	, page_id, event_type AS [Error Type]
--	, CASE event_type
--		WHEN 1 THEN 'SHORT_TRANSFER'
--		WHEN 2 THEN 'BAD_CHECKSUM'
--		WHEN 3 THEN 'TORN_PAGE'
--		WHEN 4 THEN 'INCORRECT_PAGE_ID'
--		WHEN 5 THEN 'RESTORED'
--		WHEN 6 THEN 'REPAIRED'
--		WHEN 7 THEN 'VERIFIED_OR_DEALLOCATED'
--	 END AS [Error Description]
--	, error_count, last_update_date
--FROM msdb.dbo.suspect_pages

-- Check out the BOL on the suspect_pages table: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f394d4bc-1518-4e61-97fc-bf184d972e2b.htm
-- And for the event_types use: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/119c8d62-eea8-44fb-bf72-de469c838c50.htm
GO

-- Since this file is only showing more of a "soft error" rather 
-- then a hard error, we need to investigate further. BUT - we 
-- already know this is damaged and now we need to take the file 
-- officially offline.

-- Let's check state first:
SELECT file_id, name, physical_name, state_desc 
FROM sys.database_files
go

ALTER DATABASE [CreditPTUSB]
	MODIFY FILE (NAME = N'CreditPTUSBFG3File1', OFFLINE)
GO
-- Returns TWO messages. One to disconnect users to validate 
-- database state (they can immediatelly reconnect)
-- The other is again a disk error (because they couldn't contact
-- the file when taking it offline). This is not a problem 
-- as we're going to restore.

SELECT file_id, name, physical_name, state_desc 
FROM sys.database_files
go

----------------------------------------------------------------------------------------------
------------------------------ Access Data - Query -------------------------------------------
----------------------------------------------------------------------------------------------
-- June 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040601'
go
-- July 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040701'
go
-- Aug 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040801'
-- Msg 667, Level 16, State 1, Line 2
-- Index 'ChargesPTPK' for table 'ChargesPT' (RowsetId ....) 
-- resides on a filegroup that cannot be accessed because it is offline, 
-- restoring, or defunct.
go
-- Sep 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040901'
go

-- If you want to investigate what is damaged:
--SELECT * FROM sys.partitions
--WHERE hobt_id = 72057594038845440
--
----	SELECT * 
----	FROM sys.allocation_units AS au
----		JOIN sys.partitions AS p
----			ON au.container_id = p.partition_id
----	WHERE p.[object_id] = object_id('ChargesPT')
--
--SELECT * FROM sys.dm_db_partition_stats 
--WHERE [object_id] = object_id('ChargesPT')

----------------------------------------------------------------------------------------------
------------------------------ Restore Damaged FILE from Backup ------------------------------
----------------------------------------------------------------------------------------------
USE Master
GO

RESTORE DATABASE [CreditPTUSB]
	FILE = N'CreditPTUSBFG3File1'
FROM DISK = N'C:\SQLskills\CreditPTUSB.bak' 
WITH FILE = 1, 
	MOVE N'CreditPTUSBFG3File1' TO N'C:\SQLskills\CreditPTUSBFG3File1.ndf', 
	NORECOVERY
go

----------------------------------------------------------------------------------------------
------------------------------ Backup the Tail -----------------------------------------------
----------------------------------------------------------------------------------------------
-- You must backup the tail of the log AFTER the file has been taken offline
-- otherwise the sync lsn will not match and you won't be able to bring the file
-- back online!

BACKUP LOG [CreditPTUSB]
	TO DISK = N'C:\SQLskills\CreditPTUSBLOG.bak' 
	WITH INIT, NO_TRUNCATE
GO

-- Now restore the tail
RESTORE LOG [CreditPTUSB]
FROM DISK = N'C:\SQLskills\CreditPTUSBLOG.bak' 
	WITH FILE = 1, RECOVERY
go


----------------------------------------------------------------------------------------------
------------------------------ Access Data - Query -------------------------------------------
----------------------------------------------------------------------------------------------
USE CreditPTUSB
go
-- June 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040601'
go
-- July 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040701'
go
-- Aug 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040801'
go
-- Sep 2004
SELECT top 100 * FROM dbo.ChargesPT 
	WHERE Charge_dt >= '20040901'
go
