Skip to content

How to create a SQL Server Database Snapshot

Database snapshots are a great way to store a quick “point-in-time” state of a database that can quickly be rolled back to if required. There are a few instances where snapshots come in particularly useful to a DBA;

  • Prior to major DB upgrade or data import which may be problematic
  • On a SQL mirrored database in order to allow queries on the non-primary DB copy

The setup of a snapshot can be done easily via T-SQL;

ON (NAME = '%%LogicalDataName%%', FILENAME = '%%SnapShotFileNameAndLocation%%.SNP')
AS SNAPSHOT OF %%DatabaseName%%

For more details, see the official Microsoft KB article on this;