Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active November 16, 2021 08:58
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 5 You must be signed in to fork a gist
  • Save EitanBlumin/3b789f96055d9e27afd852c2dd810063 to your computer and use it in GitHub Desktop.
Save EitanBlumin/3b789f96055d9e27afd852c2dd810063 to your computer and use it in GitHub Desktop.
Easily create a database snapshot for a given database using T-SQL
DECLARE
@CurrDB SYSNAME = DB_NAME()
,@WhatIf BIT = 1
DECLARE @CMD NVARCHAR(MAX), @SnapshotName SYSNAME;
SET @SnapshotName = @CurrDB + '_snapshot_' + CONVERT(nvarchar, GETDATE(), 112) + REPLACE(CONVERT(nvarchar, GETDATE(), 114),':','');
SELECT @CMD = ISNULL(@CMD + N',
', N'') + N'(NAME = ' + QUOTENAME(name) + N'
, FILENAME = ' + QUOTENAME(LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
+ @SnapshotName + N'_' + name + '.ss', N'''')
+ N')'
FROM sys.master_files
WHERE type <> 1
AND database_id = DB_ID(@CurrDB)
SELECT
@CMD = N'CREATE DATABASE ' + QUOTENAME(SnapshotName)
+ ISNULL(N'
ON ' + @CMD, N'')
+ N'
AS SNAPSHOT OF ' + QUOTENAME(@CurrDB)
, @CurrDB = SnapshotName
FROM
(VALUES (@SnapshotName)) AS v(SnapshotName)
PRINT @CMD
IF @WhatIf = 0 EXEC(@CMD)
@EitanBlumin
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment