title: Powershell Script for creating a local *.bak backup of a SQL Server database on a server via a remote Powershell connection
body: <p>My script is executed on my local Windows 10 machine in a PowerShell console. </p>
  
  <p>This is my code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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 <code>backup-sqlDatabase</code>, then I get the following error message:</p>
  
  <blockquote>
    <p>Backup-SqlDatabase : Fehler beim Herstellen einer Verbindung mit dem Server " Server\Instance".<br>
    In Zeile:39 Zeichen:1<br>
    + Backup-SqlDatabase -ServerInstance $SQLServer -Database $SQLDBName -B ...<br>
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
    + CategoryInfo          : NotSpecified: (:) [Backup-SqlDatabase], ConnectionFailureException<br>
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand  </p>
  </blockquote>
  
  <p>It is a German SQL Server. The translation is error while connecting to the server " ..."</p>
  
  <p>Any ideas what the problem could be?</p>
  
  <p>Thanks in advance</p>