Deploying DACPAC as new database in Azure
Posted onThe following powershell script can be used to deploy a DACPAC file to a new (or replace an existing) database in Azure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
param( [string]$rg = $( throw "Missing required resource group parameter"), [string]$sqlserver = $( throw "Missing required SQL Server parameter"), [string]$dbName = $( throw "Missing required DB Name parameter"), [string]$dacpac = $( throw "Missing required DACPAC file location") ) function Login { $needLogin = $true Try { $content = Get-AzureRmContext if ($content) { $needLogin = ([string]::IsNullOrEmpty($content.Account)) } } Catch { if ($_ -like "*Login-AzureRmAccount to login*") { $needLogin = $true } else { throw } } if ($needLogin) { $azureCred = Get-Credential -message "Enter Azure Credentials" Login-AzureRmAccount -Credential $azureCred } } Login $database = Get-AzureRMSqlDatabase -ServerName $sqlserver -DatabaseName $dbName -ResourceGroupName $rg -ErrorAction SilentlyContinue if ($database) { $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes","" $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No","" $choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no) $caption = "Warning!" $message = "The database $dbName already exists. Do you want to replace it?" $result = $Host.UI.PromptForChoice($caption,$message,$choices,0) if($result -eq 0) { Write-Host "Deleting database $dbname" Remove-AzureRmSqlDatabase -ServerName $sqlserver -DatabaseName $dbName -ResourceGroupName $rg -Force -ErrorAction Stop | out-null } else { exit } } Write-Host "Creating database $dbname" New-AzureRmSqlDatabase -ResourceGroupName $rg -ServerName $sqlserver -DatabaseName $dbName -RequestedServiceObjectiveName "S0" | out-null $sqlCred = Get-Credential -Message "Enter SQL Server credentials" $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($sqlCred.Password) $plainPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) $connectionString = "Server=tcp:$sqlserver.database.windows.net,1433;Initial Catalog=$dbname;Persist Security Info=False;User ID=$($sqlCred.UserName);Password=$plainPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" & "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe" /Action:publish /SourceFile:"$dacpac" /TargetConnectionString:"$connectionString" |
Notice that Azure and SQL credentials are not taken as parameter, but instead the Get-Credential command is called. It works well in desktop, but not necessarily as an automated script without human intervention. The code can be easily modified for that purpose, though. The script above can be executed with the following command:
|
1 |
.\script-name.ps1 <RESOURCE-GROUP> <SERVER> <DATABASE> <PATH-TO-DACPAC> |