远程数据库中本地机器的C＃SMO备份 [英] C# SMO backup of remote database to local machine

问题描述

我有执行备份和SQL数据库还原的应用程序，这工作正常在本地机器上，但是如果我跑这对托管于其他机器，我得到了以下错误

上的SQL服务器

Microsoft.SqlServer.Management.Smo.FailedOperationException：备份失败，服务器'25 .98.30.79'。 ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException：执行Transact-SQL语句或批处理时出现异常。 ---> System.Data.SqlClient.SqlException：无法打开备份设备'C：\Program Files\State Manager\Archive\Capture\20100217152147 *产品* \databases * **数据库数据库名为* .bak 。操作系统错误3（系统不能找到指定的路径）。

这似乎是由SQL服务器试图引起将此文件写入到本地驱动器。我不能设置一个共享区域到其中的备份可以放置由于安全限制。

有谁知道我可以搬回这个数据到机器的代码是被从叫什么名字？

我的代码如下。

 私人字符串名称; 
私人字符串服务器; 
私人字符串DBNAME; 
私人字符串用户; 
私人字符串密码; 
 
公共布尔performCapture（字符串archiveDir）
 {
字符串目的地= archiveDir +\\+名称; 
如果
 {
 System.IO.Directory.CreateDirectory（目标）（System.IO.Directory.Exists（目标）！）; 
} 
 
服务器SQLSERVER =连接（）; 
如果（SQLSERVER！= NULL）
 {
 DatabaseCollection DBC = sqlServer.Databases; 
如果（dbc.Contains（数据库））
 {
备份bkpDatabase =新的Backup（）; 
 bkpDatabase.Action = BackupActionType.Database; 
 bkpDatabase.Database = DBNAME; 
 BackupDeviceItem bkpDevice =新BackupDeviceItem（目标+\\+ DBNAME +名为.bak，DeviceType.File）; 
 
 bkpDatabase.Devices.Add（bkpDevice）; 
 bkpDatabase.Incremental = FALSE; 
 bkpDatabase.Initialize = TRUE; 
 //执行备份
 bkpDatabase.SqlBackup（SQLSERVER）; 
 
如果（System.IO.File.Exists（目的地+\\+ DBNAME +名为.bak））
 {
返回真; 
} 
，否则
 {
返回FALSE; 
} 
} 
，否则
 {
返回FALSE; 
} 
} 
，否则
 {
返回FALSE; 
} 
} 
  

解决方案

没有，这不会永远工作 - SQL Server只能回到曾经到一个驱动器物理连接到实际的SQL Server计算机。您不能在任何情况下备份的远程SQL Server到本地硬盘 - 是不可能的（无论是在SMO，或SQL Server Management Studio中）。

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

解决方案

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

这篇关于远程数据库中本地机器的C＃SMO备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！