Home » SQL Master Database » Know About SQL Server Database Snapshot

Know About SQL Server Database Snapshot

Stacy Brown ~ Modified: January 24th, 2019 ~ SQL Master Database ~ 4 Minutes Reading

Introduction

SQL Server database snapshot in SQL database is a read-only and static view of the database. Reverting SQL database to a state when the snapshot was created serves as an alternative to restore database form online backups. Reverting the database to a recent snapshot proves to be beneficial if the database has been damaged somehow. It is to be considered that the snapshot should be taken before the occurrence of the error or the corruption of the database.

The updates made to the database are overwritten while reverting the snapshots by copying all the copy-on-write pages back to the database. The pages that have been updated only get overwritten.

In this article, you will learn how to create database snapshots in SQL Server and also, about its advantage and limitations in SQL Server.

How to Create a Database Snapshot in SQL Server 2012?

CREATE DATABASE Aria_dbss1800 ON
( NAME = Aria, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ Aria_dbss1800.ss' )
AS SNAPSHOT OF Aria;
GO

The above T-SQL statement will create a database snapshot on “Area” database. you can also create a database snapshot on mirrored database in your SQL Server database

Creating a Database Snapshot on the Mirror Database

CREATE DATABASE Aria_dbss1800
ON (NAME = 'datafile', FILENAME='E:\Aria_dbss1800.SNP')
AS SNAPSHOT OF Area

SQL Server Database Snapshot – Advantage

  • The SQL Server database snapshots can be used afterwards to make reports of the data residing in the database snapshots.
  • Helpful for maintaining historical data for the purpose of report generation in future.
  • Using database snapshots accompanied with database mirroring allows making reports of the data residing on the mirror server.
  • The data is safeguarded against administrative errors.

SQL Server Database Snapshots – Limitations

Some of the limitations that come with database snapshots are described as under:

  • The database snapshot should be created as well as kept in the source database instance in which it was originally created.
  • The snapshots always work on the complete SQL database.
  • Database snapshots do not serve as prevention for corruption or disk errors.
  • While a database snapshot is created and it runs out of disk space, it becomes suspect and its deletion is necessary.
  • Being read-only, the database snapshots cannot be upgraded and cannot be considered executable after upgradation.
  • The model, tempdb and master database snapshots are prohibited.
  • Files cannot be dropped from the SQL Server database snapshots.
  • Database snapshots cannot be restored or backed up.

Now, its time to know about the different steps to revert SQL database to a database snapshot in SQL Server.

Steps To Revert SQL Database To A Database Snapshot

In order to revert a SQL database to a database snapshot Transact-SQL statements are used. Follow the given mentioned procedure for reverting the database via database snapshot:

  1. Select SQL Server database snapshot with which you want to revert the database. There are two ways with the help of which you can search the database snapshot. Either you can find it in SQL Server Management Studio or you can search the source database from source_database_id column of the sys.databases.
  2. Drop any other database snapshot if available.
  3. Execute the Revert operation. It is to be noted that the reverting process requires RESTORE DATABASE permissions on the source database. The below mentioned Transact-SQL statement is used for reverting the database:
    RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT = database_snapshot_name

    Here database_name is the name of the source database and database_snapshot_name is the name of the snapshot to which the database needs to be reverted.

  4. In case the database owner of the snapshot has been changes, updation of the database owner of the reverted database needs to be done.
  5. Start the source database.
  6. You can also create back up of the reverted database in case you are using full-recovery model.

By following the above steps you might easily revert SQL Database to a Database Snapshot in SQL Server.

Example: Here the name of the source database is Aria and the name of the database snapshot is Aria_dbss1800.

USE master;
-- Reverting Aria to Aria_dbss1800
RESTORE DATABASE Aria from 
DATABASE_SNAPSHOT = 'Aria_dbss1800';
GO