SQL Azure backup on-prem using Powershell

Question

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'?

score 0 · Accepted Answer · answered Feb 06 '23 at 04:19

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 ""

SQL Azure backup on-prem using Powershell

1 Answers1