Using T-SQL Snapshot Backup - Point in Time Recovery - Azure Edition

2024-09-03

FlashArray, Powershell, SQL Server, Using T-SQL Snapshot Backup, Azure

Page content

Introduction

In this post, the third in our series on using T-SQL Snapshot Backup, 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 here.

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 and 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 a connections to our SQL Server and also Azure. I’m utilizing dbatools and the Az PowerShell module to manage this process.

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.
```
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,Sql1_DataDisk_1. Azure tools and documentation refer to any non-operating system volume as a data disk.
```
$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.

$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 confuration 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 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.
```
$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.

$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 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.
```
$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.
```
$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.
```
$LogBackup = Backup-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Type Log -Path $BackupShare -CompressBackup
```

With snapshot created, you can used 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.

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:39 PM                         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:42 PM                         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 being copied.

Let’s check out the error log to see what SQL Server thinks happened,

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

Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:32 PM spid64  Setting database option suspend_for_snapshot_backup to ON for database 'Database1'.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:32 PM spid64  Database 'Database1' acquired suspend locks in session 64.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:32 PM 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:32 PM spid64  Database 'Database1' successfully suspended for snapshot backup in session 64.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:48 PM 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:48 PM spid64  I/O was resumed on database Database1. No user action is required.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:48 PM spid64  Database 'Database1' released suspend locks in session 64.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:48 PM spid64  Database 'Database1' originally suspended for snapshot backup in session 64 successfully resumed in session 64.
Sql1         MSSQLSERVER  Sql1        9/3/2024 12:37:48 PM 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:48 PM 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.

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.

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.

$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 needs to be taken offline. You will need to update the disk number to match those in your VM
```
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.
```
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 -SourceRescourceID 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.
```
$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 toReadWrite 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.
```
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.
```
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.
```
$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. Its in RESTORING mode.

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.

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

Online the database, we’ve just completed a point in time restore

$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…its ONLINE

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 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?”.