azurepowershellbacpac

SQL Azure backup on-prem using Powershell


I have this Powershell script I am trying to run, to do me a 'bacpac' file from an Azure tenancy database, to on-prem (local folder).

# Load SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

# Define the source database
$sourceServer = "myserver-sql-server"
$sourceDB = "mydb-sql-db"

# Define the target file
$targetFile = "c:\temp\mydb.bacpac"

# Connect to the source database
$sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server $sourceServer
$sourceDB = $sourceServer.Databases[$sourceDB]

# Export the database to the target file
$sourceDB.ExportBacpac($targetFile)

The error I am getting is on the last line...

You cannot call a method on a null-valued expression. At line:2 char:1

  • $sourceDB.ExportBacpac($targetFile)
  •   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
    

The variables have values. Am I missing a parameter calling 'ExportBacPac'?


Solution

  • This script worked...

    Function Get-Bacpacs {
        Param(
        [string]$location
        , [string]$server
        , [string]$smolibrary
        , [string]$daclibrary
        , [string]$username
        , [string]$password
        )
        Process
        {
            $dt = Get-Date -uFormat "%Y%m%d"
            
            Add-Type -Path $smolibrary
            $scon = "Data Source=$server.database.windows.net;Initial Catalog=master;User ID=$username;Password=$password;"
            $servercon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
            $servercon.ConnectionString = $scon
            $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($servercon)
            
            foreach ($db in $srv.Databases | Where-Object {$_.Name -ne "master"})
            {
                $database = $db.Name
                $bak_scon = "Data Source=$server.database.windows.net;Initial Catalog=$database;Connection Timeout=0;User ID=$username;Password=$password;"
                
                if (!(Test-Path $location))
                {
                    New-Item $location -ItemType Directory
                }
                
                $outfile = $location + $database + "_" + $dt + ".bacpac"
                Add-Type -Path $daclibrary
                $d_exbac = New-Object Microsoft.SqlServer.Dac.DacServices $bak_scon
                
                try
                {
                    $d_exbac.ExportBacpac($outfile, $database)
                }
                catch
                {
                    Write-Warning $_
                    ###  Other alerting can go here
                }
            }
        }
    }
    
    ###  This makes it easier for us who don't have really long screens!  Location may vary.
    $smo = "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
    $dac = "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"
    
    Get-Bacpacs -location "" -server "" -smolibrary $smo -daclibrary $dac -username "" -password ""