远程数据库的C# Server备份到远程默认备份位置，而不直接访问远程位置？

Question

TL;DR -我希望服务器进行备份，而不是我的应用程序，因为服务器被设置为备份，而我的应用程序将无法访问。

背景

20年前，我的公司用Delphi 7/Pascal语言为客户开发了软件。我正在用C#重写软件.作为重写的一部分，我创建了新的Firebird、Oracle和Server数据库。联邦法规要求维护所有现有数据，因此我创建了一个数据库修改/转换工具，以便将旧的数据库结构转换为新的数据库结构。

在开始进行更改之前，我需要备份现有数据库。将运行此工具的技术人员无法访问其本地文件结构，也无法手动访问数据库所在的远程服务器。该工具在本地系统上访问一个加密的.ini-like文件，以解析连接字符串的组件并创建一个连接对象。然后，我使用该连接对象连接到技术人员计算机要连接到的同一个数据库。--这部分全部工作，

如果保留默认备份路径，它尝试备份到默认路径，但在本地机器上(技术人员没有创建该路径的权限，我们也不希望技术人员访问.bak )，如果我修改默认备份路径为从连接字符串获取的网络路径，则会得到

SmoException: System.Data.SqlClient.SqlError:无法打开备份设备操作系统错误67(找不到网络名称)。

因为文件路径不是网络共享(也不会是)，而且数据库用户凭据无法从Server外部访问该路径。

所以问题是:如何将备份带到远程默认路径，就像我在服务器上一样？

下面是生成上述错误的代码(远程为null )。

public static void FullSqlBackup (Connection oldProactiveSql)
{
           String sqlServerLogin = oldProactiveSql.UserName;
           String password = oldProactiveSql.PassWord;
           String instanceName = oldProactiveSql.InstanceName;
           String remoteSvrName = oldProactiveSql.Ip + "," + oldProactiveSql.Port;

           Server srv2;
           Server srv3;
           string device;

           switch (oldProactiveSql.InstanceName)
           {
                case null:
                     ServerConnection srvConn2 = new ServerConnection(remoteSvrName);
                     srvConn2.LoginSecure = false;
                     srvConn2.Login = sqlServerLogin;
                     srvConn2.Password = password;
                     srv3 = new Server(srvConn2);
                     srv2 = null;
                     Console.WriteLine(srv3.Information.Version);

                     if (srv3.Settings.DefaultFile is null)
                     {
                          device = srv3.Information.RootDirectory + "\\DATA\\";
                          device = device.Substring(2);
                          device = oldProactiveSql.Ip + device;
                     }
                     else device = srv3.Settings.DefaultFile;
                     device = device.Substring(2);
                     device = string.Concat("\\\\", oldProactiveSql.Ip, device);
                     break;

                default:
                     ServerConnection srvConn = new ServerConnection();
                     srvConn.ServerInstance = @".\" + instanceName;
                     srvConn.LoginSecure = false;
                     srvConn.Login = sqlServerLogin;
                     srvConn.Password = password;
                     srv2 = new Server(srvConn);
                     srv3 = null;
                     Console.WriteLine(srv2.Information.Version);

                     if (srv2.Settings.DefaultFile is null)
                     {
                          device = srv2.Information.RootDirectory + "\\DATA\\";
                     }
                     else device = srv2.Settings.DefaultFile;
                     break;
           }

           Backup bkpDbFull = new Backup();
           bkpDbFull.Action = BackupActionType.Database;
           bkpDbFull.Database = oldProactiveSql.DbName;
           bkpDbFull.Devices.AddDevice(device, DeviceType.File);
           bkpDbFull.BackupSetName = oldProactiveSql.DbName + " database Backup";
           bkpDbFull.BackupSetDescription = oldProactiveSql.DbName + " database - Full Backup";
           bkpDbFull.Initialize = true;
           bkpDbFull.PercentComplete += CompletionStatusInPercent;
           bkpDbFull.Complete += Backup_Completed;

           switch (oldProactiveSql.InstanceName)
           {
                case null:
                     try 
                     {
                         bkpDbFull.SqlBackup(srv3); 
                     }
                     catch (Exception e)
                     {
                          Console.WriteLine (e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;

                default:
                     try 
                     { 
                         bkpDbFull.SqlBackup(srv2); 
                     }
                     catch (Exception e)
                     {
                          Console.WriteLine(e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;
           }
      }

任何帮助都将不胜感激，因为我只是四处转圈。

从下面的注释中，我将尝试- 1.在数据库上动态创建存储过程BackupToDefault，然后运行它。2.如果失败，则将数据库链接到其本身。3.尝试- Exec BackupToDefault At LinkedSelfSynonmym

祝我好运，虽然它似乎错综复杂，并在漫长的道路上，我希望它的工作。

Answer 1

谢谢你@SeanLange

非常确定在这种情况下需要使用动态sql。然后，路径将相对于执行sql的位置。

我更改了代码以添加：

 private static void WriteBackupSp (Server remoteServer, string dbName, out string storedProcedure)
      {
           var s = "CREATE PROCEDURE [dbo].[ProactiveDBBackup]\n";
           s += "AS\n";
           s += "BEGIN\n";
           s += "SET NOCOUNT ON\n";
           s += "BACKUP DATABASE " + dbName + " TO DISK = \'" + string.Concat (remoteServer.BackupDirectory,@"\", dbName, ".bak") + "\'\n";
           s += "END\n";
           storedProcedure = s;
      }

然后将最后一个开关更改为：

switch (oldProactiveSql.InstanceName)
           {
                case null:
                     try
                     {
                          WriteBackupSp (srv3, oldProactiveSql.DbName, out var storedProcedure);
                          ConnectionToolsUtility.GenerateSqlConnectionString (oldProactiveSql, out var cs);

                          using (SqlConnection connection = new SqlConnection (cs))
                          {
                               using (SqlCommand command = new SqlCommand (storedProcedure, connection))
                               {
                                    connection.Open ();
                                    command.ExecuteNonQuery ();
                                    connection.Close ();
                               }
                          }
                          var execBackup = "EXEC [dbo].[ProactiveDBBackup]\n";
                          using (SqlConnection connection = new SqlConnection (cs))
                          {
                               using (SqlCommand command = new SqlCommand (execBackup, connection))
                               {
                                    connection.Open ();
                                    command.ExecuteNonQuery ();
                                    connection.Close ();
                               }
                          }
                     }
                     catch (Exception e)
                     {
                          Console.WriteLine(e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;
                default:
                     try { bkpDbFull.SqlBackup(srv2); }
                     catch (Exception e)
                     {
                          Console.WriteLine(e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;
           }

这允许我通过连接字符串对数据库进行备份，而不需要访问网络路径位置的凭据。

Answer 2

为了灵感..。备份分为3个文件，每个文件位于不同的目录中( sql实例备份dir &sql实例默认dir &数据库主dir)。

//// compile with:   
// /r:Microsoft.SqlServer.Smo.dll  
// /r:Microsoft.SqlServer.SmoExtended.dll 
// /r:Microsoft.SqlServer.ConnectionInfo.dll  

using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SMObackup
{
    class Program
    {
        static void Main()
        {

            // For remote connection, remote server name / ServerInstance needs to be specified  
            ServerConnection srvConn2 = new ServerConnection("machinename"/* <--default sql instance on machinename*/);  // or (@"machinename\sqlinstance") for named instances
            srvConn2.LoginSecure = false;
            srvConn2.Login = "smologin";
            srvConn2.Password = "SmoL@gin11";
            srvConn2.DatabaseName = "msdb";
            Server srv3 = new Server(srvConn2);

            //server info
            Console.WriteLine("servername:{0} ---- version:{1}", srv3.Name, srv3.Information.Version);

            //server root directory
            string serverRootDir = srv3.Information.RootDirectory;
            //server backup directory
            string serverBackupDir = srv3.Settings.BackupDirectory;
            //database primary directory
            string databasePrimaryFilepath = srv3.Databases[srvConn2.DatabaseName].PrimaryFilePath;

            Console.WriteLine("server_root_dir:{0}\nserver_backup_dir:{1}\ndatabase_primary_dir{2}", serverRootDir, serverBackupDir, databasePrimaryFilepath);

            Backup bkpDbFull = new Backup();
            bkpDbFull.Action = BackupActionType.Database;
            //comment out copyonly ....
            bkpDbFull.CopyOnly = true; //copy only, just for testing....avoid messing up with existing backup processes
            bkpDbFull.Database = srvConn2.DatabaseName;

            //backup file name
            string backupfile = $"\\backuptest_{DateTime.Now.ToString("dd/MM/yyyy/hh/mm/ss")}.bak";

            //add multiple files, in each location
            bkpDbFull.Devices.AddDevice(serverRootDir + backupfile, DeviceType.File);
            bkpDbFull.Devices.AddDevice(serverBackupDir + backupfile, DeviceType.File);
            bkpDbFull.Devices.AddDevice(databasePrimaryFilepath + backupfile, DeviceType.File);
            bkpDbFull.Initialize = true;

            foreach (BackupDeviceItem backupdevice in bkpDbFull.Devices)
            {
                Console.WriteLine("deviceitem:{0}", backupdevice.Name);
            }

            //backup is split/divided amongst the 3 devices
            bkpDbFull.SqlBackup(srv3);

            Restore restore = new Restore();
            restore.Devices.AddRange(bkpDbFull.Devices);
            DataTable backupHeader = restore.ReadBackupHeader(srv3);


            //IsCopyOnly=True
            for (int r = 0; r < backupHeader.Rows.Count; r++)
            {
                for (int c = 0; c < backupHeader.Columns.Count; c++)
                {
                    Console.Write("{0}={1}\n", backupHeader.Columns[c].ColumnName, (string.IsNullOrEmpty(backupHeader.Rows[r].ItemArray[c].ToString())? "**": backupHeader.Rows[r].ItemArray[c].ToString()) );
                }
            }

            srvConn2.Disconnect(); //redundant

        }
    }
}