Tags

, ,


Introduced in SQL Server 2005, database snapshot is a useful feature and is considered a real-time point-in-time restore option by taking an image of the database which can used later on if needed to revert the database back to the point when database snapshot was taken.

In the very beginning of this article I would like to stress on one point that Database Snapshot is not a replacement of database backups or database log backups. It is VERY important in every organisation to have a solid backup\recovery strategy which should not be compromised at any cost. The reason why database snapshots are not a replacement for database backups is because database snapshot is only usable when its source database is usable and online. If the source database gets corrupted or goes into SUSPECT mode then most likely the database Snapshots are also not usable.

In my environment, many times developers do a lot of testing on QA environment and they ask DBAs to take the backup in case things don’t pan out as expected. Later on if needed they ask us to restore these databases. Now the environment in which I am working has databases which are Huge in size, few of them crosses 5TB. To restore these huge databases often takes a lot of time and then these database snapshots come handy 🙂 .

According to the BOL:
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.

This is it for today, thank you for reading this long article 🙂 .

Cheers,

Subhro Saha

Advertisements