Change logical database name with SMO

Question

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

/Viktor

Linebreeding

Do you mean the database name, or the filenames? All the answers below appear to assume you mean the logical file names? – Vestpocket

Answer 1

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.

Bread

Answer 2

//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");

Bodleian

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();

Palladio