SQL Azure backup on-prem using Powershell 使用Powershell在本地备份SQL Azure

huangapple go评论57阅读模式
英文:

SQL Azure backup on-prem using Powershell

问题

我有这个PowerShell脚本,我尝试运行它,从Azure租户数据库生成一个“bacpac”文件,然后将其复制到本地文件夹。

# 加载 SMO 组件
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

# 定义源数据库
$sourceServer = "myserver-sql-server"
$sourceDB = "mydb-sql-db"

# 定义目标文件
$targetFile = "c:\temp\mydb.bacpac"

# 连接到源数据库
$sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server $sourceServer
$sourceDB = $sourceServer.Databases[$sourceDB]

# 将数据库导出到目标文件
$sourceDB.ExportBacpac($targetFile)

我遇到的错误是在最后一行...

在第2行的字符:1位置不能对空值表达式调用方法。

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

变量已经有值。我是否漏掉了调用'ExportBacpac'的参数?

英文:

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

答案1

得分: 0

这段脚本工作...

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 Server0\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$dac = "C:\Program Files (x86)\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"

Get-Bacpacs -location "" -server "" -smolibrary $smo -daclibrary $dac -username "" -password ""

这部分代码没有被翻译,如你所要求。

英文:

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 Server0\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$dac = "C:\Program Files (x86)\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"

Get-Bacpacs -location "" -server "" -smolibrary $smo -daclibrary $dac -username "" -password ""

huangapple
  • 本文由 发表于 2023年2月6日 10:00:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356765.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定