0

For reliability first I copy locally a .bak file from the network and then try to restore it to the local SQL Server instance. However the restore wants to restore the DB file to the original folder location which doesn't exist locally. How can I tell it to use the local SQL instance default file directory?

public void RestoreDB(string file)
{
    try
    {
            SqlConnection sqlConnection = new SqlConnection()
            {
                ConnectionString = $"Data Source=(LocalDB)\\MSSQLLocalDB;User ID=xx;Password=xx"
            };

            ServerConnection serverConnection = new ServerConnection(sqlConnection);

            Server dbServer = new Server(serverConnection);

            Restore restore = new Restore()
            {
                Action = RestoreActionType.Database,
                Database = "DBTest",
                NoRecovery = false,
                ReplaceDatabase = true
            };

            restore.Devices.AddDevice(file, DeviceType.File);

            restore.SqlRestore(dbServer);
        }
    catch (Exception ex)
    {
        throw ex;
    }
}
user3140169
  • 221
  • 3
  • 12
  • 1
    That's going to need the `.RelocateFiles` property, which like most SMO things has no proper documentation (anymore?) but [this answer](https://stackoverflow.com/a/17547737/4137916) demonstrates one possible use (which can be simplified if you happen to know all the files involved). AFAIK this will need absolute paths -- figuring out the data path of the local instance is a separate challenge, though. Something like `select physical_name from master.sys.database_files` might be required. – Jeroen Mostert Jan 28 '20 at 15:11
  • I tried the @JeroenMostert suggestion which makes sense but '''relocateFile.PhysicalFileName = $@"{DBServer.BackupDirectory}{relocateFile.LogicalFileName}.mdf";''' doesn't properly set the physical path. It still includes the original path plus my value. – user3140169 Jan 28 '20 at 16:21
  • Well you almost certainly don't want `DBServer.BackupDirectory`; that won't necessarily match the database file location. Remember also that SMO is just a fairly thin wrapper around just actually executing statements; you can always hook up a Profiler instance to see the `RESTORE` statement it actually cooks up if there's any doubt (or, if you get sufficiently frustrated, you can even write code that just generates the `RESTORE` statement -- my preferred approach as opposed to using SMO for anything at all :-P). – Jeroen Mostert Jan 28 '20 at 16:24

0 Answers0