database - Powershell:Backup-Sqldatabase 无法解析路径

Question

编辑:

我发现我还需要指定 ServerInstance:

Backup-SqlDatabase -ServerInstance [ServerName] -Database [DBName] -BackupFile "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Copy.bak"

原帖:

我正在尝试使用 Backup-Sqldatabase cmdlet 备份数据库。这是我要运行的脚本:

Backup-SqlDatabase -Database "LokalUdvikling" -CompressionOption "On" -CopyOnly -Path “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" -BackUpFile "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Copy.bak"

这会导致此错误:

Backup-SqlDatabase : Failed to resolve the path ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL’ to an object of type 'Microsoft.S
qlServer.Management.Smo.Server'. Either set your location to the proper context, or use the -Path parameter to specify the location. 
At line:1 char:1
+ Backup-SqlDatabase
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (C:\Program File...SQLSERVER\MSSQL:String) [Backup-SqlDatabase], SqlPowerShellContextException
    + FullyQualifiedErrorId : ContextError,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

路径是数据库文件所在的位置。我做错了什么？

我真的希望有人能帮助/澄清事情:)

Answer 1

尝试这样的事情:

Function Invoke-SQLBackup {

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $ServerInstance,

    [Parameter(Mandatory=$true)]
    [String] $DatabaseName,

    [Parameter(Mandatory=$true)]
    [String] $BackupFile

    )

if (Get-Module -ListAvailable -Name SQLPS)
    {
        Import-Module SQLPS -Force

            Backup-SqlDatabase -ServerInstance $ServerInstance -Database $DatabaseName -BackupFile $BackupFile -Verbose 
    }

ELSE

    {
        Write-Output "SQLPS Module not Installed. Please install and try again."
    }
}

我在我的测试环境中编写并执行了上面的内容，并且备份没有问题地完成了。

运行和输出的语法类似于以下内容:

PS SQLSERVER:\> Invoke-SQLBackup -ServerInstance 'SERVERNAME\INSTANCENAME' -DatabaseName 'TestDatabase' -BackupFile 'C:\SomePath\Filename.bak'
    WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
    VERBOSE: Performing the operation "Backup-SqlDatabase" on target "[SERVERNAME\INSTANCENAME]".
    VERBOSE: BACKUP DATABASE [KB] TO  DISK = N'C:\SomePath\FileName.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD,  STATS = 10
    VERBOSE: 10 percent processed.
    VERBOSE: 21 percent processed.
    VERBOSE: 32 percent processed.
    VERBOSE: 40 percent processed.
    VERBOSE: 51 percent processed.
    VERBOSE: 61 percent processed.
    VERBOSE: 72 percent processed.
    VERBOSE: 80 percent processed.
    VERBOSE: 91 percent processed.
    VERBOSE: Processed 296 pages for database 'DatabaseTest', file 'DatabaseTest' on file 4.
    VERBOSE: 100 percent processed.
    VERBOSE: Processed 2 pages for database 'DatabaseTest', file 'DatabaseTest_log' on file 4.
    VERBOSE: BACKUP DATABASE successfully processed 298 pages in 0.217 seconds (10.728 MB/sec).

由于您正在尝试保存到程序文件中的默认 sql 文件夹，请确保您以有权写入该位置的帐户身份运行脚本。例如，一个管理员帐户，或者如果您有一个用于备份的服务帐户，那么这两个都可以。假定权限未更改，管理员帐户默认情况下应有权访问程序文件文件夹。

希望这对您有所帮助。