March 12, 2007 at 2:24 pm
Hi
I have a environment where we have to create new individual databases with similar structure. For this I have a template db, which can be used to create new db by using Copy Database Wizard. In SQL Server 2000 I used to copy db by using script, but that doesn't work anymore in 2005. It manages to copy db files but restoring them as a new database fails. Now I want to be able to copy db using script also in SQL Server 2005.
So to my question:
Does anyone know a script which would copy all db-objects and full-text catalogs/indexes just like 'Copy database wizard' does. Or is there a way to extract script from the wizard to see what it is doing or start wizard by using script.
Cheers
-Jouni-
March 12, 2007 at 4:31 pm
You can run the sql profiler to see what is happening behind the scenes when copy database wizard runs...
But if I am not mistaken, copy database wizard detach the database and copy the database files to the destination and attaches the files at source and destination...
Same thing can be done using backup/restore...
MohammedU
Microsoft SQL Server MVP
March 13, 2007 at 6:10 pm
Detach is not a problem as copied data base is just a template.
Backup - Restore does not work (at least not with SQL Server Manager) because I'm trying to create a new DB with a different name to the same DB Server as where the template DB is located. Restore works only if db name is not changed.
My original, now non-working, script used to clone db using backup - Restore from disk method, but it doesn't work anymore.
Another problem which I encountered is that even when creating new db using "Copy Database Wizard" (Detach method) Full-Text indexing in new DB is totally stuck until whole SQL Server is rebooted.
Either we have configured something wrong big time or then problem is in SQL Server itself.
Any ideas how to start solving this problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply