5

I am sing C#, .NET 3.5, and SMO to create a copy of a database (our production database) to some other databases (our DEV and Test-database) in SQL Server 2005.

I have managed to create a backup from a database, an to restore it. this of course overwrites the existing one. I have used this CodeProject guide.

How do I restore a backup of a database into another database, with a different name on the same server?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Martin
  • 195
  • 1
  • 12
  • You have to set the Database property to the new name. You also have to make sure, that the log/data file(s) name do not macht existing ones. Maybe duplicate http://stackoverflow.com/questions/1627145/smo-restoring-to-a-different-db – Bernhard Kircher Aug 16 '11 at 14:19

2 Answers2

9
using Microsoft.SqlServer.Management.Smo;

string backupFile="Backupfile";//.bak file

string dbName = "DBName";

string dbServerMachineName = "MachineName";

Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(dbServerMachineName);

Database database = new Database(server, dbName);

//If Need

database.Create();

database.Refresh();

//Restoring

Restore restore = new Restore();

restore.NoRecovery = false;

restore.Action = RestoreActionType.Database;

BackupDeviceItem bdi = default(BackupDeviceItem);

bdi = new BackupDeviceItem(backupFile, DeviceType.File);

restore.Devices.Add(bdi);

restore.Database = dbName;

restore.ReplaceDatabase = true;

restore.PercentCompleteNotification = 10;

restore.SqlRestore(server);

database.Refresh();

database.SetOnline();

server.Refresh();
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
soniya
  • 91
  • 1
5

In short:

  1. set the Restore.Database property to a new name,
  2. set the Restore.ReplaceDatabase property to true,
  3. specify new data and log files using the Restore.RelocateFiles property.
blackgreen
  • 34,072
  • 23
  • 111
  • 129
Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111