Published
Mar 13 2019 06:59 PM
2,999
Views
First published on MSDN on Mar 25, 2018
We noticed a few requests come to our support queue asking for a feasibility to export Azure SQL Database to local path directly, so following steps below to build PowerShell script that can do that job to copy Azure SQL Database to another db for consistency, then export Azure SQL Database to blob storage, later connect to a single or all storage container and download blob files locally:
First you need to save your Azure login credential to be able to use saved profile credential later to automate login to Azure subscription, to do so, please follow below steps:
# Setup – First login manually per previous section
Add-AzureRmAccount
# Now save your context locally (Force will overwrite if there)
$path = "C:\Azure\PS\ProfileContext1.ctx"
Save-AzureRmContext -Path $path -Force
# Once the above two steps are done, you can simply import
$path = ‘C:\Azure\PS\ProfileContext1.ctx’
Import-AzureRmContext -Path $path
A new window opened to enter username & password to login to Azure Subscription
Once you can authenticated, your Azure subscription information listed and saved as shown below.
To verify, navigate to local path, you will find the ProfileContext.ctx file created as shown below.
[code language="PowerShell"]<#
.SYNOPSIS
Export Azure SQL Database to Blob storage and download the exported *.bacpac file from blob to local path
.DESCRIPTION
This PowerShell Script to export Azure SQL DB to a blob storage and then copies blobs from a single storage container to a local directoy.
The script supports the -Whatif switch so you can quickly see how complex the copy
operation would be.
.EXAMPLE
.\CopyFilesFromAzureStorageContainer -LocalPath "c:\users\myUserName\documents" `
-ServerName "myservername" -DatabaseName "myDBname" -ResourceGroupName "myresourcegroupname" -StorageAccountName "mystorageaccount" -ContainerName "myuserdocuments" -Force
#>;
[CmdletBinding(SupportsShouldProcess = $true)]
param(
# The destination path to copy files to.
[Parameter(Mandatory = $true)]
[string]$LocalPath,
# The name of the SQL Server to connect to.
[Parameter(Mandatory = $true)]
[string]$ServerName,
# The name of the SQL database to export.
[Parameter(Mandatory = $true)]
[string]$DatabaseName,
# The name of the resource group contians (SQL Server, SQL Database and Storage account name).
[Parameter(Mandatory = $true)]
[string]$ResourceGroupName,
# The name of the storage account to copy files from.
[Parameter(Mandatory = $true)]
[string]$StorageAccountName,
# The name of the SQL database to export.
[Parameter(Mandatory = $true)]
[string]$CopyDatabaseName,
# The name of the storage container to copy files from.
[Parameter(Mandatory = $true)]
[string]$ContainerName
)
# Login to Azure subscription
$path = ‘C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path
# $DatabaseName = "DBName"
# $ServerName = "ServerName"
# $ResourceGroupName = "ResourceGroupName"
# $StorageAccountName = "StorageAccountName"
# $ContainerName = "StorageContainerName"
# $LocalPath = "C:\LocalPath\"
# Create a credential
$ServerAdmin = "serverlogin"
$Password = ConvertTo-SecureString –String 'password' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential `
–ArgumentList $ServerAdmin, $Password
# Generate a unique filename for the BACPAC
$bacpacFilename = "$DatabaseName" + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Blob storage information
$StorageKey = "YOUR STORAGE KEY"
$BaseStorageUri = "https://STORAGE-NAME.blob.core.windows.net/BLOB-CONTAINER-NAME/"
$BacPacUri = $BaseStorageUri + $bacpacFilename
New-AzureRmSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -CopyResourceGroupName $ResourceGroupName -CopyServerName $ServerName -CopyDatabaseName $CopyDatabaseName
Write-Output "Azure SQL DB $CopyDatabaseName Copy completed"
# Create a request
$Request = New-AzureRmSqlDatabaseExport –ResourceGroupName $ResourceGroupName –ServerName $ServerName `
–DatabaseName $DatabaseName –StorageKeytype StorageAccessKey –StorageKey $StorageKey `
-StorageUri $BacPacUri –AdministratorLogin $Credential.UserName `
–AdministratorLoginPassword $Credential.Password
# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
Start-Sleep -s 10
}
$exportStatus
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Azure SQL DB Export $Status for "$DatabaseName""
}
else
{
Write-Output "Azure SQL DB Export Failed for "$DatabaseName""
}
# Download file from azure
Write-Output "Downloading"
$StorageContext = Get-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName
$StorageContext | Get-AzureStorageBlob -Container $ContainerName -blob $bacpacFilename | Get-AzureStorageBlobContent -Destination $LocalPath
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Blob $bacpacFilename Download $Status for "$DatabaseName" To $LocalPath"
}
else
{
Write-Output "Blob $bacpacFilename Download Failed for "$DatabaseName""
}
# Drop Copy Database after successful export
Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $CopyDatabaseName `
-Force
Write-Output "Azure SQL DB $CopyDatabaseName Deleted"[/code]
The above script can be saved and triggered manually, to automate the process and setup a schedule task, we can gain benefits from Windows Task Scheduler to run the PowerShell script on schedule of your preference. To do so, please follow steps below:
[code language="PowerShell"]<#
.SYNOPSIS
Export Azure SQL Database to Blob storage and download the exported *.bacpac file from blob to local path
.DESCRIPTION
This PowerShell Script to export Azure SQL DB to a blob storage and then copies blobs from a single storage container to a local directoy.
The script supports the -Whatif switch so you can quickly see how complex the copy
operation would be.
.EXAMPLE
.\CopyFilesFromAzureStorageContainer -LocalPath "c:\users\myUserName\documents" `
-ServerName "myservername" -DatabaseName "myDBname" -ResourceGroupName "myresourcegroupname" -StorageAccountName "mystorageaccount" -ContainerName "myuserdocuments" -Force
#>;
# Login to Azure subscription
$path = ‘C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path
$DatabaseName = " hidden"
$CopyDatabaseName = $DatabaseName + "_Copy"
$ServerName = "hidden"
$ResourceGroupName = "hidden"
$StorageAccountName = "hidden"
$ContainerName = "bacpac"
$LocalPath = "C:\localpath\"
# Create a credential
$ServerAdmin = "serverlogin"
$Password = ConvertTo-SecureString –String 'password' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential `
–ArgumentList $ServerAdmin, $Password
# Generate a unique filename for the BACPAC
$bacpacFilename = "$DatabaseName" + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Blob storage information
$StorageKey = "YOUR STORAGE KEY"
$BaseStorageUri = "https://StorageAccountName.blob.core.windows.net/ContainerName/"
$BacPacUri = $BaseStorageUri + $bacpacFilename
New-AzureRmSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -CopyResourceGroupName $ResourceGroupName -CopyServerName $ServerName -CopyDatabaseName $CopyDatabaseName
Write-Output "Azure SQL DB $CopyDatabaseName Copy completed"
# Create a request
$Request = New-AzureRmSqlDatabaseExport –ResourceGroupName $ResourceGroupName –ServerName $ServerName `
–DatabaseName $CopyDatabaseName –StorageKeytype StorageAccessKey –StorageKey $StorageKey `
-StorageUri $BacPacUri –AdministratorLogin $Credential.UserName `
–AdministratorLoginPassword $Credential.Password
# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
Start-Sleep -s 10
}
$exportStatus
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Azure SQL DB Export $Status for "$DatabaseName""
}
else
{
Write-Output "Azure SQL DB Export Failed for "$DatabaseName""
}
# Download file from azure
Write-Output "Downloading"
$StorageContext = Get-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName
$StorageContext | Get-AzureStorageBlob -Container $ContainerName -blob $bacpacFilename | Get-AzureStorageBlobContent -Destination $LocalPath
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Blob $bacpacFilename Download $Status for "$DatabaseName" To $LocalPath"
}
else
{
Write-Output "Blob $bacpacFilename Download Failed for "$DatabaseName""
}
# Drop Copy Database after successful export
Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $CopyDatabaseName `
-Force
Write-Output "Azure SQL DB $CopyDatabaseName Deleted"[/code]
Hope that you enjoyed that article, we appreciate your comments and feedback on this !!
We noticed a few requests come to our support queue asking for a feasibility to export Azure SQL Database to local path directly, so following steps below to build PowerShell script that can do that job to copy Azure SQL Database to another db for consistency, then export Azure SQL Database to blob storage, later connect to a single or all storage container and download blob files locally:
First you need to save your Azure login credential to be able to use saved profile credential later to automate login to Azure subscription, to do so, please follow below steps:
- Open Windows PowerShell ISE
- Copy/past below command
# Setup – First login manually per previous section
Add-AzureRmAccount
# Now save your context locally (Force will overwrite if there)
$path = &amp;amp;quot;C:\Azure\PS\ProfileContext1.ctx&amp;amp;quot;
Save-AzureRmContext -Path $path -Force
# Once the above two steps are done, you can simply import
$path = ‘C:\Azure\PS\ProfileContext1.ctx’
Import-AzureRmContext -Path $path
A new window opened to enter username & password to login to Azure Subscription
Once you can authenticated, your Azure subscription information listed and saved as shown below.
To verify, navigate to local path, you will find the ProfileContext.ctx file created as shown below.
- Now as ProfileContext data saved locally, please copy / past below PowerShell script to a new notepad and save it as CopyFilesFromAzureStorageContainer.ps1
- Note that values highlighted in Yellow need to fill before executing this PowerShell script Manually as in the below example
PS C:\bacpac> .\CopyFilesFromAzureStorageContinaer.ps1 -ResourceGroupName $ResourceGroupName -ServerName $SeverName -DatabaseName $DatabaseName -CopyDatabaseName $CopyDatabaseName -LocalPath $LocalPath -StorageAccountName $StorageAccountName -ContainerName $ContainerName
[code language="PowerShell"]<#
.SYNOPSIS
Export Azure SQL Database to Blob storage and download the exported *.bacpac file from blob to local path
.DESCRIPTION
This PowerShell Script to export Azure SQL DB to a blob storage and then copies blobs from a single storage container to a local directoy.
The script supports the -Whatif switch so you can quickly see how complex the copy
operation would be.
.EXAMPLE
.\CopyFilesFromAzureStorageContainer -LocalPath "c:\users\myUserName\documents" `
-ServerName "myservername" -DatabaseName "myDBname" -ResourceGroupName "myresourcegroupname" -StorageAccountName "mystorageaccount" -ContainerName "myuserdocuments" -Force
#>;
[CmdletBinding(SupportsShouldProcess = $true)]
param(
# The destination path to copy files to.
[Parameter(Mandatory = $true)]
[string]$LocalPath,
# The name of the SQL Server to connect to.
[Parameter(Mandatory = $true)]
[string]$ServerName,
# The name of the SQL database to export.
[Parameter(Mandatory = $true)]
[string]$DatabaseName,
# The name of the resource group contians (SQL Server, SQL Database and Storage account name).
[Parameter(Mandatory = $true)]
[string]$ResourceGroupName,
# The name of the storage account to copy files from.
[Parameter(Mandatory = $true)]
[string]$StorageAccountName,
# The name of the SQL database to export.
[Parameter(Mandatory = $true)]
[string]$CopyDatabaseName,
# The name of the storage container to copy files from.
[Parameter(Mandatory = $true)]
[string]$ContainerName
)
# Login to Azure subscription
$path = ‘C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path
# $DatabaseName = "DBName"
# $ServerName = "ServerName"
# $ResourceGroupName = "ResourceGroupName"
# $StorageAccountName = "StorageAccountName"
# $ContainerName = "StorageContainerName"
# $LocalPath = "C:\LocalPath\"
# Create a credential
$ServerAdmin = "serverlogin"
$Password = ConvertTo-SecureString –String 'password' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential `
–ArgumentList $ServerAdmin, $Password
# Generate a unique filename for the BACPAC
$bacpacFilename = "$DatabaseName" + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Blob storage information
$StorageKey = "YOUR STORAGE KEY"
$BaseStorageUri = "https://STORAGE-NAME.blob.core.windows.net/BLOB-CONTAINER-NAME/"
$BacPacUri = $BaseStorageUri + $bacpacFilename
New-AzureRmSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -CopyResourceGroupName $ResourceGroupName -CopyServerName $ServerName -CopyDatabaseName $CopyDatabaseName
Write-Output "Azure SQL DB $CopyDatabaseName Copy completed"
# Create a request
$Request = New-AzureRmSqlDatabaseExport –ResourceGroupName $ResourceGroupName –ServerName $ServerName `
–DatabaseName $DatabaseName –StorageKeytype StorageAccessKey –StorageKey $StorageKey `
-StorageUri $BacPacUri –AdministratorLogin $Credential.UserName `
–AdministratorLoginPassword $Credential.Password
# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
Start-Sleep -s 10
}
$exportStatus
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Azure SQL DB Export $Status for "$DatabaseName""
}
else
{
Write-Output "Azure SQL DB Export Failed for "$DatabaseName""
}
# Download file from azure
Write-Output "Downloading"
$StorageContext = Get-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName
$StorageContext | Get-AzureStorageBlob -Container $ContainerName -blob $bacpacFilename | Get-AzureStorageBlobContent -Destination $LocalPath
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Blob $bacpacFilename Download $Status for "$DatabaseName" To $LocalPath"
}
else
{
Write-Output "Blob $bacpacFilename Download Failed for "$DatabaseName""
}
# Drop Copy Database after successful export
Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $CopyDatabaseName `
-Force
Write-Output "Azure SQL DB $CopyDatabaseName Deleted"[/code]
The above script can be saved and triggered manually, to automate the process and setup a schedule task, we can gain benefits from Windows Task Scheduler to run the PowerShell script on schedule of your preference. To do so, please follow steps below:
- Now that we need to automate this PowerShell script and run it using Windows Task Scheduler.
- Copy / past the below PowerShell Script to a new notepad and save it as CopyFilesFromAzureStorageContainerV2.ps1.
-
Values highlighted in Green need to be updated, to automate run this PowerShell script which is explained in the below steps:
- a. Create a new schedule task
- In action tap navigate to powershell.exe localpath C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
- In add argument (optional) copy/past the full local path to your PowerShell script C:\bacpac\CopyFilesFromAzureStorageContainerV2.ps1
- Add trigger (schedule) according to your need and then save it
[code language="PowerShell"]<#
.SYNOPSIS
Export Azure SQL Database to Blob storage and download the exported *.bacpac file from blob to local path
.DESCRIPTION
This PowerShell Script to export Azure SQL DB to a blob storage and then copies blobs from a single storage container to a local directoy.
The script supports the -Whatif switch so you can quickly see how complex the copy
operation would be.
.EXAMPLE
.\CopyFilesFromAzureStorageContainer -LocalPath "c:\users\myUserName\documents" `
-ServerName "myservername" -DatabaseName "myDBname" -ResourceGroupName "myresourcegroupname" -StorageAccountName "mystorageaccount" -ContainerName "myuserdocuments" -Force
#>;
# Login to Azure subscription
$path = ‘C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path
$DatabaseName = " hidden"
$CopyDatabaseName = $DatabaseName + "_Copy"
$ServerName = "hidden"
$ResourceGroupName = "hidden"
$StorageAccountName = "hidden"
$ContainerName = "bacpac"
$LocalPath = "C:\localpath\"
# Create a credential
$ServerAdmin = "serverlogin"
$Password = ConvertTo-SecureString –String 'password' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential `
–ArgumentList $ServerAdmin, $Password
# Generate a unique filename for the BACPAC
$bacpacFilename = "$DatabaseName" + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Blob storage information
$StorageKey = "YOUR STORAGE KEY"
$BaseStorageUri = "https://StorageAccountName.blob.core.windows.net/ContainerName/"
$BacPacUri = $BaseStorageUri + $bacpacFilename
New-AzureRmSqlDatabaseCopy -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -CopyResourceGroupName $ResourceGroupName -CopyServerName $ServerName -CopyDatabaseName $CopyDatabaseName
Write-Output "Azure SQL DB $CopyDatabaseName Copy completed"
# Create a request
$Request = New-AzureRmSqlDatabaseExport –ResourceGroupName $ResourceGroupName –ServerName $ServerName `
–DatabaseName $CopyDatabaseName –StorageKeytype StorageAccessKey –StorageKey $StorageKey `
-StorageUri $BacPacUri –AdministratorLogin $Credential.UserName `
–AdministratorLoginPassword $Credential.Password
# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $Request.OperationStatusLink
Start-Sleep -s 10
}
$exportStatus
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Azure SQL DB Export $Status for "$DatabaseName""
}
else
{
Write-Output "Azure SQL DB Export Failed for "$DatabaseName""
}
# Download file from azure
Write-Output "Downloading"
$StorageContext = Get-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName
$StorageContext | Get-AzureStorageBlob -Container $ContainerName -blob $bacpacFilename | Get-AzureStorageBlobContent -Destination $LocalPath
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Blob $bacpacFilename Download $Status for "$DatabaseName" To $LocalPath"
}
else
{
Write-Output "Blob $bacpacFilename Download Failed for "$DatabaseName""
}
# Drop Copy Database after successful export
Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $CopyDatabaseName `
-Force
Write-Output "Azure SQL DB $CopyDatabaseName Deleted"[/code]
Hope that you enjoyed that article, we appreciate your comments and feedback on this !!