Powershell Script for creating a local *.bak backup of a SQL Server database on a server via a remote Powershell connection

Question

My script is executed on my local Windows 10 machine in a PowerShell console.

This is my code:

# Import the SQL Server Module.    
Import-Module Sqlps -DisableNameChecking;

# To check whether the module is installed.
Get-Module -ListAvailable -Name Sqlps;

$directory = "C:\Users\max.mueller\Documents\Backups\"

$SQLServer = "MMSQLServer\Instance"
$SQLDBName = "DBNAME"
$uid ='admin'
$pwd = '123456'

# Connect to database and generate a local output file - works
$SqlQuery = "SELECT * from customers;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = False; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | out-file "C:\Users\max.mueller\Documents\Backups\test.txt"

#do a backup: does not work
$secpasswd = ConvertTo-SecureString $pwd -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($uid, $secpasswd)

Backup-SqlDatabase -ServerInstance $SQLServer -Database $SQLDBName -BackupFile "C:\Users\max.mueller\Documents\Backups\MyRemoteBackUp.bak" # -SqlCredential $mycreds

The interesting thing is that I can perform a SQL select query and export the results. However, when I try to execute a backup with backup-sqlDatabase, then I get the following error message:

Backup-SqlDatabase : Fehler beim Herstellen einer Verbindung mit dem Server " Server\Instance".
In Zeile:39 Zeichen:1
+ Backup-SqlDatabase -ServerInstance $SQLServer -Database $SQLDBName -B ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Backup-SqlDatabase], ConnectionFailureException
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

It is a German SQL Server. The translation is error while connecting to the server " ..."

Any ideas what the problem could be?

Thanks in advance

Answer 1

you run the backup in a remote machine, so you should pass user/passwod

use Credential not SqlCredential and enter user and password.

The command is:

Backup-SqlDatabase -ServerInstance $SQLServer -Database $SQLDBName -BackupFile $fname -Credential $mycreds

The filename $fname is in the server (remote machine), not in the local machine

The SQL Server service account should have a permission R/W on the folder of backup otherwise error message is fired

Q- So I am not sure, how is it possible to store the backup locally on my machine / not on the remote SQL Server?

A- You can't store the backup locally in your machine.

but you can copy the remote backup to a local folder ( if you have a permission)