This is a really neat addition to SQL Server 2005. Database Snapshots are essentially a read-only, static view of an existing database, without duplicating all the data on disk.

I use this feature a lot when I upgrade database schemas, or make major changes to data, and need to be able to compare the changes afterwards. If you had a 3rd party tool (or an untrusted developer) that upgrades an existing database, you could use this to see what changes occur.

Now database snapshots are only available in the Enterprise and Developer editions of SQL Server 2005, and is really usefull in both these setups. Developers benefit from being able to compare database changes to a prior state. In Enterprise production, you can use the snapshot for reporting purposes or with Analysis Services. The read-only snapshot will not suffer from locking issues that you might experience with active OLTP databases.

Creating a snapshot of an existing database is really simple. 

CREATE DATABASE database_snapshot_name 
	ON
		(
		NAME = logical_file_name, 
		FILENAME = 'os_file_name'
		)
	AS SNAPSHOT OF source_database_name

So why the need for a filename if we’re creating a read-only view of the source database? We’ll before a data-page is modified in the source database, the original page is copied to the snapshot database. This “copy-on-write operation” is what ensures the data in the snapshot database remains constant.

SQL Server Database Snapshots use the NTFS file system “sparce file” feature to create a file on disk that appears the same size as the original database, but doesn’t actually consume disk space until a data-page is modified in the source. You can check this by viewing the snapshot physical file properties in windows explorer (the Size on Disk represents how much disk space is really being consumed). Or you can use the T-SQL fn_virtualfilestats function. Should every page in your source database be modified, your snapshot file will at most consume the size of the original source database at the time of the snapshot creation.

Now if you really wanted to, you can even restore your source database from the snapshot. While you couldn’t use snapshots in place of a proper backup stratergy: if you are testing a change script over and over, it might be quicker to restore from the snapshot itself. In that case a simple

RESTORE DATABASE source_database_name 
FROM DATABASE_SNAPSHOT = database_snapshot_name

will bring your source database back to its previous state.

Such a simple elegant solution!

Tags: SQL Server, T-SQL