Changing the name of a logical database using SMO

Question

How to change the name of a logical database when restoring a database using SMO?

/Victor

Answer 1

You cannot rename logical database files using SQL RESTORE DATABASE : it is not suggested. Only physical files can be modified with WITH MOVE

You rename logical files using ALTER DATABASE in SQL, as a rule.

This is apparently confirmed by the SMO RelocateFile class.

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

Answer 3

The correct Rahul code: restoring new physical files and renaming logical files is a two-step process:

The RelocateFile call says, "Copy this logical file name into this physical file." You must use the logical file names of the original backup here NOT new, otherwise you will probably get " .mdf cannot be overwritten " exceptions.

To create new logical names, use the Rename() calls after that, as shown in the Rahul code.

However, if you want to change the database name 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();