1

When I run the following script line locally on an SQL Server machine, it executes perfectly:

Backup-SqlDatabase -ServerInstance $serverName -Database $sqldbname -BackupFile "$($backupFolder)$($dbname)_db_$($addinionToName).bak"

$serverName contains a short name of the SQL Server instance. SQL Server is 2012, so these new cmdlets work like a charm.

On the other hand, when I am trying to perform a DB backup from a TeamCity agent machine like this (Through Invoke-Command cmdlet):

function BackupDB([String] $serverName, [String] $sqldbname, [String] $backupFolder, [String] $addinionToName)
{
    Import-Module SQLPS -DisableNameChecking

    Backup-SqlDatabase -ServerInstance $serverName -Database $sqldbname -BackupFile "$($backupFolder)$($dbname)_db_$($addinionToName).bak"
}

Invoke-Command -computername $SQLComputerName -Credential $credentials -ScriptBlock ${function:BackupDB} -ArgumentList $SQLInstanceName, $DatabaseName, $BackupDirectory, $BakId

results in an error:

Failed to connect to server $serverName. + CategoryInfo : NotSpecified: (:) [Backup-SqlDatabase], ConnectionFailureException + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.M anagement.PowerShell.BackupSqlDatabaseCommand

What is the correct way to execute Backup-SqlDatabase cmdlet remotely?

1

You can just install three modules from SQL Feature pack that provides the SQLPS functionality and use it without Invoke. Get and install in this order:

Microsoft® System CLR Types for Microsoft® SQL Server® 2012 Microsoft®
SQL Server® 2012 Shared Management Objects Microsoft® Windows
PowerShell Extensions for Microsoft® SQL Server® 2012

from here. After this you can run them localy and access remote SQL servers back to SQL 2000. Read here for some help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.