Using SQLDMO to Restore a Database to a New Database

Posted on July 11th, 2006 mitch  Print No comments

My head hurts – mostly from banging it against the wall for the past three hours as I attempted to get SQLDMO to restore a full SQL backup to a completely new database.

You wouldn't think it was that hard, but unfortunately, everything has to be high on the very correct side in order for the Restore function to work properly.

Again, this is to restore to a new database, not the same one the backup came out of.  Anyway, here is the code:

// Assumptions:
// sWorkingDirectory = "c:\data"
// fromdb = "MYORGANIZATION"
// todb = "NEWORG"
// sPrimaryDatabaseDirectory = "c:\sql\data\"
//
// create the full path of the physical location
// of the database backup file.
// sDatabaseFullpath = c:\data\MYORGANIZATION_MSCRM.bak
//
string sDatabaseFullpath =
string.Format(@"{0}\{1}_MSCRM.bak", sWorkingDirectory, fromdb);
// Create a SQLDMO SQL Server Object and connect
// to the database using a Windows Trusted connection.
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.LoginSecure = true;
srv.Connect(sDatabaseDataSource, null, null);// Create a SQL Server Restore Object
// and instruct it to restore the Full database
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; // Formulate our Old and New database names
// sOldDatabaseName = MYORGANIZATION_MSCRM
// sNewDatabaseName = NEWORG_MSCRM
string sNewDatabaseName = string.Format(@"{0}_MSCRM", todb);
string sOldDatabaseName = string.Format(@"{0}_MSCRM", fromdb); oRestore.Database = sNewDatabaseName;
oRestore.Files = sDatabaseFullpath;

// This the important part, and what took me so long.
// We want to instruct SQL Server to install our files
// in a new location, not the default location from
// whence they came. BUT, we MUST use the OLD logical
// names or SQL Server will get really confused and
// throw an error.
//
// RelocateFiles =
// [MYORGANIZATION_MSCRM], [c:\sql\data\NEWORG_MSCRM.mdf]
// [MYORGANIZATION_MSCRM], [c:\sql\data\NEWORG_MSCRM.ldf]
//
oRestore.RelocateFiles =
string.Format(@"[{0}],[{1}{2}.mdf],[{0}_log],[{1}{2}.ldf]",
sOldDatabaseName,
sPrimaryDatabaseDirectory,
oRestore.Database);
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(srv);


Development

(2 votes, average: 4.00 out of 5)

 Loading ...

1,226 views