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

try -Credential (Get-Credential "admin") manually enter the password for now, just for testing purposes — Jimbo
– Jimbo, Commented Sep 20, 2016 at 12:12
Is there a reason that you have commented out the -sqlcredential flag on the last command? — BenH
– BenH, Commented Sep 20, 2016 at 13:21
I commented the SQL crendtial flag out, because I tried different parameter Settings without any meaning. I dont use my current Windows / Domain User to Access the database. — angry-sp-engineer
– angry-sp-engineer, Commented Sep 20, 2016 at 14:58
When I use -credential (Get-credential "admin") and enter the Password into the prompt, I got the following error message: Backup-SqlDatabase : System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Users\max.mueller\Documents\Backups\MyRemoteBackUp.bak'. Operating system error 3(The system cannot find the path specified.). In C:\Users\max.mueller\Documents\Used_PowershellScripts\ConnectMSSQLQueryBackUpps1.ps1:41 Zeichen:1 So I am not sure, how is it possible to store the backup locally on my machine / not on the remote SQL Server? — angry-sp-engineer
– angry-sp-engineer, Commented Sep 20, 2016 at 15:02
Do you have any recommendations what are the next steps or how to tackle the issue? — angry-sp-engineer
– angry-sp-engineer, Commented Sep 21, 2016 at 8:49

M.Hassan · Accepted Answer · 2016-10-10 17:37:57Z

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)

answered Oct 10, 2016 at 17:37

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related