Error restoring SQL Server backup to a new database-CodePudding

I created a backup of a SQL Server database named mydb. I need to restore it programmatically with a C# code.

The restore must create a new database named mydbnew. I'm doing it using the Microsoft.SqlServer.Management.Smo library.

The code is this:

public void RestoreDatabase()
{
    string databaseName = "mydbnew";
    string userName = "user";
    string password = "password";
    string serverName = "(local)\\SQLEXPRESS";
    string sourcePath = @"c:\temp\";
    string fileName = $"mydbbackup.bak";

    ServerConnection connection = new(serverName, userName, password);

    Server sqlServer = new Server(connection);

    BackupDeviceItem deviceItem = new(sourcePath   fileName, DeviceType.File);

    Restore restore = new Restore();
    restore.Database = databaseName;
    restore.ReplaceDatabase = true;

    restore.NoRecovery = false;

    restore.Devices.Add(deviceItem);

    RelocateFile dataFile = new RelocateFile();
    dataFile.LogicalFileName = databaseName   "_data";
    dataFile.PhysicalFileName = databaseName   ".mdf";

    RelocateFile logFile = new RelocateFile();
    logFile.LogicalFileName = databaseName   "_log";
    logFile.PhysicalFileName = databaseName   ".ldf";

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

    restore.SqlRestore(sqlServer);

    restore.Devices.Remove(deviceItem);
}

I get an error at restore.SqlRestore(sqlServer):

Logical file 'mydbnew_log' is not part of database 'mydbnew'. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.

What is wrong in my code?

CodePudding user response：

Solved. I changed the Relocate section with this version:

RelocateFile dataFile = new RelocateFile();
dataFile.LogicalFileName = "mydb";
dataFile.PhysicalFileName = Path.Combine(@"C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA", "mydbnew.mdf");

RelocateFile logFile = new RelocateFile();
logFile.LogicalFileName = "mydb_log";
logFile.PhysicalFileName = Path.Combine(@"C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA", "mydbnew.ldf");

Of course I must work to get the paths and names dinamically, but it works.

CodePudding user response：

Looks to be 2 issues, first one:

    RelocateFile logFile = new RelocateFile();
    dataFile.LogicalFileName = databaseName   "_log";
    dataFile.PhysicalFileName = databaseName   ".ldf";

You are creating a object with name logFile, but in the next statement setting values for the old variable.

I expect you want it to be:

    RelocateFile logFile = new RelocateFile();
    logFile.LogicalFileName = databaseName   "_log";
    logFile.PhysicalFileName = databaseName   ".ldf";

The next issue, the LogicalFileName is the actual logical name in the original database. But, the PhysicalFileName is the new name.

So, as example, if your DB is like this,

then the code is like this:

        RelocateFile dataFile = new RelocateFile();
        dataFile.LogicalFileName = "Mine";
        dataFile.PhysicalFileName = sourcePath   databaseName   ".mdf";

        RelocateFile logFile = new RelocateFile();
        logFile.LogicalFileName = "Mine_log";
        logFile.PhysicalFileName = sourcePath   databaseName   ".ldf";

The RelocateFile uses the logical name of old (original database), but creates file in the new location mentioned in your PhysicalFileName

The above code creates file with new name.

Good luck.