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.