/*
SnapshotSample
www.resquel.com
2007-11-05
*/
CREATE DATABASE AdventureWorks_Snapshot1 ON(
	NAME = 'AdventureWorks_Data',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data1.ss')
AS SNAPSHOT OF AdventureWorks
GO
SELECT 'Step 1'
	, mf.database_id
	, mf.file_id
	, mf.name
	, mf.is_sparse
	, mf.physical_name
	, vfs.BytesWritten
	, vfs.BytesOnDisk
FROM master.sys.master_files mf 
	CROSS APPLY fn_virtualfilestats(mf.database_id, mf.file_id) vfs
WHERE mf.name LIKE 'AdventureWorks/_%' ESCAPE '/' AND is_sparse = 1
GO
USE AdventureWorks
UPDATE Person.Contact
SET FirstName = LastName, LastName = FirstName
GO
CREATE DATABASE AdventureWorks_Snapshot2 ON(
	NAME = 'AdventureWorks_Data',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data2.ss')
AS SNAPSHOT OF AdventureWorks
GO
SELECT 'Step 2'
	, mf.database_id
	, mf.file_id
	, mf.name
	, mf.is_sparse
	, mf.physical_name
	, vfs.BytesWritten
	, vfs.BytesOnDisk
FROM master.sys.master_files mf 
	CROSS APPLY fn_virtualfilestats(mf.database_id, mf.file_id) vfs
WHERE mf.name LIKE 'AdventureWorks/_%' ESCAPE '/' AND is_sparse = 1
GO
USE AdventureWorks
UPDATE Person.Address
SET AddressLine1 = COALESCE(AddressLine2,''), AddressLine2 = AddressLine1
GO
SELECT 'Step 3'
	, mf.database_id
	, mf.file_id
	, mf.name
	, mf.is_sparse
	, mf.physical_name
	, vfs.BytesWritten
	, vfs.BytesOnDisk
FROM master.sys.master_files mf 
	CROSS APPLY fn_virtualfilestats(mf.database_id, mf.file_id) vfs
WHERE mf.name LIKE 'AdventureWorks/_%' ESCAPE '/' AND is_sparse = 1
GO
USE AdventureWorks
UPDATE Person.Contact
SET FirstName = LastName, LastName = FirstName
GO
SELECT 'Step 4'
	, mf.database_id
	, mf.file_id
	, mf.name
	, mf.is_sparse
	, mf.physical_name
	, vfs.BytesWritten
	, vfs.BytesOnDisk
FROM master.sys.master_files mf 
	CROSS APPLY fn_virtualfilestats(mf.database_id, mf.file_id) vfs
WHERE mf.name LIKE 'AdventureWorks/_%' ESCAPE '/' AND is_sparse = 1
GO
DROP DATABASE AdventureWorks_Snapshot1
DROP DATABASE AdventureWorks_Snapshot2
GO