C# SMO backup of remote database to local machine

Andy March picture Andy March · Feb 17, 2010 · Viewed 10.3k times · Source

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

Answer

marc_s picture marc_s · Feb 17, 2010

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