In SQL Server snapshot can be created which creates a point in time read-only copy of any database. The amount of space is less that the actual database as it stores the pages that have changed, it has a copy of the page before the change was made. The snapshot database is a read-only database. Reading from snapshot are not blocked no matter of isolation level as it reads pages that are not being changed. Snapshot can only be created on NTFS system, it can’t be created on FAT system.

Syntax to create a snapshot:
CREATE DATABASE AW_snapshot ON
( Name = N’AWData’, Filename = N’c:\program files\…\AW_snapshot.mdf’)
AS SNAPSHOT OF AdventureWorks
Note: The “Name” should be the same as the Name of the datafile of the database you want snapshot of the database, otherwise you will get the following error message when the name doesn’t match.
Msg 5014, Level 16, State 3, Line 1
The file ‘AdventureWorks1_Data’ does not exist in database ‘AdventureWorks’.
select name, fileid from sysfiles
name fileid
————————- ——
AdventureWorks_Data 1
AdventureWorks_Log 2

It may seem snapshot takes the same out of disk space but it reality snapshot doesn’t. Below shows the space used by snapshot. One can also see the difference in size of the snapshot file using Windows Explorer properties of the snapshot file.

Free space before snapshot was created: 201,216,602,112 bytes free
Free space after snapshot created: 201,216,405,504 bytes free
Space displayed in OS used by snapshot and database data file:
C:\temp>dir AW_snapshot.mdf
Volume in drive C has no label.
Volume Serial Number is 8866-839E

Directory of C:\temp

04/01/2010 09:13 PM 171,900,928 AW_snapshot.mdf

C:\temp>dir “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adve
ntureWorks_Data.mdf”
Volume in drive C has no label.
Volume Serial Number is 8866-839E

Directory of C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

03/27/2010 11:04 AM 171,900,928 AdventureWorks_Data.mdf

Actual file size of AdventureWorks:
SELECT database_id, size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(DB_ID(N’AdventureWorks’), 1);

database_id size_on_disk_bytes
———– ——————–
7 196608

(1 row(s) affected)

Actual file size of AdventureWorks snapshot:
SELECT database_id, size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(DB_ID(N’AW_snapshot’), 1);

database_id size_on_disk_bytes
———– ——————–
6 171900928

To drop DROP DATABASE SnapshotName. Example: DROP DATABASE AW_snapshot

RESTORE DATABASE AdventureWorks FROM snapshot = AW_snapshot
If multiple snapshot exist you should drop all the ones you don’t want except for the one you want revert to
Dropping a user from source database will not drop user from snapshot database, the snapshot inherits security constraints and it can’t be changed as it is read-only database.

Note; Database cannot be dropped, detached or restored if snapshot exists. If DB made offline then snapshot will be dropped
If multiple exists you can restore tilll all except the one you want is dropped. Snapshot cannot be backed or restored, it cannot be attached/detached.