Backup/Restore database using SQL-DMO

Hi, all,

I am using SQL-Server 2000, VC++6.0, SQL-DMO and I am trying to backup/restore a database. Here is what I need:

1. Backup a database, saying A. Then then I got a file say FileA.

2. Create a database B.

3. "Restore" database B based on FileA on the SAME SQLServer. Basically, I want everything in database B is exactly the same as database B.

The problem I got is SQL-DMO complains that the Data file of A (..\Microsoft SQLServer\MSSQL\DATA\A_Data.MDF) is being used.

The key point here:

1. In the backup file. I assume it keeps the data file name A_Data.MDF, A_Log.Ldf, ...
2. Since I need to "restore"(or I should say create) the database B from backup file A in the same SQL server, but File A is still being used, that causes problem.
3. If there is a way somehow rename A_Data.MDF, A_Log.Ldf in the backup file, then I guess the problem can be solved.

Any hints ? Please help.

Thanks.




mikechenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
You need to look at the RelocateFiles property.  This tells DMO where, and with what name, the files are to be restored.

Here's a VB example:

Option Explicit
Dim oSQLDMOSQLServer
Dim oSQLDMORestore

Set oSQLDMOSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLDMOSQLServer.LoginSecure = True
oSQLDMOSQLServer.Connect "MyServer"

Set oSQLDMORestore = CreateObject("SQLDMO.Restore")
oSQLDMORestore.Database = "Northwind2"
oSQLDMORestore.Files = "C:\Backups\NorthwindBackup.bak"
oSQLDMORestore.RelocateFiles = _
    "[Northwind]" & "," & "[c:\temp\Northwind2.mdf]" & "," & _
    "[Northwind_log]" & "," & "[c:\temp\Northwind2.ldf]"

oSQLDMORestore.SQLRestore(oSQLDMOSQLServer)
0
 
Cool2kCommented:
Setting the property DatabaseFiles of Restore/backup object may help you. I never tried.

I am also interested in knowing the answer.

Magesh
0
 
mikechenAuthor Commented:
Thank you for your information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.