C# SMO backup of remote database to local machine

Question

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

Dante

Answer 1

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).

Swanhildas

You're right. It Can't be made. I spend a few hours searching a way some months ago. – Kin

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. – Dante

+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." – Bufflehead

I realize the question is tagged as SMO but maybe this is helpful for others to know of: @Kin what about Microsoft.SqlServer.Dac.DacServices? With it you can create a local .bacpac file csharp.hotexamples.com/examples/-/DacServices/ExportBacpac/… – Soane

Answer 2

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

Kin

true - but even here, you must first store the backup on the server machine somewhere...... – Swanhildas

Answer 3

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.

Perturbation

Answer 4

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

Fingered

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. – Soane