Finding data files

Before you create a snapshot you need to know the files used by the database. Here’s a query to get the current list of files associated with a mounted database:

SELECT
  db.name AS DBName,
  mf.name as [Filename],
  type_desc AS FileType,
  Physical_Name AS Location
FROM sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
WHERE db.name LIKE 'ExampleDatabase'

Example output:

DBname   Filename     FileType  Location
Contoso	 Contoso      ROWS      C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Contoso.mdf
Contoso	 Contoso_log  LOG       C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Contoso_log.ldf

Make a note of all the ROWS and FILESTREAM entries. You cannot create a snapshot of LOG entries.

If you want to use the SQL Server Management Studio GUI, right click on the database and click on the Files section.

Creating a snapshot

Make sure the folder exists and your credentials can write to it otherwise you’ll get an error. Remember this is all happening on the server the command is running on, and this might not be your local machine.

You can only have one snapshot for a database, and snapshots cannot chain be chained together. You cannot create a snapshot of a snapshot.

Creating a snapshot via T-SQL:

CREATE DATABASE [201607041516_ExampleDatabase_Snapshot]
ON
( NAME = 'ExampleDatabase_rows', FILENAME = 'F:\Snapshots\201607041516_ExampleDatabase_Snapshot\ExampleDatabase_rows.ss' ),
( NAME = 'ExampleDatabase_files', FILENAME = 'F:\Snapshot\201607041516_ExampleDatabase_Snapshot\ExampleDatabase_files.ss' )
AS SNAPSHOT OF ExampleDatabase

Make sure to add an entry for each data file.

Restoring a snapshot

When you’ve got a snapshot and need to restore a database to it, use the below SQL.

The “WITH ROLLBACK IMMEDIATE” statement rolls back any currently open transactions. The database must be in single user mode to be restored. After it’s been restored, it’s set to multi-user mode.

USE master; 

ALTER DATABASE ExampleDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE ExampleDatabase from 
DATABASE_SNAPSHOT = '201607041516_ExampleDatabase_Snapshot';
GO

ALTER DATABASE ExampleDatabase
SET MULTI_USER;
GO