Introduction
Smile, say cheese, and click!! And the snap is clicked. Anytime later when I look at it, it reminds me of the time the snap was clicked. I really wish there was a time machine which could take me back in time. Alas, there is no such time machine with a rewind button in life. Not so in databases. Database snapshot is that wished for feature; it makes the life of databases amazing. Snapshots can be considered as a real time point in time restore option which takes the image of a database. Anytime later, this image/snapshot can be used to revert the database to the point the snapshot was taken.
Snapshots would be desirable in a number of situations:
- Snapshots are read only and are a very good option for reporting purposes. All the reporting related hits could be made on the snapshot instead of on the the actual database, and the burden on the active database could be minimized. Also, as snapshots are read only, chances of any undesired updates by the subscriber (who reads data for reports etc.) could be negated.
- Snapshots could be used to preserve data for financial statistics/analysis. An example yearly snapshot data for a particular product could be used to perform statistical analysis and predict its market growth.
- Snapshots could also be used to restore the current database to the point the snapshot was taken, if the need arises. It would perform a very quick point in time restore of the database.
Initially, when a snapshot is taken, it's nothing less than a pointer to the actual database, i.e., when I query the snapshot, it retrieves data from the actual database. But this happens only till the time the data page has not been changed on the actual database. The moment a change is made to the database, the original page is placed on to the snapshot and then the change is made to the page on the database. This makes sure that the snapshot keeps the image intact. Till the time the snapshot is active, the actual database cannot be dropped. To be able to drop the database, the snapshot would have to be deleted first, followed by the database. Snapshot takes very little disk space. The space occupied by the snapshot is nothing but the changed data pages. For unchanged pages, it still fetches the data from the actual database.
Snapshot Creation
Before we create a snapshot, let's create a database, a table, and insert a few records into the table.
USE master
CREATE DATABASE TEST
USE TEST
CREATE TABLE Student(ID INT,Name VARCHAR(15),SECTION CHAR(1))
INSERT INTO Student VALUES
(1,'Keshav','A'),
(2,'Sachin','B'),
(3,'Rahul','C')
SELECT * FROM dbo.Student
ID Name SECTION
1 Keshav A
2 Sachin B
3 Rahul C
(3 row(s) affected)
Let's note the size of the database.
USE TEST
EXEC sp_spaceused
database_name database_size unallocated space
TEST 1.87 MB 0.11 MB
reserved data index_size unused
1296 KB 512 KB 640 KB 144 KB
and also the size of the .MDF file at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST.mdf.
Next, we will create a snapshot of the database.
USE master
CREATE DATABASE TEST_SS
ON
(NAME=TEST,
FILENAME='C:\Program Files\Microsoft SQL Server\
MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_SS.ss'
)
AS SNAPSHOT OF TEST
The above query creates a snapshot of the test database which is visible on the Object Explorer.
Let's note the size of the database.
USE TEST_SS
EXEC sp_spaceused
database_name database_size unallocated space
TEST_SS 1.87 MB 0.11 MB
reserved data index_size unused
1296 KB 512 KB 640 KB 144 KB
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:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\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.
Next let’s insert a few records into the TEST database’s Student table.
USE TEST
INSERT INTO Student VALUES
(4,'Rohit','A'),
(5,'Saumya','B')
Now if we query the snapshot:
USE TEST_SS
SELECT * FROM dbo.Student
ID Name SECTION
1 Keshav A
2 Sachin B
3 Rahul C
(3 row(s) affected)
There is still the same number of records in the snapshot.
Also, let's try to get the size of the snapshot.
We observe that the size of the snapshot has increased; this is on account of the page being copied to the snapshot. The page holding the Student table’s three records got copied into the snapshot when we added the two additional records into it.
Next, let's try to perform an update on the snapshot data.
UPDATE TEST_SS.dbo.Student SET Name='XXX'
WHERE ID=1
Msg 3906, Level 16, State 1, Line 1
Failed to update database "TEST_SS" because the database is read-only.
Any DML is not possible as the snapshot is always read-only.
Next, let’s try to drop the TEST database.
DROP DATABASE TEST
Msg 3709, Level 16, State 2, Line 1
Cannot drop the database while the database snapshot
"TEST_SS" refers to it. Drop that database first.
Restore from Snapshot
As I said earlier, with a database snapshot, we can reverse the database to the point when the snapshot was taken. This can be done by restoring the database from the snapshot.
A snapshot for a database has pages that are being modified; they are copied to the snapshot before they are updated in the original database so the snapshot contains the unmodified page and the original database holds the modified page. Now when we restore a database from a snapshot, the unmodified pages stored by the snapshot are simply copied on the actual database and the transaction log is also overwritten and rebuilt.
Can we perform regular backups with the help of a snapshot? Can we restore the snapshot on a different server? Can we perform copy database functionality on a snapshot database from the host server to another?
The answer to all of the above questions is no. Since the database snapshot is an incomplete copy of the database, it’s not meant for performing any regular backups. It could be considered as a real time point in time restore option.
USE master
RESTORE DATABASE TEST
FROM DATABASE_SNAPSHOT = 'TEST_SS'
