Backup and Restore MS SQL SERVER DB to MS-Azure using Powershell

Backup-SQLAzureDB.ps1

This is a very simple script allowing you to specify the connection string of your SQL Azure server, the database you wish to export and the output filename for the bacpac file. Note that this assumes that you have installed SQL Server Management Studio 2012 under: C:\Program Files (x86)\Microsoft SQL Server (if this isn't the case you can simply specify the optional -SqlInstallationFolder parameter with the correct path).

Syntax:

.\Backup-SQLAzureDB.ps1 -ConnectionString "Server=tcp:….database.windows.net,1433;Database=MyDb;User ID=something@server;Password=pwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" -DatabaseName "MyDB" -OutputFile "C:\backup.bacpac"

param([string]$ConnectionString = $(throw "The ConnectionString parameter is required."),  
      [string]$DatabaseName = $(throw "The DatabaseName parameter is required."),
      [string]$OutputFile = $(throw "The OutputFile parameter is required."), 
      [string]$SqlInstallationFolder = "C:\Program Files (x86)\Microsoft SQL Server")

# Load DAC assembly.
$DacAssembly = "$SqlInstallationFolder\110\DAC\bin\Microsoft.SqlServer.Dac.dll"
Write-Host "Loading Dac Assembly: $DacAssembly"  
Add-Type -Path $DacAssembly  
Write-Host "Dac Assembly loaded."

# Initialize Dac service.
$now = $(Get-Date).ToString("HH:mm:ss")
$Services = new-object Microsoft.SqlServer.Dac.DacServices $ConnectionString
if ($Services -eq $null)  
{
    exit
}

# Start the actual export.
Write-Host "Starting backup at $DatabaseName at $now"  
$Watch = New-Object System.Diagnostics.StopWatch
$Watch.Start()
$Services.ExportBacpac($OutputFile, $DatabaseName)
$Watch.Stop()
Write-Host "Backup completed in" $Watch.Elapsed.ToString()  

At the end of the backup the script will show how long it took:

Restore-SQLAzureDB.ps1

This script is very similar to the previous script. But instead of calling the BackupBakpac method this script calls theImportBakpac method allowing you to use a local *.bacpac file to create a new database.

param([string]$ConnectionString = $(throw "The ConnectionString parameter is required."),  
      [string]$DatabaseName = $(throw "The DatabaseName parameter is required."),
      [string]$InputFile = $(throw "The InputFile parameter is required."), 
      [string]$SqlInstallationFolder = "C:\Program Files (x86)\Microsoft SQL Server")

# Load DAC assembly.
$DacAssembly = "$SqlInstallationFolder\110\DAC\bin\Microsoft.SqlServer.Dac.dll"
Write-Host "Loading Dac Assembly: $DacAssembly"  
Add-Type -Path $DacAssembly  
Write-Host "Dac Assembly loaded."

# Initialize Dac service.
$now = $(Get-Date).ToString("HH:mm:ss")
$Services = new-object Microsoft.SqlServer.Dac.DacServices $ConnectionString
if ($Services -eq $null)  
{
    exit
}

# Start the actual export.
Write-Host "Starting restore to $DatabaseName at $now"  
$Watch = New-Object System.Diagnostics.StopWatch
$Watch.Start()
$Package =  [Microsoft.SqlServer.Dac.BacPackage]::Load($InputFile)
$Services.ImportBacpac($Package, $DatabaseName)
$Package.Dispose()
$Watch.Stop()
Write-Host "Restore completed in" $Watch.Elapsed.ToString()