Using T-SQL Snapshot Backup - Point in Time Recovery

Introduction

In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and perform point-in-time database restores using a snapshot backup as the base of the restore. We will explore how to manage storage-level operations, such as cloning snapshots and executing an instantaneous point-in-time restore of a database from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and the PureStoragePowerShellSDK2 modules to automate the process.

You can grab the whole script for this blog post here.

Taking a T-SQL Snapshot Backup

In the previous post in this series, “Using T-SQL Snapshot Backup - Are Snapshots Backups?” we covered the theory of using T-SQL Snapshot Backup for backup and restore operations. Now let’s get down to business and walk through the process of taking a T-SQL Snapshot Backup.

Initialize the Script’s Variables and Connections

  • To begin, we’ll set up some PowerShell variables to establish a connection to our SQL Server. We’ll also set some variables describing where our primary storage is, a Pure Storage FlashArray, and which volumes contain our databases. I’m utilizing dbatools and the Pure Storage PowerShell SDK2 to manage this process.

    Import-Module dbatools
Import-Module PureStoragePowerShellSDK2

$TargetSQLServer = 'SqlServer1'                       # SQL Server Name
$ArrayName       = 'flasharray1.example.com'          # FlashArray
$DbName          = 'Database1'                        # Name of database
$BackupShare     = '\\FileServer1\SHARE\BACKUP'       # File system location to write the backup metadata file
$PGroupName      = 'SqlServer1_Pg'                    # Name of the Protection Group on FlashArray1
$FlashArrayDbVol = 'Fa_Sql_Volume_1'                  # Volume name on FlashArray containing database files
$TargetDisk      = '6000c29240f79ca82ef017e1fdc000a7' # The serial number of the Windows volume containing database files

  • I am using the dbatools module here because it allows me to create a persistent SMO connection to our SQL instance and use it across multiple cmdlet calls in my script. Unlike the Invoke-SqlCmd from the SqlServer module, which establishes a connection, executes the query, and then disconnects the session. If we use Invoke-SqlCmd to initiate the database freeze, the database will immediately thaw when the session disconnects. Since dbatools maintains the connection, the database will remain frozen even after executing the cmdlet. To do this, I establish the SMO connection using Connect-DbaInstance, store the connection in $SqlInstance, and then pass it across various cmdlet calls, ensuring that the same SPID/connection is reused within SQL Server.

    $SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection

  • Let’s get some information about our database; take note of the size (in MB). That’s a pretty big database, nearly 3.5TB on disk. We’re going to restore it nearly instantly later on in this script. Who’s excited?

    Get-DbaDatabase -SqlInstance $SqlInstance -Database $DbName | Select-Object Name, SizeMB

Name          SizeMB
----          ------
Database1 3484091.81

Connect to Our Storage Environment

  • In order to connect to the FlashArray’s REST API, we will prompt for user credentials. These will be used to create a connection object stored in the $FlashArray variable for subsequent operations in the script, including taking a storage-based snapshot after freezing the database for write IO.

    $Credential = Get-Credential
$FlashArray = Connect-Pfa2Array –EndPoint $ArrayName -Credential $Credential -IgnoreCertificateError

Taking a T-SQL Based Snapshot Backup

  • Next, we need to freeze the database for write operations. We will create a query string for the T-SQL we want to run and then pass that string as a parameter to Invoke-DbaQuery. This code reuses the $SqlInstance SMO connection that was established earlier, so when this code finishes running, the database will still be frozen for write IO. I added the -Verbose flag to expose some additional information. In the output, you can see the backup locks being acquired and the output confirming that the database is frozen for write IO.

    $Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

VERBOSE: Database 'Database1' acquired suspend locks in session 57.
VERBOSE: I/O is frozen on database Database1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
VERBOSE: Database 'Database1' successfully suspended for snapshot backup in session 57.

  • Next, take a snapshot of the Protection Group while the database is frozen for write IO. This snapshot will contain an application-consistent set of data that can be used for restores, replicated to other arrays in the same site, or transferred between sites for disaster recovery. In the output below, you can see the information returned when taking a snapshot on a FlashArray. Let’s store this information in the variable $Snapshot so we can use it later to encode some of these details in the metadata-only backup file’s header.

    $Snapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray -SourceName $PGroupName 
$Snapshot

Name              : SqlServer1-pg.2121
Created           : 9/1/2024 7:34:46 PM
Destroyed         : False
Pod               : 
Source            : @{Id='53340721-d581-366f-078d-6d6be001fab3'; Name='SqlServer1-pg'}
Space             : 
Suffix            : 2121
TimeRemaining     : 
EradicationConfig : @{ManualEradication='enabled'}
Id                : 7200bda4-b855-9c01-627e-5bf6eeec5a16

  • Now, it’s time to take a metadata-only backup. The metadata-only backup file name is dynamically generated using the database name and the current date. The backup command contains the database name $DbName, the backup file location TO DISK='$BackupFile', and the WITH METADATA_ONLY option to specify that it’s a snapshot backup without copying the data. The MEDIADESCRIPTION part stores the snapshot and FlashArray names in the backup metadata as a pipe-delimited string. This information can help locate the snapshot associated with this metadata-only backup file. Finally, the T-SQL Snapshot Backup is executed using the cmdlet Invoke-DbaQuery, which writes the metadata-only backup file to the specified location after thawing the database if successful.

    I am using the verbose flag again. When the command is executed, the output will show that the database is thawed, write IO is resumed, and the backup locks are released. Additionally, it will produce output similar to that of a full backup, reporting the number of pages copied from the database files. However, in this case, it will show that 0 pages are copied because we are performing a METADATA_ONLY snapshot backup.

    $BackupFile = "$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName 
TO DISK='$BackupFile' 
WITH METADATA_ONLY, MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

VERBOSE: I/O was resumed on database Database1. No user action is required.
VERBOSE: Database 'Database1' released suspend locks in session 57.
VERBOSE: Database 'Database1' originally suspended for snapshot backup in session 57 successfully resumed in session 57.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_Base_1' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci1_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci2_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci3_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci4_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci5_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci6_01' on file 1.
VERBOSE: Processed 0 pages for database 'Database1', file 'Database1_part_ci7_01' on file 1.
VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).

  • Now that we have a snapshot backup as a base for a FULL database restore, let’s take a log backup so that we can later showcase how to perform a point-in-time recovery.

    $LogBackup = Backup-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Type Log -Path $BackupShare -CompressBackup

What Does SQL Server Think Happened?

  • Let’s check the SQL Server Error Log to see what SQL Server thinks happened. Here you can see that the snapshot backup process begins for Database1. The database option suspend_for_snapshot_backup is set to ON, and the database acquires backup locks, and freezes I/O operations. Then after we take our METADATA_ONLY snapshot backup, the I/O operations for Database1 are resumed, and the backup locks are released. The log reports that the backup operation completes successfully, and we see that again 0 pages being copied.

    Get-DbaErrorLog -SqlInstance $SqlInstance -LogNumber 0 | Format-Table

SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:34:29 PM  spid57  Setting database option suspend_for_snapshot_backup to ON for database 'Database1'.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:34:29 PM  spid57  Database 'Database1' acquired suspend locks in session 57.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:34:29 PM  spid57  I/O is frozen on database Database1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:34:29 PM  spid57  Database 'Database1' successfully suspended for snapshot backup in session 57.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  spid57  I/O is frozen on database Database1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  spid57  I/O was resumed on database Database1. No user action is required.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  spid57  Database 'Database1' released suspend locks in session 57.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  spid57  Database 'Database1' originally suspended for snapshot backup in session 57 successfully resumed in session 57.
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  Backup  Database backed up. Database: Database1, creation date(time): 2023/10/28(20:43:06), pages dumped: 186122570, first LSN: 185573:2497964:53, last LSN: 185573:2497987:1, number of dump devices: 1, device info…
SqlServer1 MSSQLSERVER  SqlServer1 9/1/2024 7:35:13 PM  Backup  BACKUP DATABASE successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).…

  • The backup is recorded in MSDB as a Full backup with a snapshot.

    Get-DbaDbBackupHistory -SqlInstance $SqlInstance -Database $DbName -Last

SqlInstance  Database  Type TotalSize DeviceType Start                   Duration End
-----------  --------  ---- --------- ---------- -----                   -------- ---
SqlServer1   Database1 Full 1.39 TB   Disk       2024-09-01 19:35:11.000 00:00:02 2024-09-01 19:35:13.000

  • Let’s take a look at the information in the backup header. Here, I’ve summarized important details such as ‘BackupType’, ‘BackupPath’, and data size. This header format is consistent across all backup types in SQL Server. As you develop automation processes around this feature, it should be familiar territory for a DBA.

    Read-DbaBackupHeader -SqlInstance $SqlInstance -Path $BackupFile

BackupName             : 
BackupDescription      : 
BackupType             : 1
ExpirationDate         : 
Compressed             : 0
Position               : 1
DeviceType             : 2
UserName               : DOMAIN\anocentino
ServerName             : SqlServer1
DatabaseName           : Database1
DatabaseVersion        : 957
...output ommitted...
BackupPath             : \\w2016-anthony\SHARE\BACKUP\Database1_20240901T1235110742.bkm
BackupSize             : 1.39 TB
CompressedBackupSize   : 1.39 TB

Performing a Point-in-Time Restore from a T-SQL Snapshot Backup

Now that we have a T-SQL snapshot backup and a log backup, let’s perform a point-in-time restore using these two backups.

  • First, we need to take the database offline, which we’d have to do anyway if we were restoring a full backup.

    $Query = "ALTER DATABASE $DbName SET OFFLINE WITH ROLLBACK IMMEDIATE" 
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query

  • Since this is a storage-level operation, the volume of the database files needs to be taken offline.

    Get-Disk | Where-Object { $_.SerialNumber -eq $TargetDisk } | Set-Disk -IsOffline $True

  • By using the following code, I can retrieve the MEDIADESCRIPTION from the metadata-only backup file, where we stored the pipe-delimited string containing the location of FlashArray where the snapshot is located and the snapshot name. I’m storing this information in the variables $ArrayName and $SnapshotName. This is a crucial part of any snapshot backup plan as it allows us to accurately identify the snapshot associated with the metadata-only file. In this case, the FlashArray is flasharray1.example.com and the snapshot name on that array is SqlServer1-pg.2121, which contains our backup.

    $Query = "RESTORE LABELONLY FROM DISK = '$BackupFile'"
$Labels = Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$ArrayName = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[1]
$SnapshotName = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[0]
$ArrayName
$SnapshotName

flasharray1.example.com
SqlServer1-pg.2121

  • In this example, we are performing an in-place database restore. This process involves overwriting the current volume with the contents of the snapshot and reverting the database to its previous state. On a Pure Storage FlashArray (or Cloud Block Store), the cmdlet “New-Pfa2Volume” is used to clone a snapshot. In this code, we are overwriting the volume’s contents with the snapshot’s contents to revert the database to its previous state.

    New-Pfa2Volume -Array $FlashArray -Name $FlashArrayDbVol -SourceName ($SnapshotName + ".$FlashArrayDbVol") -Overwrite $true

    In our previous post, we discussed additional restore scenarios. We could clone to a new set of volumes on the same instance, restoring the database with a new name. Alternatively, we could clone the volumes to another instance of SQL Server.

  • After restoring the data to its original state, let’s bring the disk back online.

    Get-Disk | Where-Object { $_.SerialNumber -eq $TargetDisk} | Set-Disk -IsOffline $False

  • With the database files physically in-place, and reverted back in time, we can use the following code to create the RESTORE command as a string: RESTORE DATABASE $DbName to initiate the restore process, and specify the location of the metadata-only backup file using the FROM DISK = '$BackupFile' option. Since this is a T-SQL Snapshot backup-based restore, we need to include the METADATA_ONLY option, which informs the database engine not to copy the pages during the restore process. Additionally, we should incorporate the REPLACE option to overwrite the existing database and include the NORECOVERY option to keep the database in RESTORING mode after the command is executed. If you wish to bring the database online at this point, you can omit the NORECOVERY option. The Invoke-DbaQuery command is used to execute the query string and initiates the restore process.

    $Query = "RESTORE DATABASE $DbName FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE, NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query -Verbose

VERBOSE: RESTORE DATABASE successfully processed 0 pages in 1.184 seconds (0.000 MB/sec).

    The full restore duration includes the instant snapshot revert and about 1 second of database operations. That’s just wild!

  • With the full restore completed, let’s check the database’s current status. Its in RESTORING mode.

    Get-DbaDbState -SqlInstance $SqlInstance -Database $DbName 

Access       : MULTI_USER
ComputerName : SqlServer1
DatabaseName : Database1
InstanceName : MSSQLSERVER
RW           : READ_WRITE
SqlInstance  : SqlServer1
Status       : RESTORING

  • Now, it’s time to perform the log restore. I am leaving the database in the RESTORING mode by adding the -NoRecovery parameter.

    Restore-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Path $LogBackup.BackupPath -NoRecovery -Continue

  • With the restore complete, let’s online the database.

    $Query = "RESTORE DATABASE $DbName WITH RECOVERY" 
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query

  • Let’s again check the current state of the database…its ONLINE

    Get-DbaDbState -SqlInstance $SqlInstance -Database 'Database1' 

Access       : MULTI_USER
ComputerName : SqlServer1
DatabaseName : Database1
InstanceName : MSSQLSERVER
RW           : READ_WRITE
SqlInstance  : SqlServer1
Status       : ONLINE

How Long Did The Backup Take?

Lastly, let’s look at how long this process takes, from freezing the database for a consistent snapshot to creating the snapshot on our FlashArray and performing a metadata-only backup. In my lab, this usually takes 400-800ms. But remember, the freeze is only for writes, so it is very fast and only impacts writes. When the database is frozen, you can measure it using the WRITELOG wait type. Most applications can tolerate a write IO freeze of a few hundred milliseconds. Also, it’s likely that you’ll perform this operation during a maintenance window.

$Start = (Get-Date)

$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

$Snapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray -SourceName $PGroupName

#We'll use MEDIADESCRIPTION to hold some information about our snapshot
$BackupFile = "$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName 
 TO DISK='$BackupFile' 
 WITH METADATA_ONLY, MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$Stop = (Get-Date)

Write-Output "The snapshot time takes...$(($Stop - $Start).Milliseconds)ms!"

The snapshot time takes...480ms

Wrapping Things Up

In this post, which is the second in our series, we used the T-SQL Snapshot Backup feature for point-in-time recovery of a database. Consider the business impact of this capability. You can perform an instantaneous full database restore and control your precise recovery point with a point-in-time restore.

You can grab the whole script for this blog post here.

Using T-SQL Snapshot Backup Blog Series

This article is part of a blog series covering the Using T-SQL Snapshot Backup. Check out the other posts in this series:

You can watch Bob Ward and me present a deep dive on this feature at PASS Summit 2023 in the session “Are Snapshots Backups?”.