Database Snapshot Automation

This PowerShell script creates SQL Server database snapshots with the snapshot files named after the database files they are snapshots of, plus a version number and a .ss suffix. The script should be saved to a folder and called passing parameters, no changes should be necessary. Instance name and database name are mandatory, self explanatory parameters, the version number parameter allows multiple snapshots of the same database to be taken. Call it as below:

X:\ps\ps_Snapshot.ps1 -Instance "PBV02\I08A" -DatabaseName "workspace" -SSVersion "01"

Care is obviously needed managing database snapshots, they grow in size proportional to the volume of transactions occurring in the database and disk space can become an issue.


# Updates: https://paulbrewer.wordpress.com/2013/10/26/database-snapshot-automation/
# Version 1.0
# ==============================================================================
# PARAMETERS AND DEFAULTS
# ==============================================================================
#Param($Instance, $DatabaseName, $SSVersion) 

$Instance = "PBV01\I01"
$DatabaseName = "workspace"
$SSVersion = "01"

# DEFAULTS
if ($SSVersion-eq $null) {$SSVersion = "1"}  

# REPORT RUNTIME VALUES
"EXECUTION - RUNTIME PARAMETERS" | Out-Default
"Server = " + $Server | Out-Default
"DatabaseName = " + $DatabaseName | Out-Default
"SSVersion = " + $SSVersion | Out-Default
" " | Out-Default

# ==============================================================================
# INITIALIZE AND VALIDATE PARAMETERS
# ==============================================================================
#Snapin for the Invoke-SQLCmd cmdlet
Import-Module SQLPS -DisableNameChecking
Set-Location -Path Alias:
#
if ($DatabaseName -eq $null)
{throw "DATABASE NAME IS A REQUIRED PARAMETERS"} 

$InstanceTest = New-Object Microsoft.SQLServer.Management.Smo.Server($Instance)
if ($InstanceTest.InstanceName -eq $null)
{throw "SQL SERVER INSTANCE NOT FOUND"} 

# ==============================================================================
# GENERATE T-SQL SNAPSHOT COMMAND
# ==============================================================================
$GetCommand = ":SETVAR DatabaseName `"" + $DatabaseName + "`"" + "`r`n" + ":SETVAR SSVersion `"" + $SSVersion + "`"" + "`r`n"

$GetCommand = $GetCommand + "
SELECT
'BEGIN TRY
    DROP DATABASE `$(DatabaseName)_`$(SSVersion)_SS
    PRINT ''DROPPING AND CREATING DATABASE SNAPSHOT `$(DatabaseName)_`$(SSVersion)_SS''
END TRY
BEGIN CATCH
    PRINT ''CREATING DATABASE SNAPSHOT `$(DatabaseName)_`$(SSVersion)_SS''
END CATCH' AS TSQL, -1 AS Sequence
UNION ALL
SELECT 'CREATE DATABASE `$(DatabaseName)_`$(SSVersion)_SS ON' AS TSQL,0
UNION ALL
SELECT
    CASE x.file_id
    WHEN 1 THEN '(NAME = ' + name + ',FILENAME = ' + '''' + physical_name + '_' + CAST(`$(SSVersion) AS VARCHAR(5)) + '_' + '.ss' + '''' + ')'
    ELSE ',(NAME = ' + name + ',FILENAME = ' + '''' + physical_name + '_' + CAST(`$(SSVersion) AS VARCHAR(5)) + '_' + '.ss' + '''' + ')'
    END AS TSQL,
    x.file_id
FROM `$(DatabaseName).sys.database_files x
WHERE x.type = 0
AND x.state = 0
UNION ALL
SELECT 'AS SNAPSHOT OF `$(DatabaseName);' AS TSQL,33000" 

$SnapshotCommands = ""
$SnapshotCommands = Invoke-Sqlcmd -Query $GetCommand -ServerInstance $Instance

# ==============================================================================
# DESERIALIZE MULTI LINED T-SQL COMMANDS TO A SINGLE COMMAND
# ==============================================================================
$SnapshotEXEC = ""
foreach ($snapshotCommand in $SnapshotCommands)
{$SnapshotEXEC = $SnapshotEXEC + " " + $snapshotCommand.TSQL} 

Invoke-Sqlcmd -Query $SnapshotEXEC -ServerInstance $Instance -Verbose  -QueryTimeOut 3600

The PowerShell script above is invoking the T-SQL query below which creates and returns a CREATE DATABASE AS SNAPSHOT OF command.


:SETVAR DatabaseName "workspace"
:SETVAR SSVersion "01"

SELECT
'BEGIN TRY
    DROP DATABASE $(DatabaseName)_$(SSVersion)_SS
    PRINT ''DROPPING AND CREATING DATABASE SNAPSHOT $(DatabaseName)_$(SSVersion)_SS''
END TRY
BEGIN CATCH
    PRINT ''CREATING DATABASE SNAPSHOT $(DatabaseName)_$(SSVersion)_SS''
END CATCH' AS TSQL, -1 AS Sequence
UNION ALL
SELECT 'CREATE DATABASE $(DatabaseName)_$(SSVersion)_SS ON' AS TSQL, 0 AS Sequence
UNION ALL
SELECT
    CASE x.file_id
    WHEN 1 THEN '(NAME = ' + name + ',FILENAME = ' + '''' + physical_name + '_' + CAST($(SSVersion) AS VARCHAR(5)) + '_' + '.ss' + '''' + ')'
    ELSE ',(NAME = ' + name + ',FILENAME = ' + '''' + physical_name + '_' + CAST($(SSVersion) AS VARCHAR(5)) + '_' + '.ss' + '''' + ')'
    END AS TSQL,
    x.file_id AS Sequence
FROM $(DatabaseName).sys.database_files x
WHERE x.type = 0
AND x.state = 0
UNION ALL
SELECT 'AS SNAPSHOT OF $(DatabaseName);' AS TSQL, 33000

To restore a database from a snapshot, use the T-SQL RESTORE DATABASE command using the FROM DATABASE_SNAPSHOT option as below.

ALTER DATABASE WORKSPACE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE workspace FROM DATABASE_SNAPSHOT = 'WORKSPACE_01_SS'
ALTER DATABASE WORKSPACE SET MULTI_USER


Categories: PowerShell, Snapshot

%d bloggers like this: