September 27, 2007 at 12:54 pm
Currently, I have a development database for which I want to create a test database so that I can promote changes in development to test. In the future these databases will reside on different servers, but for now they are to be on the same server. The problem I am having is that when I try to restore my dev database to the test database(different name) the restore fails. SQL Server is expecting that I restore to the same name rather than a different name. Is there any way I can take advantage of copying an existing database to a different database on the same machine. I have some alternatives where I used the Generate Scripts functionality and generated the DDL and bcp'd data etc but I was looking almost for the attach/detach method with the flexibility of chaning database names. I hope my question makes sense. Thanks in advance for your response.
September 27, 2007 at 4:07 pm
restore database can use a DIFFERENT target database name. Just make sure you also change the physical filenames using the "move" option.
Cheers,
* Noel
September 27, 2007 at 4:43 pm
As Noel mentioned, you need to just change the name. The restore dialog in SSMS lets you just type a new name in there at the top.
September 28, 2007 at 11:29 am
Hi Noel and Steve,
Thanks very much for your help!! Things worked the way you directed. The piece I was missing was the moving of the datafile in the options section of the wizard.
Best Regards
Joe
September 30, 2007 at 10:36 am
Sounds like you will need to do this multiple times.
I usually create a job to do it - backup the source database then restore it with move and replace.
Then all you need to do is make sure there are no users in the destination database and kick off the job.
Cursors never.
DTS - only when needed and never to control.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply