How to restore SQL Server database backup to a newly created database?

Question

I've a SQL Server 2005 database backup named backupdb.bak.I want to restore it to database MyDatabase.

But before restoring I've to check whether MyDatabase already exists or not. If it does then I create a new database named MyDatabaseNew and I restore the backup file to this new database.

If I'm directly restoring the file into MyDatabase, the process is running fine. But when I'm checking the existence of MyDatabase and trying to create new database named MyDatabaseNew, it's giving error:

Restore failed for Server servername

My code looks like this:

Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
CreateDatabase(MyDatabaseNew);
restore.Database = MyDatabaseNew;
restore.ReplaceDatabase = true;

BackupDeviceItem deviceItem = new BackupDeviceItem("C:\\backupdb.bak",DeviceType.File);
restore.Devices.Add(deviceItem);
SqlConnection sqlCon = new SqlConnection("Data Source=.;Initial Catalog=MyDatabaseNewIntegrated Security=True;User ID=uid; Pwd=Pwd");
ServerConnection connection = new ServerConnection(sqlCon);
sqlCon.Open();

Microsoft.SqlServer.Management.Smo.Server smoServer = new Server(new  ServerConnection("."));
if (File.Exists("C:\\backupdb.bak"))
    // restore
    restore.SqlRestore(smoServer);
if (sqlCon.State == ConnectionState.Open)
    sqlCon.Close();

and my create database (in case MyDatabase exists) code looks like this, where I'm passing MyDatabaseNew as a parameter for this method:

public void CreateDatabase(string NewDBName)
{
  string str;
  SqlConnection myConn = new SqlConnection("Server=.;Integrated  security=True;
  database=master;User ID=uid;Password=Pwd");

  str = "CREATE DATABASE " + NewDBName + "";
  SqlCommand myCommand = new SqlCommand(str, myConn);
  try
  {
    myConn.Open();
    myCommand.ExecuteNonQuery();
    MessageBox.Show("New DataBase is Created Successfully", "Database Creation",
    MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  catch (System.Exception ex)
  {
    MessageBox.Show(ex.ToString(), "Database Creation", MessageBoxButtons.OK,
    MessageBoxIcon.Information);
  }
  finally
  {
    if (myConn.State == ConnectionState.Open)
    {
      myConn.Close();
    }
  }
}

Can any one tell me where I'm going wrong?

Answer 1

When restoring to the new database, you need to use an equivalent of Transact-SQL's RESTORE DATABASE ... WITH MOVE ... statement to specify new names for the data and log files:

restore.RelocateFiles.Add(new RelocateFile("MyDatabase", @"c:\MyDatabaseNew.mdf"));
restore.RelocateFiles.Add(new RelocateFile("MyDatabase_Log", @"c:\MyDatabaseNew.ldf"));

More information: Getting Started with SMO in SQL 2005 - Restores