Change logical database name with SMO

Question

How can I change the logical database name when restoring a database with SMO?

/Viktor

Answer 1

//restore is the Restore object in SMO

restore.RelocateFiles.Add(new RelocateFile(SourceDataFile.Name, Path.Combine(destinationDirectory, destination.Database + ".mdf")));
restore.RelocateFiles.Add(new RelocateFile(SourceLogFile.Name, Path.Combine(destinationDirectory, destination.Database + "_log.ldf")));

restore.SqlRestore(destinationServer);

var destinationDatabase = destinationServer.Databases[destinationDatabaseName];

//renaming the logical files does the trick

destinationDatabase.FileGroups[0].Files[0].Rename(destinationDatabaseName);
destinationDatabase.LogFiles[0].Rename(destinationDatabaseName + "_log");

Answer 2

You can't rename the logical database files with a SQL RESTORE DATABASE: it's not offered. Only physical files can be changed using WITH MOVE

You rename logical files by using ALTER DATABASE in SQL, normally.

This appears to be be confirmed by the RelocateFile SMO class.

Answer 3

Rahul's code is correct: Restoring to new physical files and renaming logical files is a two-step process:

The RelocateFile call is saying "map this logical file name to this physical file". You need to use the logical file names of the original backup here NOT new ones, otherwise you are likely to get ".mdf cannot be overwritten" exceptions.

To make new logical names, use the Rename() calls afterwards, as shown in Rahul's code.

However, if you want to change the name of the database using SMO:

var srvConn = new ServerConnection(serverName)     
{  
    LoginSecure = false,  
    Login = dbUserName,  
    Password = dbUserPassword,  
    DatabaseName = "master",               
};  
var mainDb = new Database(srvConn, "old database name");  
mainDb.Rename("new database name");
mainDb.Refresh();