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’
| 1 | sp_Snapshot @DatabaseList = 'AdventureWorks,SQL%' |
sp_snapshot
the code can be found on our GitHub repo https://github.com/SQLUndercover/UndercoverToolbox/blob/master/sp_Snapshot.sql