/*============================================================================
  File:     CreditSnap2.sql

  Summary:  This script creates the second database snapshot
			while the database (CreditPT) has a transaction
			in flight.

  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.
============================================================================*/

CREATE DATABASE [CreditSnap2] 
ON  PRIMARY 
( NAME = N'CreditData'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2Data.mdfss')
,
( NAME = N'CreditPTChargesStaging'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2ChargesStaging.ndfss')
,
( NAME = N'CreditPTFG1File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2PTFG1File1.ndfss')
,
( NAME = N'CreditPTFG2File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2PTFG2File1.ndfss')
,
( NAME = N'CreditPTFG3File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2PTFG3File1.ndfss')
,
( NAME = N'CreditPTFG4File1'
	, FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CreditSnap2PTFG4File1.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('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