Know How To Create Database Snapshot In SQL Server

What is SQL Server Database Snapshot

A database snapshot in SQL Server is a read-only static view of the database. The major advantage of snapshot over backups is the time, especially when you want to take the backup and restore quickly in a short span of time.

Need of Database Snapshot in SQL Server

1. Snapshots are in read only state and very good option for reporting purposes. All the reporting related to hits are made on the snapshot instead on the actual database. Also, as snapshots are read only, the chances of any undesired updates by the subscriber is less.
2. Snapshots can 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.
3. Snapshots can also be used to restore the current database to the point the snapshot was taken. It would perform a very quick point in time restore of the database.

What is a Sparse File In SQL Server Database Snapshot

A sparse file is basically an empty file. It does not contain any data until a change is made to the source database. Sparse file is a feature of the NTFS file system. Initially, a sparse file contains no user data, and disk space.

A sparse file takes up less disk space when first created. As data is written to the sparse file, NTFS allocates disk space gradually. Potentially, a sparse file can grow very large. If a database snapshot runs out of space, it is marked as suspect.

Creating a Database Snapshot using T-SQL

The only way to create a SQL Server database snapshot is to use Transact-SQL. SQL Server Management Studio does not support the creation of database snapshots.

Before the creation of database snapshot there are some recommendation which are given below:

1. Each database snapshot requires a unique database name. For administrative ease.
2. Each snapshot persists until it is explicitly dropped.
3. To use a database snapshot, clients need to know where to find it.

Syntax:

CREATE DATABASE database_snapshot_name
    ON
    (
    NAME = logical_file_name,
    FILENAME = 'os_file_name'
    ) [,..n]
    AS SNAPSHOT OF source_database_name
    [;]

Conclusion

In the present article we have discussed about database snapshot in SQL Server. This article also describes how can we create a database snapshot with the help of Transact-SQL query and the need of SQL Server databases snapshot.