--
-- 0 - Make directories used by example
--

-- Modify production directory
EXEC xp_cmdshell 'mkdir c:\MSSQL\SNAP';
GO


--
-- 1 - Drop existing snapshot!
--

-- Use master
USE [master]
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE_SS')
    DROP DATABASE [WILDLIFE_SS]
GO


--
-- 2 - Create a new snapshot
--

CREATE DATABASE WILDLIFE_SS ON
( NAME = WildLifeData, FILENAME = 'C:\MSSQL\SNAP\Wild_Life_Data.ss' )
AS SNAPSHOT OF [WILDLIFE];
GO

--
-- 3 - Damage the animals table (445)
--

-- Odd id numbers
SELECT COUNT(*) AS ODDNUMS FROM WILDLIFE.dbo.ANIMALS 
WHERE (ID % 2) = 1

-- Remove the odd numbers
DELETE FROM WILDLIFE.dbo.ANIMALS 
WHERE (ID % 2) = 1


--
-- 4 - Get my data back!
--

-- Start with master
USE master;
GO

-- Do a restore with the snapshot (REVERT)
RESTORE DATABASE [WILDLIFE] FROM DATABASE_SNAPSHOT = 'WILDLIFE_SS';
GO

-- Total number of records = 445
SELECT COUNT(*) AS TOTAL FROM WILDLIFE.dbo.ANIMALS