0

I want to backup my remote SQL Server database onto the local computer.

So far I've tried:

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();
    }
}

But I get this error :

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.

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2088807
  • 1,378
  • 2
  • 25
  • 47
  • Using that method, the file will be written to the SQL servers local file system.. so, thats why it doesnt like the path. – BugFinder Mar 22 '17 at 08:11
  • maybe you should just copy the db on pendrive from server and paste on your computer – Rico Mar 22 '17 at 08:12
  • If you want to create the backup from the **remote** SQL Server to your local machine's filesystem, you must create a **share** on your local computer pointing to a directory on your local file system, and then use the UNC notation in your path `\\YourPC\YourBackupShare\.......` on the server - provided the server *can* connect to your local system, and the user that remote SQL Server instance is running under has the permissions to write your your own, local file system..... – marc_s Mar 22 '17 at 08:33

1 Answers1

0

You cannot create a directly backup from a remote server to a local disk.

Please look this link.

Community
  • 1
  • 1