How to Back Up All Azure SQL Databases at Once? PowerShell Automated bacpac Export

Conclusion#

This script controls the export scope via the DatabaseName parameter: if a value is provided, only the specified DB is exported; if it’s empty, all user databases under the entire Azure SQL Server are automatically exported.

Where is this useful?#

To test if a single DB export works correctly
To back up all user databases on an entire Azure SQL Server at once
To turn the backup process into a reusable PowerShell script
To retain export results and error messages for each run, facilitating future investigation

Steps#

First, prepare the Azure resource name, Storage location, and SQL Admin account, and add the DatabaseName parameter. This parameter is the core of this post: an empty value means all databases, while a value means a single specified database. Then, enter the SQL Admin password and retrieve the Storage Account Key, which will be used later for bacpac export.

1
# ===== 基本參數 =====
2
$ResourceGroupName   = "your-rg"
3
$ServerName          = "your-sql-server"
4
$DatabaseName        = ""   # 空值 = 全部 DB；有值 = 指定單一 DB
5
$StorageAccountName  = "yourstorageacct"
6
$ContainerName       = "sql-backup"
7
$SqlAdminLogin       = "your_sql_admin"
8

9
# 會跳出輸入框，請輸入 Azure SQL admin 密碼
10
$SqlAdminPassword = Read-Host "Enter Azure SQL admin password" -AsSecureString
11

12
# 取得 Storage Account Key
13
$StorageKey = (Get-AzStorageAccountKey `
14
    -ResourceGroupName $ResourceGroupName `
15
    -Name $StorageAccountName)[0].Value

2. Determine Which Databases to Process Based on DatabaseName#

This step only processes the list, not the export itself. If DatabaseName is empty, it retrieves all databases under the logical server, excluding master; if a value is provided, it creates a list for only that single database. This allows the subsequent export process to be shared, avoiding the need to write two separate scripts.

1
if ([string]::IsNullOrWhiteSpace($DatabaseName)) {
2
    # 抓出此 logical server 底下的資料庫，排除 master
3
    $databases = Get-AzSqlDatabase `
4
        -ResourceGroupName $ResourceGroupName `
5
        -ServerName $ServerName |
6
        Where-Object { $_.DatabaseName -ne "master" }
7
}
8
else {
9
    # 若有指定 DB，則只建立單一清單
10
    $databases = @(
11
        [PSCustomObject]@{
12
            DatabaseName = $DatabaseName
13
        }
14
    )
15
}

3. Export bacpac to Blob Storage One by One#

Each database is appended with a timestamp to prevent overwriting files with the same name. Then, New-AzSqlDatabaseExport is called to submit an export request, writing the bacpac to the specified Blob Container. This section is the core and most practical part.

1
# 建立結果紀錄
2
$results = @()
3

4
foreach ($db in $databases) {
5
    $CurrentDatabaseName = $db.DatabaseName
6
    $TimeStamp = Get-Date -Format "yyyyMMdd-HHmmss"
7
    $BacpacFileName = "$CurrentDatabaseName-$TimeStamp.bacpac"
8
    $StorageUri = "<https://$StorageAccountName.blob.core.windows.net/$ContainerName/$BacpacFileName>"
9

10
    Write-Host "Starting export for $CurrentDatabaseName ..."
11

12
    try {
13
        $exportRequest = New-AzSqlDatabaseExport `
14
            -ResourceGroupName $ResourceGroupName `
15
            -ServerName $ServerName `
16
            -DatabaseName $CurrentDatabaseName `
17
            -StorageKeyType "StorageAccessKey" `
18
            -StorageKey $StorageKey `
19
            -StorageUri $StorageUri `
20
            -AdministratorLogin $SqlAdminLogin `
21
            -AdministratorLoginPassword $SqlAdminPassword

4. Continuously Query Status and Organize Results#

Export is not a synchronous operation, so Get-AzSqlDatabaseImportExportStatus is used to poll the status. Once completed, each DB’s status, output location, and error message are collected into a results table, which is then output uniformly. This prevents a situation where you only see one DB stuck without knowing the status of others.

1
        # 輪詢匯出狀態
2
        do {
3
            Start-Sleep -Seconds 15
4
            $status = Get-AzSqlDatabaseImportExportStatus `
5
                -OperationStatusLink $exportRequest.OperationStatusLink
6

7
            Write-Host "$CurrentDatabaseName => $($status.Status)"
8
        } while ($status.Status -eq "InProgress")
9

10
        $results += [PSCustomObject]@{
11
            DatabaseName = $CurrentDatabaseName
12
            Status       = $status.Status
13
            StorageUri   = $StorageUri
14
            ErrorMessage = $status.ErrorMessage
15
        }
16
    }
17
    catch {
18
        $results += [PSCustomObject]@{
19
            DatabaseName = $CurrentDatabaseName
20
            Status       = "Failed"
21
            StorageUri   = $StorageUri
22
            ErrorMessage = $_.Exception.Message
23
        }
24
    }
25
}
26

27
$results | Format-Table -AutoSize

Additional Notes#

It’s recommended to exclude master directly, as it usually has no practical value when backed up as a bacpac.
It’s recommended to use $CurrentDatabaseName and avoid overwriting the original $DatabaseName parameter within the foreach loop to prevent confusion later.
If you encounter issues like unsupported schema, cross-database references, or external objects, export failures are often due to bacpac limitations, not PowerShell syntax errors.

Commands / Examples Summary#

Click to expand

1
# 安裝 Az PowerShell 模組
2
Install-Module -Name Az -Scope CurrentUser
3

4
# 匯入 Az 模組
5
Import-Module Az
6

7
# 登入 Azure
8
Connect-AzAccount
9

10
# ===== 基本參數 =====
11
$ResourceGroupName   = "your-rg"
12
$ServerName          = "your-sql-server"
13
$DatabaseName        = ""   # 空值 = 全部 DB；有值 = 指定單一 DB
14
$StorageAccountName  = "yourstorageacct"
15
$ContainerName       = "sql-backup"
16
$SqlAdminLogin       = "your_sql_admin"
17

18
# 會跳出輸入框，請輸入 Azure SQL admin 密碼
19
$SqlAdminPassword = Read-Host "Enter Azure SQL admin password" -AsSecureString
20

21
# 取得 Storage Account Key
22
$StorageKey = (Get-AzStorageAccountKey `
23
    -ResourceGroupName $ResourceGroupName `
24
    -Name $StorageAccountName)[0].Value
25

26
# 抓取 DB 清單：空值 = 全部 DB；有值 = 單一 DB
27
if ([string]::IsNullOrWhiteSpace($DatabaseName)) {
28
    $databases = Get-AzSqlDatabase `
29
        -ResourceGroupName $ResourceGroupName `
30
        -ServerName $ServerName |
31
        Where-Object { $_.DatabaseName -ne "master" }
32
}
33
else {
34
    $databases = @(
35
        [PSCustomObject]@{
36
            DatabaseName = $DatabaseName
37
        }
38
    )
39
}
40

41
# 建立結果紀錄
42
$results = @()
43

44
foreach ($db in $databases) {
45
    $CurrentDatabaseName = $db.DatabaseName
46
    $TimeStamp = Get-Date -Format "yyyyMMdd-HHmmss"
47
    $BacpacFileName = "$CurrentDatabaseName-$TimeStamp.bacpac"
48
    $StorageUri = "<https://$StorageAccountName.blob.core.windows.net/$ContainerName/$BacpacFileName>"
49

50
    Write-Host "Starting export for $CurrentDatabaseName ..."
51

52
    try {
53
        $exportRequest = New-AzSqlDatabaseExport `
54
            -ResourceGroupName $ResourceGroupName `
55
            -ServerName $ServerName `
56
            -DatabaseName $CurrentDatabaseName `
57
            -StorageKeyType "StorageAccessKey" `
58
            -StorageKey $StorageKey `
59
            -StorageUri $StorageUri `
60
            -AdministratorLogin $SqlAdminLogin `
61
            -AdministratorLoginPassword $SqlAdminPassword
62

63
        # 輪詢匯出狀態
64
        do {
65
            Start-Sleep -Seconds 15
66
            $status = Get-AzSqlDatabaseImportExportStatus `
67
                -OperationStatusLink $exportRequest.OperationStatusLink
68

69
            Write-Host "$CurrentDatabaseName => $($status.Status)"
70
        } while ($status.Status -eq "InProgress")
71

72
        $results += [PSCustomObject]@{
73
            DatabaseName = $CurrentDatabaseName
74
            Status       = $status.Status
75
            StorageUri   = $StorageUri
76
            ErrorMessage = $status.ErrorMessage
77
        }
78
    }
79
    catch {
80
        $results += [PSCustomObject]@{
81
            DatabaseName = $CurrentDatabaseName
82
            Status       = "Failed"
83
            StorageUri   = $StorageUri
84
            ErrorMessage = $_.Exception.Message
85
        }
86
    }
87
}
88

89
# 顯示結果
90
$results | Format-Table -AutoSize
91

92
# ===== 補充查詢 / 驗證指令 =====
93

94
# 查詢某台 SQL Server 底下所有 DB
95
Get-AzSqlDatabase `
96
    -ResourceGroupName "your-rg" `
97
    -ServerName "your-sql-server"
98

99
# 查詢指定 DB
100
Get-AzSqlDatabase `
101
    -ResourceGroupName "your-rg" `
102
    -ServerName "your-sql-server" `
103
    -DatabaseName "your-db-name"
104

105
# 查詢 Storage Account Key
106
Get-AzStorageAccountKey `
107
    -ResourceGroupName "your-rg" `
108
    -Name "yourstorageacct"
109

110
# 建立 Storage Context
111
$ctx = New-AzStorageContext `
112
    -StorageAccountName "yourstorageacct" `
113
    -StorageAccountKey "your-storage-key"
114

115
# 查詢 Blob Container 是否存在
116
Get-AzStorageContainer `
117
    -Name "sql-backup" `
118
    -Context $ctx
119

120
# 建立 Blob Container
121
New-AzStorageContainer `
122
    -Name "sql-backup" `
123
    -Context $ctx
124

125
# 手動測試單一 DB 匯出
126
$DatabaseName = "appdb"
127

128
# 手動切換成匯出全部 DB
129
$DatabaseName = ""
130

131
# 手動查某次匯出狀態
132
Get-AzSqlDatabaseImportExportStatus `
133
    -OperationStatusLink "貼上 OperationStatusLink"
134

135
# 查看匯出後的 bacpac 檔案
136
Get-AzStorageBlob `
137
    -Container "sql-backup" `
138
    -Context $ctx

Conclusion#

This version isn’t overly complex; it simply gathers all necessary commands in one place. This makes it easier for single-file testing, full-site backups, or scheduling, reducing the need for repeated fixes.

Conclusion#

Where is this useful?#

Steps#

1. Define Parameters and Login Information#

2. Determine Which Databases to Process Based on DatabaseName#

3. Export bacpac to Blob Storage One by One#

4. Continuously Query Status and Organize Results#

Additional Notes#

Commands / Examples Summary#

Conclusion#