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