Restore network database on SQL Server without copy it

328 views Asked by At

I trying to restore a SQL Server backup over the network.

I found a lot of way to copy file over network but backup files are big I don't want to copy them.

My problem is simple: this line

var fileList = restore.ReadFileList(smoServer);

executes on SQL Server.

This one define the backup I try to restore

var backupDeviceItem = new BackupDeviceItem(path, DeviceType.File);

The problem is the path looks like: 10.\*.\*.\*\\file.bak

So I get an exception that says

"10.*.*.*" path not found on server

That is logical - I need to provide credential for the machine 10.\*.\*.\*

If path parameter is C:\file.bak, my code works - if it's \\10.\*.\*.\*\\file.bak, my code says "file not found".

The BackupDeviceItem has a constructor with credentials but its SQL credential... or I'm already on my SQL Server... I need credentials for the machine that has the .bak file ...

Any ideas?

Just in case someone want my entire code here is my helper

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

namespace SqlManager.Tools
{
    public class SMOHelper
    {
        public static void RestoreDatabase(string path, string restore_name, SqlConfig sqlAccess)
        {
            ServerConnection SqlConnection;

            if (String.IsNullOrWhiteSpace(sqlAccess.username))
            {
                SqlConnection = new ServerConnection(sqlAccess.uri);
            }
            else
            {
                SqlConnection = new ServerConnection(sqlAccess.uri, sqlAccess.username, sqlAccess.password);
            }

            Server smoServer = new Server(SqlConnection);

            try
            {
                // On crée la BDD
                if (!smoServer.Databases.Contains(restore_name))
                {
                    Logger.Info("Creation de la base:");
                    Logger.Info(restore_name);
                    var database = new Database(smoServer, restore_name);
                    database.Create();
                }

                Logger.Success("Connexion SQL réussi");
                var targetDatabase = smoServer.Databases[restore_name];
                targetDatabase.RecoveryModel = RecoveryModel.Simple;
                targetDatabase.Alter();

                Restore restore = new Restore();

                var backupDeviceItem = new BackupDeviceItem(path, DeviceType.File);
                restore.Devices.Add(backupDeviceItem);
                restore.Database = restore_name;
                restore.ReplaceDatabase = true;
                restore.NoRecovery = false;
                restore.Action = RestoreActionType.Database;

                var fileList = restore.ReadFileList(smoServer);
                Logger.Success("Fichier de backup trouvé sur le serveur distant");

                var dataFile = new RelocateFile();
                dataFile.LogicalFileName = fileList.Rows[0][0].ToString();
                dataFile.PhysicalFileName = smoServer.Databases[restore_name].FileGroups[0].Files[0].FileName;

                var logFile = new RelocateFile();
                logFile.LogicalFileName = fileList.Rows[1][0].ToString();
                logFile.PhysicalFileName = smoServer.Databases[restore_name].LogFiles[0].FileName;

                restore.RelocateFiles.Add(dataFile);
                restore.RelocateFiles.Add(logFile);

                var backupHeaderInfo = restore.ReadBackupHeader(smoServer);

                smoServer.KillAllProcesses(restore_name);

                Logger.Info("Debut de la restauration sur le server");
                restore.SqlRestore(smoServer);
                Logger.Success("Restauration terminer");

                targetDatabase.SetOnline();
                SqlConnection.Disconnect();
            }
            catch (SmoException ex)
            {
                Logger.Error("SMO Message : " + ex.Message);
                Logger.Error("SMO Exception : " + ex.InnerException);
            }
            catch (IOException ex)
            {
                Logger.Error("IO Message : " + ex.Message);
                Logger.Error("IO Exception : " + ex.InnerException);
            }
            catch (Exception ex)
            {
                Logger.Error("Message : " + ex.Message);
                Logger.Error("Exception : " + ex.InnerException);
            }
        }
    }
}
1

There are 1 answers

2
DWRoelands On

You say

So i get an exception that say "10.*.*.*" path not found on server

You cannot use an asterisk in a folder name or a file name on a Windows server. See Naming Files, Paths, and Namespaces