How to export on premise database as bacpac directly to AZURE blob using command line

2
votes

We were able to create the bacpac using the below command,

C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin>

sqlpackage.exe /Action:Export /ssn:. /sdn:demo /tf:d:\bacpac\demo.bacpac

However we want to automate the below action,

enter image description here

Also can this be done via SSMS using something similar

BACKUP DATABASE demo

TO URL = @FullName

WITH CREDENTIAL = democredential;

2
sql-serverexportazure-sql-databasebacpac

2 Answers

1
votes

You can use Azcopy to upload those bacpac files to an Azure BLOB Storage account as shown below:

AzCopy /Source:"d:\bacpac" /Dest:"https://exampleaccount.blob.core.windows.net/bacpacs" /DestKey:storageaccountkey /Pattern:*.bacpac

Hope this helps.

1
votes

You can achieve this using Powershell Script,

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

To check the Status of the Export you can use the below one

$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
    Start-Sleep -s 10
    $exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
    [Console]::Write(".")   
}
[Console]::WriteLine("")
$exportStatus

Refer here

If you want to Automate this by using Azure Automation RunBook