12

I have an application which performs backups and restores of SQL databases, this works fine on the local machine, however if I run this against a SQL server hosted on another machine I get the following error

Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server '25.98.30.79'.
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch.
System.Data.SqlClient.SqlException: Cannot open backup device 'C:\Program Files\State Manager\Archive\Capture\20100217152147\*product*\databases\*database*\*database*.bak'. Operating system error 3(The system cannot find the path specified.).

This appears to be being caused by the SQL server attempting to write this file to its local drive. I cannot setup a shared area into which the backup can be placed due to security restrictions.

Does anyone know how I can move this data back to the machine the code is being called from?

My code is below.

private string Name;
private string Server;
private string dbName;
private string user;
private string password;

public Boolean performCapture(String archiveDir)
{
    String destination = archiveDir + "\\" + Name;
    if (!System.IO.Directory.Exists(destination))
    {
        System.IO.Directory.CreateDirectory(destination);
    }

    Server sqlServer = connect();
    if (sqlServer != null)
    {
        DatabaseCollection dbc = sqlServer.Databases;
        if (dbc.Contains(dbName))
        {
            Backup bkpDatabase = new Backup();
            bkpDatabase.Action = BackupActionType.Database;
            bkpDatabase.Database = dbName;
            BackupDeviceItem bkpDevice = new BackupDeviceItem(destination + "\\" + dbName + ".bak", DeviceType.File);

            bkpDatabase.Devices.Add(bkpDevice);
            bkpDatabase.Incremental = false;
            bkpDatabase.Initialize = true;
            // Perform the backup
            bkpDatabase.SqlBackup(sqlServer);

            if (System.IO.File.Exists(destination + "\\" + dbName + ".bak"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
    }
    else
    {
        return false;
    }
}
C. Tewalt
  • 2,271
  • 2
  • 30
  • 49
Andy March
  • 586
  • 1
  • 5
  • 20

4 Answers4

19

No, this won't ever work - SQL Server can only ever back up to a drive physically attached to the actual SQL Server machine. You cannot under any circumstances back up a remote SQL Server to your local harddisk - just not possible (neither in SMO, or in SQL Server Management Studio).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • You're right. It Can't be made. I spend a few hours searching a way some months ago. – Jonathan Feb 17 '10 at 16:51
  • 1
    Thanks to you both, looks like my way round is as suggested by Jonathan below. I've written a little socket service to send back files on request. – Andy March Feb 18 '10 at 11:11
  • 1
    +1. Had a hard time to find this out. You may add "*except* you create a network folder on your machine and tell Sql Server to create the backup file there." – Stefan Steinegger Aug 18 '11 at 08:36
  • I realize the question is tagged as SMO but maybe this is helpful for others to know of: @Jonathan what about `Microsoft.SqlServer.Dac.DacServices`? With it you can create a local .bacpac file https://csharp.hotexamples.com/examples/-/DacServices/ExportBacpac/php-dacservices-exportbacpac-method-examples.html – Don Cheadle Aug 03 '18 at 03:47
1

You can copy remote database to local. To copy use this: Right click on remote database in SSMS: Tasks -> Export data. In opened window choose source and destination database, it will be copy all data from source (remote) to your local database.

Dmitriy
  • 552
  • 1
  • 6
  • 20
1

As Marc_s said, It can't be made. As a workarround you make that your database call a command line program in the host, that send the file by ftp, copy it to some shared folder, or something else.

Good Luck

Jonathan
  • 11,809
  • 5
  • 57
  • 91
  • true - but even here, you must first store the backup on the server machine somewhere...... – marc_s Feb 17 '10 at 16:55
0

I know it is an old post but Yes you can backup a database of a remote sqlserver

you just create the same folder name and drive in both the remote and local computer

example:

d:\sql_backup on local computer d:\sql_backup on remote computer

and it will do it... of course the backup file will be on the remote computer

if you want to get the backup files, just share the folder of the remote computer

saludos rubenc

ruben
  • 109
  • 2
  • 10
  • This question is specifically about backing up the DB to the "local machine", as in the machine that the code is running in. So `Yes` followed by `of course the backup file will be on the remote computer` is a little contradictory. – Don Cheadle Aug 03 '18 at 16:02