I've read a dozen different blogs, as well as reading through the msdn examples and they just aren't working for me.
Ultimately what I'm trying to do is automate moving a DB from our production instance to our dev instance, or the other direction.
The approach I've taken is thus: 1. backup/restore to a temp DB 2. detach temp DB 3 copy mdf and ldf files to the other instance 4. reattach.
I'm stuck on 1 and I cannot understand why. Everything I've read claims this should be working.
NOTE: I've set dbName to the db I want to restore to. I have also set restore.Database = dbName, where restore is an instance of the Restore class in the smo namespace.
mdf.LogicalFileName = dbName;
mdf.PhysicalFileName = String.Format(@"{0}\{1}.mdf", server.Information.MasterDBPath, dbName);
ldf.LogicalFileName = dbName + "_log";
ldf.PhysicalFileName = String.Format(@"{0}\{1}.ldf", server.Information.MasterDBPath, dbName);
restore.RelocateFiles.Add(mdf);
restore.RelocateFiles.Add(ldf);
restore.SqlRestore(server);
This is the exception I'm getting:
The file 'D:\\MSSQL.MIQ_Dev\\MSSQL.2\\MSSQL\\Data\\MIQDesign2Detach.mdf' cannot be overwritten. It is being used by database 'MIQDesignTest2'.\r\nFile 'MIQDesign' cannot be restored to 'D:\\MSSQL.MIQ_Dev\\MSSQL.2\\MSSQL\\Data\\MIQDesign2Detach.mdf'. Use WITH MOVE to identify a valid location for the file.\r\nThe file 'D:\\MSSQL.MIQ_Dev\\MSSQL.2\\MSSQL\\Data\\MIQDesign2Detach.ldf' cannot be overwritten. It is being used by database 'MIQDesignTest2'.\r\nFile 'MIQDesign_log' cannot be restored to 'D:\\MSSQL.MIQ_Dev\\MSSQL.2\\MSSQL\\Data\\MIQDesign2Detach.ldf'. Use WITH MOVE to identify a valid location for the file.\r\nProblems were identified while planning for the RESTORE statement. Previous messages provide details.\r\nRESTORE DATABASE is terminating abnormally.
Why is this trying to overwrite the original mdf? Isn't the RelocateFiles stuff supposed to specify that you want it being saved to a different physical filename?