Testing development using Database Snapshot

Database snapshot is the new feature on SQL Server 2005. for the first i saw this feature, i've never touch it on the production are, even for testing area. But recently, i found the new concept for testing development using database snapshot.

So i implemented database snapshot as a backup database. and if i need to restore it back as an original data we can just only restore it from database snapshot. it's easier and faster.

A couple days ago, i had a task to testing end of day module. because the database was about 90GB, we couldn't restored the database just one or two hours .. it took a couple hours to complete the restore -more than 3 hours-. and then i found the article -at MSDN- about database snapshot on testing development. first step that i've done was creating database snapshot.

here is the script:

USE [master]
GO

CREATE DATABASE [UGETS_TEST_SNAPSHOT_2] ON
( NAME = N'SYSTEM_DATA_01',
FILENAME = N'F:\Snapshot\SYSTEM_DATA_011.ss' ),
( NAME = N'SYSTEM_DATA_02',
FILENAME = N'F:\Snapshot\SYSTEM_DATA_021.ss' ),
( NAME = N'SYSTEM_DATA_03',
FILENAME = N'F:\Snapshot\SYSTEM_DATA_031.ss' ),
( NAME = N'SYSTEM_DATA_04',
FILENAME = N'F:\Snapshot\SYSTEM_DATA_041.ss' ),
( NAME = N'R05G01_01',
FILENAME = N'F:\Snapshot\R05G01_011.ss' ),
( NAME = N'R05G01_02',
FILENAME = N'F:\Snapshot\R05G01_021.ss' ),
( NAME = N'R05G01_03',
FILENAME = N'F:\Snapshot\R05G01_031.ss' ),
( NAME = N'R05G01_04',
FILENAME = N'F:\Snapshot\R05G01_041.ss' ),
( NAME = N'R05G01idx_01',
FILENAME = N'F:\Snapshot\R05G01idx_011.ss' ),
( NAME = N'R05G01idx_02',
FILENAME = N'F:\Snapshot\R05G01idx_021.ss' ),
( NAME = N'R05G02_01',
FILENAME = N'F:\Snapshot\R05G02_011.ss' ),
( NAME = N'R05G02_02',
FILENAME = N'F:\Snapshot\R05G02_021.ss' ),
( NAME = N'R05G02_03',
FILENAME = N'F:\Snapshot\R05G02_031.ss' ),
( NAME = N'R05G02_04',
FILENAME = N'F:\Snapshot\R05G02_041.ss' ),
( NAME = N'R05G02idx_01',
FILENAME = N'F:\Snapshot\R05G02idx_011.ss' ),
( NAME = N'R05G02idx_02',
FILENAME = N'F:\Snapshot\R05G02idx_021.ss' ),
( NAME = N'R10G01_01',
FILENAME = N'F:\Snapshot\R10G01_011.ss' ),
( NAME = N'R10G01_02',
FILENAME = N'F:\Snapshot\R10G01_021.ss' ),
( NAME = N'R10G01_03',
FILENAME = N'F:\Snapshot\R10G01_031.ss' ),
( NAME = N'R10G01_04',
FILENAME = N'F:\Snapshot\R10G01_041.ss' ),
( NAME = N'R10G01idx_01',
FILENAME = N'F:\Snapshot\R10G01idx_011.ss' ),
( NAME = N'R10G01idx_02',
FILENAME = N'F:\Snapshot\R10G01idx_021.ss' ),
( NAME = N'R10G01idx_03',
FILENAME = N'F:\Snapshot\R10G01idx_031.ss' ),
( NAME = N'R10G01idx_04',
FILENAME = N'F:\Snapshot\R10G01idx_041.ss' )
AS SNAPSHOT OF [UGETS_TEST]

How many filegoup on database snapshot? well, it's easy. it's the same filegroup from database original except the log. after i created the database snapshot, i implemented the script to testing end of day module. and after that, i had to restore the database from database snapshot to original.

here is the script to revert database snapshot.

RESTORE DATABASE UGETS_TEST FROM
DATABASE_SNAPSHOT = 'UGETS_TEST_SNAPSHOT_2'

Well, database snapshot -i think- isn't very helpfull from production area because it can be impact for database performance. but at development area, it is very very very usefull.. moreover if we use a huge database.