If you're doing this with C#, and the physical paths are not the same, you need to use RelocateFiles, as one answer here also mentioned.
For most cases, the below code will work, assuming:
You're just restoring a backup of a database from elsewhere, otherwise meant to be identical. For example, a copy of production to a local Db.
You aren't using an atypical database layout, for example one where the rows files are spread across multiple files on multiple disks.
In addition, the below is only necessary on first restore. Once a single successful restore occurs, the below file mapping will already be setup for you in Sql Server. But, the first time - restoring a bak file to a blank db - you basically have to say, "Yes, use the Db files in their default, local locations, instead of freaking out" and you need to tell it to keep things in the same place by, oddly enough, telling it to relocate them:
var dbDataFile = db.FileGroups[0].Files[0];
restore.RelocateFiles.Add(new RelocateFile(dbDataFile.Name, dbDataFile.FileName));
var dbLogFile = db.LogFiles[0];
restore.RelocateFiles.Add(new RelocateFile(dbLogFile.Name, dbLogFile.FileName));
To better clarify what a typical case would be, and how you'd do the restore, here's the full code for a typical restore of a .bak file to a local machine:
var smoServer = new Microsoft.SqlServer.Management.Smo.Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlServerInstanceName));
var db = smoServer.Databases[dbName];
if (db == null)
{
db = new Microsoft.SqlServer.Management.Smo.Database(smoServer, dbName);
db.Create();
}
restore.Devices.AddDevice(backupFileName, DeviceType.File);
restore.Database = dbName;
restore.FileNumber = 0;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
var dbDataFile = db.FileGroups[0].Files[0];
restore.RelocateFiles.Add(new RelocateFile(dbDataFile.Name, dbDataFile.FileName));
var dbLogFile = db.LogFiles[0];
restore.RelocateFiles.Add(new RelocateFile(dbLogFile.Name, dbLogFile.FileName));
restore.SqlRestore(smoServer);
db.SetOnline();
smoServer.Refresh();
db.Refresh();
This code will work whether you've manually restored this Db before, created one manually with just the name and no data, or done nothing - started with a totally blank machine, with just Sql Server installed and no databases whatsoever.
