/*============================================================================
  File:     CreditPTwDatabaseSnapshots.sql

  Summary:  This script uses the Partitioned Table database to show how database
			snapshots need to be created as well as how sparse file population occurs.

  Date:     June 2005

  SQL Server Version: 9.00.1116.08 (April CTP)
------------------------------------------------------------------------------
  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.
============================================================================*/

-- Before this script will work you will need to:
-- (1) Create a C:\SQLTemp subdirectory.
--		Copy the CreditBackup80.bak file (from the zip) to this directory.
--		You can find the zip on the Past Events page from SQLskills.com.
-- (2) Create the multiple filegroup and partitioned table scenario
--		using the script: "CreditPTOnDriveC.sql" 
-- Once CreditPT is created, you can use this script to create a snapshot!
-- NOTE: This script has many comments which are not commented out...
-- In other words, don't try to just "execute" this script, you should
-- work your way through it slowly!

USE CreditPT
go

-- Make sure to look at every data file's size.
sp_helpfile
go

----------------------------------------------------------------------------------------------
---------------------------------- Create Database Snapshot ----------------------------------
----------------------------------------------------------------------------------------------

-- Note: Every data file must have a valid path, 
-- preferrably on a separate NTFS harddrive. 
-- Notice how fast this "database" gets created.

CREATE DATABASE [CreditSnap1] 
ON  PRIMARY 
( NAME = N'CreditData'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1Data.mdfss')
,
( NAME = N'CreditPTChargesStaging'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1ChargesStaging.ndfss')
,
( NAME = N'CreditPTFG1File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1PTFG1File1.ndfss')
,
( NAME = N'CreditPTFG2File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1PTFG2File1.ndfss')
,
( NAME = N'CreditPTFG3File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1PTFG3File1.ndfss')
,
( NAME = N'CreditPTFG4File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap1PTFG4File1.ndfss')
AS SNAPSHOT OF CreditPT
GO

----------------------------------------------------------------------------------------------
------------------------------- Verify Database Snapshot Files -------------------------------
----------------------------------------------------------------------------------------------

SELECT db_name(v.database_id) AS [Database Name]
	, v.file_id AS [File ID]
	, f.[name] AS [Database File Name]
	, CASE 
			WHEN is_sparse = 0 THEN 'No'
			ELSE 'Yes' 
	  END AS [Sparse File]
	, f.[name] AS [File Name]
	, v.size_on_disk_bytes/1024 AS [PHYSICAL Size (KB)]
	, f.[size]*8/1024 AS [LOGICAL Size (MB)]
	, f.physical_name AS [Physical File Name]
FROM sys.dm_io_virtual_file_stats(db_id('CreditSnap1'), -1) AS v
	JOIN sys.master_files AS f ON v.file_id = f.file_id
WHERE f.database_id = v.database_id
	AND f.database_id = db_id('CreditSnap1')
go

-- When there are no open trans, creating a database snapshot is immediate!
-- sizes of files (all SHOW the same - in sys.master_files, etc. but using 
-- the DMV you can get better information about their on disk structure)

--CreditData		384k
--CreditLog			n/a
--CreditPTFGFile1	64k
--CreditPTFGFile2	64k
--CreditPTFGFile3	64k
--CreditPTFGFile4	64k
--ChargesStaging	64k

----------------------------------------------------------------------------------------------
------------------------------ Change some of source DB's data -------------------------------
----------------------------------------------------------------------------------------------

-- Make a data change to a VERY nicely isolated set of data:
USE CreditPT
go

UPDATE ChargesPT
	SET charge_amt = 20040806
	WHERE charge_dt >= '20040806' 
		AND charge_dt < '20040807'
go

-- CreditPTFGFile3	768k (copy on write)

----------------------------------------------------------------------------------------------
-------------------------- Review Data between source and snapshot ---------------------------
----------------------------------------------------------------------------------------------

SELECT Snap.Charge_no
	, Snap.Charge_amt AS [Snapshot Value in CreditSnap1]
	, Curr.Charge_amt AS [Current Value in CreditPT]
FROM [CreditSnap1].dbo.ChargesPT AS Snap
	JOIN CreditPT.dbo.ChargesPT AS Curr
		ON Snap.charge_no = Curr.charge_no
WHERE Curr.charge_dt >= '20040806' AND Curr.charge_dt < '20040807'
	AND Snap.charge_dt >= '20040806' AND Snap.charge_dt < '20040807'
go


----------------------------------------------------------------------------------------------
------------------------------ Change more of source DB's data -------------------------------
----------------------------------------------------------------------------------------------

-- Again, only August Data - but notice we won't commit!

BEGIN TRAN
UPDATE ChargesPT
	SET charge_amt = 20040807
	WHERE charge_dt >= '20040807' and charge_dt < '20040808'

SELECT db_name(v.database_id) AS [Database Name]
	, v.file_id AS [File ID]
	, f.[name] AS [Database File Name]
	, CASE 
			WHEN is_sparse = 0 THEN 'No'
			ELSE 'Yes' 
	  END AS [Sparse File]
	, f.[name] AS [File Name]
	, v.size_on_disk_bytes/1024 AS [PHYSICAL Size (KB)]
	, f.[size]*8/1024 AS [LOGICAL Size (MB)]
	, f.physical_name AS [Physical File Name]
FROM sys.dm_io_virtual_file_stats(db_id('CreditSnap1'), -1) AS v
	JOIN sys.master_files AS f ON v.file_id = f.file_id
WHERE f.database_id = v.database_id
	AND f.database_id = db_id('CreditSnap1')
go

-- CreditPTFGFile3	1.31mb

ROLLBACK TRAN
-- CreditPTFGFile3	1.31mb (copy before write, not reverted - yes, it's duplicated...
-- not a big deal - only the first change does the copy... and now that the pages are 
-- already there it won't need to do the change if they really change later.)

SELECT db_name(v.database_id) AS [Database Name]
	, v.file_id AS [File ID]
	, f.[name] AS [Database File Name]
	, CASE 
			WHEN is_sparse = 0 THEN 'No'
			ELSE 'Yes' 
	  END AS [Sparse File]
	, f.[name] AS [File Name]
	, v.size_on_disk_bytes/1024 AS [PHYSICAL Size (KB)]
	, f.[size]*8/1024 AS [LOGICAL Size (MB)]
	, f.physical_name AS [Physical File Name]
FROM sys.dm_io_virtual_file_stats(db_id('CreditSnap1'), -1) AS v
	JOIN sys.master_files AS f ON v.file_id = f.file_id
WHERE f.database_id = v.database_id
	AND f.database_id = db_id('CreditSnap1')
go

-- File size has not changed! Data isn't different either (re: Rollback)

SELECT Snap.Charge_no, Snap.Charge_amt, Curr.Charge_amt
FROM [CreditSnap1].dbo.ChargesPT AS Snap
	JOIN CreditPT.dbo.ChargesPT AS Curr
		ON Snap.charge_no = Curr.charge_no
WHERE Curr.charge_dt >= '20040807' AND Curr.charge_dt < '20040808'
	AND Snap.charge_dt >= '20040807' AND Snap.charge_dt < '20040808'
go

----------------------------------------------------------------------------------------------
------------------------------ Change more of source DB's data -------------------------------
-------------------------------- Create a second DB snapshot ---------------------------------
----------------------------------------------------------------------------------------------

-- Again, only August Data - but notice we won't commit!

BEGIN TRAN
UPDATE ChargesPT
	SET charge_amt = 20040808
WHERE charge_dt >= '20040808' and charge_dt < '20040809'
-- CreditPTFGFile3	1.93mb

-- FROM ANOTHER WINDOW: Create another snapshot while the tran 
-- is in flight...

ROLLBACK TRAN

-- In the snapshot they will determine a "sync lsn" to determine the point to 
-- which the snapshot should be rolled back. On the database snapshot creation
-- they will copy the pages that need to be modified and rollback the transaction
-- in the database snapshot.
-- Again, this is duplicated information but is the consistent image of the database
-- at the time the snapshot was created. Essentially, this gives you a point in time
-- image of the database at the time the snapshot is created.
-- Again, even with a rollback - the data is not reverted.

SELECT db_name(v.database_id) AS [Database Name]
	, v.file_id AS [File ID]
	, f.[name] AS [Database File Name]
	, CASE 
			WHEN is_sparse = 0 THEN 'No'
			ELSE 'Yes' 
	  END AS [Sparse File]
	, f.[name] AS [File Name]
	, v.size_on_disk_bytes/1024 AS [PHYSICAL Size (KB)]
	, f.[size]*8/1024 AS [LOGICAL Size (MB)]
	, f.physical_name AS [Physical File Name]
FROM sys.dm_io_virtual_file_stats(db_id('CreditSnap2'), -1) AS v
	JOIN sys.master_files AS f ON v.file_id = f.file_id
WHERE f.database_id = v.database_id
	AND f.database_id = db_id('CreditSnap2')
go

----------------------------------------------------------------------------------------------
------------------------------ Change more of source DB's data -------------------------------
---------------------------- What happens with multiple snapshots ----------------------------
----------------------------------------------------------------------------------------------

-- Now that we have two snapshots - what happens with a modification?
-- Change some of September

UPDATE ChargesPT
	SET charge_amt = 20040914
	WHERE charge_dt >= '20040914' AND charge_dt < '20040915'
--Snap1 - CreditPTFGFile4	704k
--Snap2 - CredtiPTFGFile4	704k

SELECT db_name(v.database_id) AS [Database Name]
	, v.file_id AS [File ID]
	, f.[name] AS [Database File Name]
	, CASE 
			WHEN is_sparse = 0 THEN 'No'
			ELSE 'Yes' 
	  END AS [Sparse File]
	, f.[name] AS [File Name]
	, v.size_on_disk_bytes/1024 AS [PHYSICAL Size (KB)]
	, f.[size]*8/1024 AS [LOGICAL Size (MB)]
	, f.physical_name AS [Physical File Name]
FROM sys.dm_io_virtual_file_stats(NULL, -1) AS v
	JOIN sys.master_files AS f ON v.file_id = f.file_id
WHERE f.database_id = v.database_id
	AND f.[name] LIKE '%4%'
	AND f.database_id IN (db_id('CreditSnap1'), db_id('CreditSnap2'))
go

----------------------------------------------------------------------------------------------
---------------------------------- Revert Back to Original -----------------------------------
----------------------------------------------------------------------------------------------

-- revert back to unmodified version...
USE master
go

RESTORE DATABASE CreditPT
	FROM DATABASE_SNAPSHOT = 'CreditSnap1'
--Msg 3137, Level 16, State 4, Line 1
--Database can not be reverted. Either the primary 
-- or the snapshot names are improperly specified, all other snapshots have not 
--been dropped, or there are missing files.
--Msg 3013, Level 16, State 1, Line 1
--RESTORE DATABASE is terminating abnormally.

DROP DATABASE CreditSnap2
go

RESTORE DATABASE CreditPT 
FROM DATABASE_SNAPSHOT = 'CreditSnap1'
-- works!

-- Once you revert the image is restored
-- AND the snapshot **STAYS**

-- Change some of august AGAIN
USE CreditPT
go

UPDATE ChargesPT
	SET charge_amt = 20040806
	WHERE charge_dt >= '20040806' 
		AND charge_dt < '20040807'
-- File sizes don't change. Snapshot already has these values...

-- Change some of July that was never changed...
UPDATE ChargesPT
	SET charge_amt = 20040716
	WHERE charge_dt >= '20040716' and charge_dt < '20040717'

-- can we revert back...again?!
RESTORE DATABASE CreditPT 
	FROM database_snapshot = 'CreditSnap1'
-- works!

----------------------------------------------------------------------------------------------
---------------------------------- Restore Original Backup -----------------------------------
----------------------------------------------------------------------------------------------

--DROP DATABASE CreditSnap1
--go
--RESTORE DATABASE [CreditPT]
--FROM DISK = N'C:\SQLTemp\CreditPT.bak' WITH REPLACE
--GO