• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

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.


1 Solution
Setting the property DatabaseFiles of Restore/backup object may help you. I never tried.

I am also interested in knowing the answer.

Brendt HessSenior 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 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]"

mikechenAuthor Commented:
Thank you for your information.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now