How to Create a database snapshot and what are the pre-requisites?

Problem: How to Create a database snapshot and what are the pre-requisites?

Solution: Create a Database Snapshot (Transact-SQL) –This particular feature creates a snapshot of a database and will create a copy of ready only version of a database, which can be used to revert in an error situation.

Syntax:

CREATE DATABASE database_snapshot_name

ON

(

NAME =logical_file_name,

FILENAME ='os_file_name'

) [ ,...n ]

AS SNAPSHOT OF source_database_name

[;]

Note: there is no option to create a database snapshot from SQL Server management studio front end, it needs to be created using a T-SQL Query.

To create a database snapshot, we have few pre-requisites.

1. All editions of 2016 SQL server Sp1 have this feature. Previous editions (2014 and beyond) of SQL Server must be enterprise edition.

2. Database must be online or mirrored

3. Database cannot have memory_optimized_data file groups.

4. Needs disk space as big as the source database.

Example:

In this example, I would like to create a snapshot of a database and view the properties of the snapshot by querying the system catalog views.

CREATE DATABASE Test_SSEvening ON 

( NAME = Test,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\Test_data_Evening.ss' ) 

AS SNAPSHOT OF Test; 

GO 

OUTPUT:

As we can see in the above picture below the system databases folder there is a database snapshots folder which stores all the snapshots of a database.

--sample query to select the database snapshots.

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],

        sd.name AS [Snapshot],

        mf.name AS [Filename],

        size_on_disk_bytes/1024 AS [size_on_disk (KB)],

        mf2.size/128 AS [MaximumSize (MB)]

FROM sys.master_files mf

JOIN sys.databases sd

    ON mf.database_id = sd.database_id

JOIN sys.master_files mf2

    ON sd.source_database_id = mf2.database_id

    AND mf.file_id = mf2.file_id

CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)

WHERE mf.is_sparse = 1

AND mf2.is_sparse = 0

ORDER BY 1;

OUTPUT:

SourceDatabase	Snapshot	Filename	size_on_disk (KB)	MaximumSize (MB)
Test	Test_SSEvening	Test	128	4