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);
}
}
}
}
You say
You cannot use an asterisk in a folder name or a file name on a Windows server. See Naming Files, Paths, and Namespaces