Previously we ran through the basics of Azure SQL database backups including the types of backup you can take, how to configure those backups, as well as a few use cases. Now, let’s dive deep into the world of Azure SQL backups (and get some PowerShell scripting going). We’re not just skimming the surface – we’re going to explore advanced strategies, uncover potential limitations, and discuss solutions that go beyond the basics. Grab your favorite caffeinated beverage ☕, and let’s get technical!
Be sure to check out our basics on SQL database backup, including a review of the types of SQL databases out there and and options on restore, whether it be in another geographic region or using a longterm retention policy.
Azure SQL Backup: The Nitty-Gritty Details
Before we get into the advanced backup strategies, it’s crucial to understand how Azure SQL Database backups work at a fundamental level. This knowledge forms the foundation for implementing more sophisticated backup and recovery solutions. Let’s peel back the layers of Azure’s backup features, exploring the types of backups it performs, their frequency, and the underlying technology that makes it all possible.
When you create a new Azure SQL database, Azure automatically creates three types of backups:
- Full backups: Weekly
- Differential backups: Every 12 hours
- Transaction log backups: Every 5-10 minutes
These backups use Azure’s fork of SQL Server’s VDI (Virtual Device Interface) to create crash-consistent backups without locking the database. Each backup:
- Creates a snapshot of the data files.
- Reads the snapshot to create the backup, allowing normal operations to continue.
- Writes the backup to RA-GRS (Read-Access Geo-Redundant Storage) blob storage.
This process is robust and works well for many scenarios, but as we’ll see, there are situations where you might need more advanced strategies. Understanding these basics will help you appreciate why and when you might need to go beyond the default backup options.
Let’s break down each of these components in more detail…
Understanding the Limitations of Native Azure SQL Backup
1. The 35-Day Point-in-Time Restore Limit
Azure SQL’s native point-in-time restore is limited to 35 days. For longer retention, you’ll need to use long-term retention (LTR) policies. Here’s how you can set one up:
# Set long-term retention policy
# Retain weekly backups for 10 weeks
# Retain monthly backups for 12 months
# Retain yearly backups for 5 years
Set-AzSqlDatabaseLongTermRetentionPolicy -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -WeeklyRetention "P10W" -MonthlyRetention "P12M" -YearlyRetention "P5Y" -WeekOfYear 5
2. The All-or-Nothing Recovery Challenge
Azure’s native solution requires you to restore the entire database. If you need just one table from last week, you’ll have to restore the whole database to a new instance, extract what you need, and then delete the restored copy. This can be time-consuming and resource-intensive.
3. Backup Interval Considerations
Azure’s transaction log backups occur every 5-10 minutes. For some critical systems, this might not be frequent enough. It’s important to evaluate your Recovery Point Objective (RPO) and consider if this meets your needs.
Implementing Advanced Backup Strategies
Now that we understand some of the limitations, let’s explore advanced strategies to enhance your Azure SQL backup game.
1. Multi-Tiered Backup Strategy
Implement a strategy that combines short-term, medium-term, and long-term backups:
- Short-Term: Use Azure’s automatic backups for point-in-time restores within 35 days.
- Medium-Term: Implement custom scripts to create daily backups and retain them for 3-6 months. Here’s a sample PowerShell script to create a custom backup that you could use for your medium-term tier.
$bacpacUri = "https://$storageAccountName.blob.core.windows.net/backups/$databaseName_$(Get-Date -Format 'yyyyMMdd').bacpac"
New-AzSqlDatabaseExport -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -StorageKeyType "StorageAccessKey" -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -StorageAccountName $storageAccountName).Value[0] -StorageUri $bacpacUri -AdministratorLogin "sqladmin" -AdministratorLoginPassword $adminPassword
- Long-Term: Use LTR policies for yearly backups, retained for multiple years.
2. Leveraging Point-in-Time Restore
While Azure SQL Database doesn’t support database snapshots, it offers a powerful alternative: point-in-time restore. This feature allows you to create a new database from an existing one at a specific point in time within the backup retention period.
Point-in-time restore provides a read-write copy of your database as it existed at a chosen moment, which can be particularly useful for testing, reporting, or recovering from unintended changes.
Here’s an example of how to perform a point-in-time restore using PowerShell:
# Connect to Azure
Connect-AzAccount
# Set variables
$resourceGroupName = "YourResourceGroup"
$serverName = "YourServerName"
$databaseName = "YourDatabaseName"
$restorePoint = (Get-Date).AddHours(-1) # Restore to 1 hour ago
$newDatabaseName = "$databaseName-PITRestore"
# Perform the point-in-time restore
$database = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime $restorePoint -ResourceGroupName $resourceGroupName -ServerName $serverName -TargetDatabaseName $newDatabaseName -ResourceId $database.ResourceId
When using point-in-time restore, keep in mind:
- The new database is a full copy and is read-write, unlike snapshots which are read-only.
- It creates a new database, so you’ll need to account for additional storage costs.
- The restore point can be any time within the backup retention period (default is 7 days for most tiers).
- It’s a great way to recover from accidental data changes or deletions.
To leverage point-in-time restore effectively:
- Use it to create test environments with production data as of a specific moment.
- Employ it for reporting purposes when you need a consistent view of the data at a particular time.
- Utilize it as part of your disaster recovery strategy to quickly recover from data corruption or unintended changes.
Remember, while point-in-time restore is powerful, it’s not instantaneous like snapshots would be. The restore operation can take some time depending on the size of your database and the chosen restore point.
By understanding and effectively using point-in-time restore, you can enhance your testing processes, improve reporting accuracy, and provide an additional layer of protection against unwanted changes in your Azure SQL databases.
- Use Managed Identity for backup automation: Instead of embedding credentials in scripts, use Azure’s managed identities to grant secure access to storage accounts for automated export/import operations.
- Monitor backup and restore performance: Use Azure Monitor to track backup/restore operations’ performance and set up alerts for failures or unusually long operation durations.
- Use geo-redundant storage intelligently: While RA-GRS is great for durability, some high-security scenarios benefit from using zone-redundant storage (ZRS) or locally redundant storage (LRS) to optimize performance and costs.
- Enable backup encryption for sensitive databases: Encrypt database backups with Transparent Data Encryption (TDE). For additional security, use customer-managed keys (CMKs) stored in Azure Key Vault.
- Incorporate immutable storage for critical backups: For long-term backups to protect against accidental deletions or ransomware. This ensures backups remain tamper-proof during the retention period.
3. Implementing Cross-Region Replication
For critical databases, consider implementing active geo-replication. This not only serves as a disaster recovery solution but can also be used as a source for backups, reducing the load on your primary database.
# Set up active geo-replication
$primaryDatabase = Get-AzSqlDatabase `
-ResourceGroupName "PrimaryResourceGroup" `
-ServerName "PrimaryServer" `
-DatabaseName "PrimaryDatabase"
New-AzSqlDatabaseSecondary `
-ResourceGroupName "SecondaryResourceGroup" `
-ServerName "SecondaryServer" `
-DatabaseName "SecondaryDatabase" `
-PartnerResourceGroupName "PrimaryResourceGroup" `
-PartnerServerName "PrimaryServer" `
-PartnerDatabaseName "PrimaryDatabase"
Advanced Techniques for Granular Recovery
While Azure’s native backup and recovery tools are designed for restoring entire databases, you can achieve more granular control using a few techniques.
Table-Level Recovery
If you need to recover specific tables, you can leverage a temporary restore. For example, maybe you have a SalesDb and want to recover a table called Orders. You’ve already restored the entire database called SalesDB_Recovery from your production database.
Here are some steps you might take:
- Generate INSERT Statements in your recovery DB.
-- Connect to SalesDB_Recovery
USE SalesDB_Recovery;
GO
-- Generate INSERT statements for Orders table
SELECT 'INSERT INTO Orders (' + STRING_AGG(COLUMN_NAME, ', ') + ') VALUES (' + STRING_AGG( CASE WHEN DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'datetime', 'uniqueidentifier') THEN '''' + REPLACE(CAST(VALUE AS VARCHAR(MAX)), '''', '''''') + '''' ELSE CAST(VALUE AS VARCHAR(MAX)) END, ', ') + ');'
FROM ( SELECT c.COLUMN_NAME, t.VALUE, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS c CROSS APPLY ( SELECT * FROM Orders ) AS t WHERE c.TABLE_NAME = 'Orders'
) AS x
GROUP BY x.ORDINAL_POSITION
ORDER BY x.ORDINAL_POSITION
This query will output a series of INSERT INTO statements for each row in the Orders table within SalesDB_Recovery.
2. Execute INSERT Statements against production.
-- Connect to the production SalesDB
USE SalesDB;
GO
-- (Paste the INSERT statements generated in the previous step here)
Copy and paste the output from the previous query into a new query window connected to your production SalesDB. Execute this script to insert the recovered Orders data.
Object-Level Recovery
Recovering individual database objects like stored procedures or functions is also possible. You can script out their definitions from a restored copy of the database and then re-apply those scripts to your production environment.
Example: Scripting Out Stored Procedures with PowerShell
Here’s a PowerShell script you can adapt to extract all stored procedure definitions from a restored database:
$server = "YourServerName"
$database = "YourDatabaseName"
$outputFile = "C:\\Temp\\StoredProcedures.sql"
$query = @"
SET NOCOUNT ON
GO
USE [$database]
GO
DECLARE @name VARCHAR(256)
DECLARE @SQL VARCHAR(MAX)
DECLARE c1 CURSOR FOR
SELECT name FROM sys.procedures WHERE is_ms_shipped = 0
OPEN c1
FETCH NEXT FROM c1 INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE [$database]
GO
'
EXEC ('USE [$database]
SELECT @SQL = @SQL + ''
'' + definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(''' + @name + ''')')
PRINT @SQL
FETCH NEXT FROM c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
GO
"@
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -Username $adminUsername -Password $adminPassword.GetNetworkCredential().Password | Out-File -FilePath $outputFile
This script will generate a SQL file containing all user-defined stored procedures, which you can then selectively apply to your production database.
A More Holistic and Secure Azure SQL Database Backup Approach with N2W
We’ve journeyed through the advanced world of Azure SQL backups, uncovering limitations and exploring strategies that go beyond the basics. From multi-tiered backup approaches to granular recovery techniques and automated workflows, there are many ways to enhance your Azure SQL backup strategy.
Remember, the key to a robust backup strategy lies in understanding your specific needs, implementing appropriate solutions, and continuously monitoring and improving your processes.
Whether you’re using Azure’s native tools, custom scripts, or third-party cloud-native solutions like N2W, the goal is the same: ensuring your data is protected, recoverable, and compliant with your business needs.
N2W allows for efficient backup and recovery of SQL databases by leveraging incremental snapshots in Azure and providing agentless SQL backups for a holistic approach. Users not only improve performance, they scale quicky and easily, ensuring their critical databases are resilient.
In addition, businesses that require features like multi-factor authentication (MFA) (a must as ransomware threats become more prevalent), N2W provides this extra layer of protection to user accounts.
Finally, being cloud native, N2W is agentless and therefore has the ability to scale seamlessly with your cloud environment and improve its performance. By leveraging Azure-native snapshots, backup and recovery of SQL databases are as efficient as they come. N2W ensures that your SQL databases can be frequently and flexibly backed up, as well as immediately restored.
Learn more about N2W for Azure Backup
Keep exploring, keep learning, and may your databases always be safe and recoverable!
