Powershell: Backup-Sqldatabase fails to resolve path

Question

EDIT:

I figured out that I needed to specify the ServerInstance as well:

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

ORIGINAL POST:

I'm trying to backup a database with the Backup-Sqldatabase cmdlet. This is the script i'm trying to run:

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"

This results in this error:

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

The path is where the database files are located. What am I doing wrong?

I really hope someone can help/clarify things :)

Answer 1

try some thing like this:

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

I wrote and executed the above in my test environment and the backup completed without issue.

Syntax to run and output would look similar to the below:

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).

Since you are trying to save to default sql folder in program files make sure you are running the script as an account that has access to write to the location. For example an administrator account or if you have a service account for backups either of those would works. Administrator account should have access by default to the program files folder assuming permissions were not changed.

Hope this helps.