SQL Server: Database Snapshots !!

A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.
Snapshot database operates at page level. When Snapshot database is created, it is produced on sparse files, it does not occupy any space in the Operating System. When any data page is modified in the source database, that data page is first copied to Snapshot database, making the sparse file size increases. For unchanged pages, it still fetches the data from the actual database.
Lets see how database snapshot works by a practical example. Let us first create the source database to prepare environment.
CREATE DATABASE Test
USE Test
GO
CREATE TABLE Test_Table(ID INT,Name VARCHAR(15))
GO
INSERT INTO Test_Table VALUES
(1,‘A’),
(2,‘B’),
(3,‘C’)
Select * from Test_Table

So we have the table and all the records inserted in it. Lets first look at the size of the database that we created above.
EXEC sp_spaceused

Lets also look at the physical file size of the .mdf file.

Lets create the SNAPSHOT of the database:

USE master
CREATE DATABASE TEST_SS
ON
(NAME=TEST,
FILENAME=‘C:\Subhro\TEST_SS.ss’
)
AS SNAPSHOT OF TEST

The above statement creates a snapshot which can be found here:

Lets check the size of the snapshot:

USE TEST_SS
GO
EXEC sp_spaceused

The space used details shown above are of the “Test” database which the snapshot is pointing to, and to confirm the observation, if we go to C:\Subhro\TEST_SS.ss and right-click for Properties, we will see what is shown below. The actual size occupied by the snapshot is merely a 192 KB.

Lets insert few records in the source database Table:

USE Test
GO
INSERT INTO Test_Table VALUES
(4,‘D’),
(5,‘E’),
(6,‘F’)
Select * from Test_Table

Lets check the records from the Snapshot:

USE TEST_SS
GO
Select * from Test_Table

Lets take a look at the physical size of the Snapshot File again:

We observe that the size of the snapshot has increased; this is because of the page being copied to the snapshot. The page holding the Test table’s three records got copied into the snapshot when we added additional records into it.

This was all about database Snapshot. One final test, let’s try to restore the database from Database Snapshot:

USE master
RESTORE DATABASE TEST
FROM DATABASE_SNAPSHOT = ‘TEST_SS’
Finally, lets again check the records in the Test_table:
select * from test_table

So we can see that all the records that were inserted after creating the snapshot have been rolled back when we restored the database from Snapshot.