Azure SQL DB Backup Sample

Understanding Retention Policies

Azure SQL Database automatically performs backups of your databases. The default retention period varies based on the service tier:

• Basic, Standard, Premium: Point-in-time restore (PITR) backups are kept for 7 to 35 days.
• Hyperscale: PITR backups are kept for 7 to 35 days.
• Business Critical, General Purpose: PITR backups are kept for 7 to 35 days.

You can configure long-term retention (LTR) policies to store backups for longer periods (up to 10 years). This is crucial for compliance and disaster recovery scenarios.

Performing a Manual Backup (Azure Portal)

While Azure SQL Database handles automatic backups, you can initiate a manual backup for specific needs. This is typically done through the Azure portal or programmatically.

1. Navigate to your Azure SQL Database in the Azure portal.
2. Under the "Data management" section, select "Backups".
3. Choose "New backup".
4. Specify the backup name, and select the storage account where the backup file will be saved (e.g., Azure Blob Storage).
5. Click "Create".

Note: Manual backups are separate from the automatic PITR backups.

Programmatic Backup using Azure CLI

You can automate backup operations using tools like Azure CLI. Below is an example of how to initiate a manual backup of a specific database to blob storage.

Example Command:

az sql db backup create --resource-group myResourceGroup --server myServer --database myDatabase --backup-file-name myDatabaseBackup-$(date +%Y-%m-%d-%H-%M-%S).bacpac --storage-uri https://myaccount.blob.core.windows.net/backupcontainer/myDatabaseBackup.bacpac --storage-key "YOUR_STORAGE_KEY"

Replace placeholders like myResourceGroup, myServer, myDatabase, myaccount, backupcontainer, and YOUR_STORAGE_KEY with your actual Azure resource details.

To simulate execution:

Run Sample Command

Programmatic Backup using PowerShell

Azure PowerShell also provides cmdlets to manage SQL Database backups.

Example Script:

# Connect to Azure
            Connect-AzAccount

            # Set subscription context (if you have multiple)
            # Set-AzContext -SubscriptionId "YOUR_SUBSCRIPTION_ID"

            # Define variables
            $resourceGroupName = "myResourceGroup"
            $serverName = "myServer"
            $databaseName = "myDatabase"
            $backupFileName = "myDatabaseBackup-$(Get-Date -Format 'yyyy-MM-dd-HH-mm-ss').bacpac"
            $storageUri = "https://myaccount.blob.core.windows.net/backupcontainer/$backupFileName"
            $storageKey = "YOUR_STORAGE_KEY"

            # Initiate manual backup
            New-AzSqlDatabaseExport `
                -ResourceGroupName $resourceGroupName `
                -ServerName $serverName `
                -DatabaseName $databaseName `
                -StorageUri $storageUri `
                -StorageKey $storageKey `
                -Edition Premium `
                -ServiceObjective S2 `
                -AdministratorLogin "sqladmin" `
                -AdministratorLoginPassword "yourpassword"

            Write-Host "Backup operation initiated for '$databaseName'."

Remember to replace the placeholder values with your specific Azure resource details.

To simulate execution:

Run Sample Script

Restoring from a Backup

Restoring a database from a point-in-time backup or a manual backup is a common operation. You can do this through the Azure portal, Azure CLI, or PowerShell. The process involves selecting the source database (or backup file) and specifying the target database name and server.

Refer to Azure SQL Database recovery documentation for detailed steps.