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