restoring from a backup file to a new database

I am trying to restore from a backup file to a new database with SQL2000. Here is my C# code:

SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.LoginSecure = true;
srv.Connect("servername","","");

//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();

//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = @."\\server\backupfile.bak";
//set the database to the chosen database
res.Database = "databasename" + "-restored";

// Restore the database
res.ReplaceDatabase = false;
res.SQLRestore(srv);

This code gives me an error basically telling me it can't restore as the ".mdf" and ".ldf" files are already in use. I think I need to specify new names for these like I have I have specified a new name for the database. (e.g. appended "-restored" to it.) but I am not sure how. Any help would be greatly appreciated.

Sorry, I don't know DMO well enough to answer your question, but here's an example using SMO and VB that may help. After setting the Database name, insert these lines of code, before the Restore:

Dim alRSFile As New RelocateFile
Dim alRSLog As New RelocateFile
alRSFile.LogicalFileName = "AdventureWorks_Data"
alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
alRSLog.LogicalFileName = "AdventureWorks_Log"
alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
res.RelocateFiles.Add(alRSFile)
res.RelocateFiles.Add(alRSLog)

Allen

|||

I believe SQLDMO is more easy to use and comprehensive. You already mentioned the problem.

When you take backup, and then restore it in a new database, the name of the .mdf file for the new database is same as was backup containing.

So if a database with the same .mdf file name is there conflict arises. Even if you dont have it there it would work fine for the first time but for 2nd time you need to change the physical file name as we do in sql commands mentioned below....

RESTORE DATABASE db3
FROM DISK = 'c:\me.bak' \\ Original name of the .mdf file was me.mdf.
WITH MOVE 'me' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db3.mdf'

RestoreClass of SQLDMO library provided these features...but how can we specify the MOVE attribute here....still have no clues about it ..If any one has any idea...post the reply as early as possible...

|||

The code I included in my last post effectively does the MOVE operation, by setting the properties of the File objects before the restore. While you may find DMO easier to use, it's probably more due to familiarity than anything else. Everything SSMS does is done through SMO, so there can't be an argument that it's not comprehensive, and the features that are new to SQL Server 2005 aren't available in DMO.

Most importantly, though, is that there are a variety of ways to accomplish anything, so if DMO suits your needs, then by all means use it.