Error restoring SQL Server backup to a new database

Question

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?

Answer 1

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.