Creating database snapshots using Microsoft SQL Server Management Studio (SSMS)
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:
Example output:
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:
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.