英文:
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 ""
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论