sp_snapshot – The easy way to take database snapshots of one or more databases – V3.0

Presenting you with an updated version of our sp_snapshot procedure, allowing you to easily create database snapshots.

This new version fixes a bug that we’ve found in version 2 where snapshots will fail for databases with multiple data files.

We’ve also added the @STMTOnly parameter, allowing you to generate the scripts for creating the required snapshots without actually doing so.

Parameters

@DatabaseList  – a comma delimited string of database names, allows wildcards

@Suffix – adds a suffix to the snapshot name, giving it the format <databasename>_<suffix>, DEFAULT value is ‘snapshot’

@FilePath – specify a file path where the snapshot files will be saved to. If left blank, this will default to the databases’ data file directory

@TimeStamp - 0 – No timestamp is added to the snapshot name DEFAULT

1 – A time stamp is added after the suffix

@DateFormat – Specify date format code as defined here, https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16 DEFAULT = 126

@ListOnly –  1- a list of affected databases will be displayed but snapshots aren’t created.

0- Snapshots are created automatically DEFAULT

@STMTOnly – 1 – generates the scripts for creating the snapshots instead of creating them a runtime.

0 – Snapshots are generated at runtime DEFAULT

Example

Create a snapshot of AdventureWorks and all databases with names beginning with ‘SQL’

sp_snapshot

the code can be found on our GitHub repo https://github.com/SQLUndercover/UndercoverToolbox/blob/master/sp_Snapshot.sql