title: Backup a remote SQL Server database
body: <p>I want to backup my remote SQL Server database onto the local computer.</p>
  
  <p>So far I've tried:</p>
  
  <pre><code>using (SqlConnection defaultSqlConnection = new SqlConnection(Constants.Database_Constants.GetConnectionStringFromProfile(Constants.Profiles.Staging)))
  {
      string pathDatabase = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\backup_production_database"+DateTime.UtcNow.ToString("d_MMM_yyyy_HH_mm_ss", CultureInfo.InvariantCulture)+".bak";
      WriteLine("The backup will be stored in " + pathDatabase);
  
      string backupDb = "BACKUP DATABASE DB_Staging TO DISK = '" + pathDatabase+ "' WITH INIT, COMPRESSION";
  
      File.Create(pathDatabase);
  
      using (SqlCommand backupCommand = new SqlCommand(backupDb, defaultSqlConnection))
      {
          defaultSqlConnection.Open();
          backupCommand.ExecuteNonQuery();
      }
  }
  </code></pre>
  
  <p>But I get this error : </p>
  
  <blockquote>
    <p>Cannot open backup device 'XXXX\bin\Debug\backup_production_database22_Mar_2017_08_04_42.bak'.
    Operating system error 3(The system cannot find the path specified.).
    BACKUP DATABASE is terminating abnormally.</p>
  </blockquote>
  
  <p>I know that I could generate a script using SQL Server Management Studio but it doesn't help me. I want to backup my database in a powershell script automatically without having to go manually into SQL Server Management Studio</p>