Using T SQL Snapshot Backup: Point in time Recovery – Azure Edition

This blog on T-SQL snapshot backups originally appeared on www.nocentino.com. It has been republished with permission from the author.

In this post, the third 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 then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.

You can grab the whole script for this blog post on GitHub.

Taking a T-SQL Snapshot Backup

In the previous posts in this series, "Using T-SQL Snapshot Backup: Are Snapshots Backups?" and "Using T-SQL Snapshot Backup: Point-in-time Recovery," we covered the theory of using T-SQL snapshot backup for backup and restore operations and also how to use this technique on a Pure Storage® FlashArray ™ system and Pure Cloud Block Store™. Now it's time to look at how to use T-SQL snapshot backup in Azure Virtual Disks. Let's get started…

Initialize the Script's Variables and Connections

• To begin, we'll set up some PowerShell variables to establish connections to our SQL Server and also Azure. I'm utilizing dbatools and the Az PowerShell module to manage this process.
  

  1

  Import-Module dbatools $TenantId = '1d192baa-aca4-4727-ab9f-290398d9564c' #My super secret TenantId, or is it? $Location = 'CentralUS' #Region for our VM $ResourceGroupName = 'Sql1_group' #Resource group for our VM $TargetSQLServer = 'Sql1' #Virtual Machine name in Azure $DbName = 'Database1' # Name of database $BackupShare = '\\FileServer1\SHARE\BACKUP' # File system location to write the backup metadata file

• Now, let's start by connecting to Azure using the cmdlet Connect-AzAccount. After that, we can create a persistent SMO connection to our SQL Server instance using Connect-DbaInstance just like we have done in previous posts. Lastly, we'll need to work on Azure Virtual Machine storage configuration tasks, so we'll start by getting a reference to our virtual machine in Azure using the command Get-VM.
  

  1	Connect-AzAccount -TenantId $TenantId $SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection $vm = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $TargetSQLServer #Reference to our SQL VM in Azure

Connect to Our Storage Environment in Azure

• First, get the names of the Azure Virtual Disk data disks connected to our virtual machine. Ensure you retrieve all disks containing the database files (data and log) of the database you intend to snapshot. On this system, we have two virtual disks: one for the database files and one for the transaction log file. In the output, you can see our disks are named Sql1_DataDisk_0 and Sql1_DataDisk_1. Azure tools and documentation refer to any non-operating system volume as a data disk.
  

  1	$SourceDataDiskName = ($vm.StorageProfile.DataDisks | Where-Object { $_.Name -eq 'Sql1_DataDisk_0' }).Name $SourceLogDiskName = ($vm.StorageProfile.DataDisks | Where-Object { $_.Name -eq 'Sql1_DataDisk_1' }).Name $SourceDataDiskName $SourceLogDiskName Sql1_DataDisk_0 Sql1_DataDisk_1

• Use the above names to reference the Azure Virtual Disk resources, which will serve as the base for our clone operation. The output below shows the URIs for the Azure Virtual Disk resources we want to work with.
  

  1

  $SourceDataDisk = Get-AzDisk -ResourceGroupName $ResourceGroupName -DiskName $SourceDataDiskName $SourceLogDisk = Get-AzDisk -ResourceGroupName $ResourceGroupName -DiskName $SourceLogDiskName $SourceDataDisk.Id $SourceLogDisk.Id /subscriptions/fd0c5e48-eea6-4b37-a076-0e23e0df74cb/resourceGroups/Sql1_group/providers/Microsoft.Compute/disks/Sql1_DataDisk_0 /subscriptions/fd0c5e48-eea6-4b37-a076-0e23e0df74cb/resourceGroups/Sql1_group/providers/Microsoft.Compute/disks/Sql1_DataDisk_1

Let's Create a Snapshot Config

• Now, let's create a snapshot configuration with the New-AzSnapshotConfig cmdlet. This cmdlet sets up the configuration parameters for the snapshot, including its source, location, and type of snapshot. Later, this configuration will be used with New-AzSnapshot to create the snapshot. The SourceUri parameter uses the Azure Virtual Disk URIs from the previous code block. The Location parameter specifies the Azure region where the snapshot will be created. The CreateOption parameter determines that the snapshot type should be created as a copy of the source disk, using Copy. There are many options for the New-AzSnapshotConfig cmdlet, check out the docs for more details. This is where you can set options to replicate your snapshots to other regions in Azure.
  

  1	$DataSnapshot = New-AzSnapshotConfig ` -SourceUri $SourceDataDisk.Id ` -Location $location ` -CreateOption Copy $LogSnapshot = New-AzSnapshotConfig ` -SourceUri $SourceLogDisk.Id ` -Location $location ` -CreateOption Copy

Taking a T-SQL-based Snapshot Backup

• With our snapshot configuration set, let's proceed with the T-SQL-based snapshot backup process by putting the data in SUSPEND_FOR_SNAPSHOT_BACKUP mode to freeze write IO.
  

  1

  $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 64. 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 64.

• Now, use the New-AzSnapshot command to create a snapshot of each of the virtual machines' data disks using the previously defined snapshot configuration. The -SnapshotName parameter should use the dynamically generated name stored in the $DataSnapshotName variable. Then, use the -Snapshot parameter to refer to the previously built snapshot configuration. Finally, specify the Azure resource group where the snapshot will be created using the -ResourceGroupName parameter. Here, we take a snapshot of Sql1_DataDisk_0 and Sql1_DataDisk_1.
  

  1

  $DataSnapshotName = "Sql1_DataDisk_0$(Get-Date -Format FileDateTime)" $DataSnapshot = New-AzSnapshot ` -Snapshot $DataSnapshot ` -SnapshotName $DataSnapshotName ` -ResourceGroupName $ResourceGroupName $LogSnapshotName = "Sql1_DataDisk_1$(Get-Date -Format FileDateTime)" $LogSnapshot = New-AzSnapshot ` -Snapshot $LogSnapshot ` -SnapshotName $LogSnapshotName ` -ResourceGroupName $ResourceGroupName

• 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 Azure snapshot name and URI 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.
  

  1

  $BackupFile = "$BackupShare\$DbName-$(Get-Date -Format FileDateTime).bkm" $Query = "BACKUP DATABASE $DbName TO DISK='$BackupFile' WITH METADATA_ONLY, MEDIADESCRIPTION='$($DataSnapshotName.Name)|$($DataSnapshot.Id)'" 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 64. VERBOSE: Database 'Database1' originally suspended for snapshot backup in session 64 successfully resumed in session 64. VERBOSE: Processed 0 pages for database 'Database1', file 'Database1' on file 1. VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.006 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.
  

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

• With the snapshot created, you can use the command Get-AzSnapshot -ResourceGroupName $ResourceGroupName to retrieve the snapshots available within the specified resource group. In the following output, you will find both snapshots of our virtual machine disks.
  

  1

  Get-AzSnapshot -ResourceGroupName $ResourceGroupName | Format-Table ResourceGroupName ManagedBy Sku TimeCreated OsType HyperVGeneration CreationData DiskSizeGB DiskSizeBytes UniqueId --------- ----- -- ------ --- -------- ------ ----- ------- ---- Sql1_group Microsoft.Azure.Management.Compute.Models.SnapshotSku 9/3/2024 12:37:39PM Microsoft.Azure.Management.Compute.Models.CreationData 1024 1099511627776 706e4a9c-896c-46c6-b8f7-04401100b… Sql1_group Microsoft.Azure.Management.Compute.Models.SnapshotSku 9/3/2024 12:37:42PM Microsoft.Azure.Management.Compute.Models.CreationData 1024 1099511627776 18c3bbe1-bb98-4ca3-86e7-e94e6b47f…

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 are being copied.

• Let's check out the error log to see what SQL Server thinks happened.
  

  1

  Get-DbaErrorLog -SqlInstance $SqlInstance -LogNumber 0 | Format-Table Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:32PM spid64 Setting database option suspend_for_snapshot_backup to ON for database 'Database1'. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:32PM spid64 Database 'Database1' acquired suspend locks in session 64. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:32PM spid64 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. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:32PM spid64 Database 'Database1' successfully suspended for snapshot backup in session 64. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM spid64 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. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM spid64 I/O was resumed on database Database1. No user action is required. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM spid64 Database 'Database1' released suspend locks in session 64. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM spid64 Database 'Database1' originally suspended for snapshot backup in session 64 successfully resumed in session 64. Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM Backup Database backed up. Database: Database1, creation date(time): 2024/09/03(11:49:49), pages dumped: 66282, first LSN: 39:920:39, last LSN: 39:952:1, number of dump devices: 1,… Sql1 MSSQLSERVER Sql1 9/3/2024 12:37:48PM Backup BACKUP DATABASE successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).…

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

  1	Get-DbaDbBackupHistory -SqlInstance $SqlInstance -Database $DbName -Last SqlInstance Database Type TotalSize DeviceType Start Duration End ------ ---- -- ----- ----- --- ---- -- Sql1 Database1 Full 517.83 MB Disk 2024-09-03 12:37:47.000 00:00:01 2024-09-03 12:37:48.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.
  

  1

  Read-DbaBackupHeader -SqlInstance $SqlInstance -Path $BackupFile BackupName : BackupDescription : BackupType : 1 ExpirationDate : Compressed : 0 Position : 1 DeviceType : 2 UserName : aen ServerName : Sql1 DatabaseName : Database1 ... BackupPath : \\FileServer1\SHARE\BACKUP\Database1-20240903T0737464342.bkm BackupSize : 517.83 MB CompressedBackupSize : 517.83 MB

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

Performing the restore is a storage-level operation that requires work both inside the operating system and Azure. First, inside the operating system you need to take the database and disks offline. Then, in Azure, you need to detach the Azure Virtual Disks from the virtual machine. After detaching the disks, you must clone the snapshot to new virtual disks, ensuring that the configuration matches your performance and availability requirements. Then, you can attach the new cloned disks to your virtual machine. Once the disks are attached, bring them online in Windows and proceed with the T-SQL snapshot restore process. Let's take a look at how to accomplish all of this in code.

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

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

• Since this is a storage-level operation for the Azure disks, the volumes containing database files need to be taken offline. You will need to update the disk number to match those in your VM.
  

  1	Write-Host "Offlining the volume…" -ForegroundColor Red Get-Disk -Number 4 | Set-Disk -IsOffline $True Get-Disk -Number 5 | Set-Disk -IsOffline $True

• Now that the database is offline and the disks holding the database files are also offline, you need to remove the disks from the virtual machine. You can do this by using the Remove-AzVMDataDisk command and specifying a reference to the virtual machine using the -VM parameter. Then, you can pass in the name of the disks to detach using the Name parameter. Once those disks are marked for removal, you can use the Update-AzVM command to apply this change to the virtual machine and remove the disks.
  

  1	Remove-AzVMDataDisk -VM $vm -Name $SourceDataDiskName Remove-AzVMDataDisk -VM $vm -Name $SourceLogDiskName Update-AzVM -ResourceGroupName $ResourceGroupName -VM $vm

• To create our two new Azure virtual disks from our snapshots, we define a disk configuration using New-AzDiskConfig. The -SourceResourceID is the snapshot's ID. We should also consider the configuration of the previous disks for location, storage type (SKU), and availability zone. This needs to match the previous disks' configuration if you are attaching back to the same virtual machine and your performance requirements are still the same. Then, we pass the configuration into the New-AzDisk cmdlet to create the new disk. These new virtual disks will have the data on them from the point in time of the snapshots taken earlier.
  

  1

  $StorageType = 'Premium_LRS' $DataDiskName = 'DATA_CLONE' $DataDiskConfig = New-AzDiskConfig -SkuName $StorageType -Location $location -CreateOption Copy -SourceResourceId $DataSnapshot.Id -Zone 1 $DataDisk = New-AzDisk -ResourceGroupName $ResourceGroupName -DiskName $DataDiskName -Disk $DataDiskConfig $LogDiskName = 'LOG_CLONE' $LogDiskConfig = New-AzDiskConfig -SkuName $StorageType -Location $location -CreateOption Copy -SourceResourceId $LogSnapshot.Id -Zone 1 $LogDisk = New-AzDisk -ResourceGroupName $ResourceGroupName -DiskName $LogDiskName -Disk $LogDiskConfig

• Now that the disks are created with the data from the snapshots created earlier, let's attach them to the VM using Add-AzVMDataDisk. One of the best practices for SQL Server Virtual Machines in Azure is to use read-caching on the database volume, which can be enabled by using the -Caching parameter and setting the caching policy to ReadWrite on the Set-AzVMDataDisk command. However, caching should not be used on the transaction log volume. After attaching the disks and setting the caching policy, you need to update your VM's disk configuration with Update-AzVM.
  

  1

  Add-AzVMDataDisk -Name $DataDiskName -CreateOption Attach -VM $vm -ManagedDiskId $DataDisk.Id -Lun 1 Add-AzVMDataDisk -Name $LogDiskName -CreateOption Attach -VM $vm -ManagedDiskId $LogDisk.Id -Lun 2 Set-AzVMDataDisk -VM $vm -Name 'DATA_CLONE' -Caching ReadWrite Update-AzVM -VM $vm -ResourceGroupName $ResourceGroupName

• With all the Azure storage work completed, let's go online with the disks. The database files are now available inside the OS, reverted back in time, and ready to perform our T-SQL-based snapshot point-in-time restore process.
  

  1	Write-Host "Onlining the volume…" -ForegroundColor Red Get-Disk -Number 4 | Set-Disk -IsOffline $False Get-Disk -Number 5 | 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.
  

  1	$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 0.571 seconds (0.000 MB/sec).

• The full restore duration includes the instant snapshot revert and about half a second of database operations. That's just wild!
• With the full restore completed, let's check the database's current status. It's in RESTORING mode.

• 1	Get-DbaDbState -SqlInstance $SqlInstance -Database $DbName Access : MULTI_USER ComputerName : Sql1 DatabaseName : Database1 InstanceName : MSSQLSERVER RW : READ_WRITE SqlInstance : Sql1 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.
  

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

• Online the database; we've just completed a point-in-time restore.
  

  1	$Query = "RESTORE DATABASE $DbName WITH RECOVERY" Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query -Verbose VERBOSE: RESTORE DATABASE successfully processed 0 pages in 1.596 seconds (0.000 MB/sec).

• Let's again check the current state of the database…it's ONLINE.
  

  1	Get-DbaDbState -SqlInstance $SqlInstance -Database 'Database1' Access : MULTI_USER ComputerName : SqlServer1 DatabaseName : Database1 InstanceName : MSSQLSERVER RW : READ_WRITE SqlInstance : Sql1 Status : ONLINE

Wrapping Things Up

In this post, which is the third in our series, we used the T-SQL snapshot backup feature for point-in-time recovery of a database in an Azure Virtual Machine. 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 on GitHub.

Public link

Please use the above public link if you want to share this noodl on another website.