09/30/2024 | Press release | Distributed by Public on 09/30/2024 15:15
This blog on point-in-time recovery originally appeared on www.nocentino.com. It has been republished with permission from the author.
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 on GitHub.
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
1 |
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 |
1 |
$SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection |
1 |
Get-DbaDatabase -SqlInstance $SqlInstance -Database $DbName | Select-Object Name, SizeMB Name SizeMB -- --- Database1 3484091.81 |
Connect to Our Storage Environment
1 |
$Credential = Get-Credential $FlashArray = Connect-Pfa2Array -EndPoint $ArrayName -Credential $Credential -IgnoreCertificateError |
Taking a T-SQL-based Snapshot Backup
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 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. |
1 |
$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 |
1 |
$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). |
1 |
$LogBackup = Backup-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Type Log -Path $BackupShare -CompressBackup |
What Does SQL Server Think Happened?
1 |
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).… |
1 |
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 |
1 |
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 omitted... BackupPath : \\w2016-anthony\SHARE\BACKUP\Database1_20240901T1235110742.bkm BackupSize : 1.39 TB CompressedBackupSize : 1.39 TB |
Read More from This Series
Using T-SQL Snapshot Backup: Are Snapshots Backups?
Using T-SQL Snapshot Backup: Point-in-time Recovery
Using T-SQL Snapshot Backup: Point-in-time Recovery - Azure Edition
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.
1 |
$Query = "ALTER DATABASE $DbName SET OFFLINE WITH ROLLBACK IMMEDIATE" Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query |
1 |
Get-Disk | Where-Object { $_.SerialNumber -eq $TargetDisk } | Set-Disk -IsOffline $True |
1 |
$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 |
1 |
New-Pfa2Volume -Array $FlashArray -Name $FlashArrayDbVol -SourceName ($SnapshotName + ".$FlashArrayDbVol") -Overwrite $true |
1 |
Get-Disk | Where-Object { $_.SerialNumber -eq $TargetDisk} | Set-Disk -IsOffline $False |
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 1.184 seconds (0.000 MB/sec). |
1 |
Get-DbaDbState -SqlInstance $SqlInstance -Database $DbName Access : MULTI_USER ComputerName : SqlServer1 DatabaseName : Database1 InstanceName : MSSQLSERVER RW : READ_WRITE SqlInstance : SqlServer1 Status : RESTORING |
1 |
Restore-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Path $LogBackup.BackupPath -NoRecovery -Continue |
1 |
$Query = "RESTORE DATABASE $DbName WITH RECOVERY" Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query |
1 |
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 400ms-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.
1 |
$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 on GitHub.
Cyber Resilience & Risk Reduction
Boost cyber resilience and reduce risks with solutions that protect your organization from threats, ensure data recovery, and maintain business continuity.