19 Apr

Backup Azure SQL Databases

Microsoft SQL Azure
 
Since Azure SQL does not support BACKUP DATABASE. You will have to find an other way to take a backup of your SQL databases.
To do this we’ll be using the Azure PowerShell and backup our databases in bacpac-files.

 
 

You won’t be needing much data for this one:

  • The name of your subscription: [SUBSCRIPTIONNAME]
  • The route to your publishsettings file (eg.: c:\qwerty.publishsettings): [PUBLISHSETTINGS]
  • Your login name: [LOGIN]
  • Your password: [PASSWORD]
  • The name of the SQL-server: [SERVERNAME]
  • The address of the server: [SERVERADDRESS]
  • Path to save the bacpac-file: [PATH]


$subscription = "[SUBSCRIPTIONNAME]"
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\*.psd1"
Import-AzurePublishSettingsFile "[PUBLISHSETTINGS]"
Select-AzureSubscription -SubscriptionName $subscription
$servercredential = new-object System.Management.Automation.PSCredential("[LOGIN]", ("[PASSWORD]"  | ConvertTo-SecureString -asPlainText -Force))
$server = Get-AzureSqlDatabaseServer -servername "[SERVERNAME]"
$ctx = $server | New-AzureSqlDatabaseServerContext -Credential $serverCredential
$list = Get-AzureSqlDatabase $ctx
foreach($i in $list) { $tmpvar = $i.Name; & 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe' /a:Export /ssn:[SERVERADDRESS] /sdn:"$tmpvar" /su:[LOGIN] /sp:[PASSWORD] /tf:"[PATH]\$tmpvar.bacpac" }

Once you have this script ready. Just run it in Azure PowerShell and you will notice that all the databases are being imported.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.