C# SMO backup of remote database to local machine

Question

Hi all,

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\SitaWare\databases\JC3IEDM\JC3IEDM.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;
        }
    }

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

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

Answer 3

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