June 21, 2005 at 11:02 am
My AD & P division is experiencing strange behavior with our sql servers. When we copy a database from server to server, the wizard hangs just after copying the mdf files from machine to machine. I think it hangs when transferring logs, but I'm not quite sure. We aren't talking massive databases, so after 30 minutes we get suspicious and close the log details dialog. Upon refreshing the database list on source server & destination server, we discover that source database has disappeared from database list & does not appear on destination list. The data files & log files exist on both machines. On my local instance (MSDE). What are we missing?
-- Upon further reading, I find that both instances have been dettached and placed into single-user mode. What issues am I facing by attempting to manually reattach?
June 24, 2005 at 12:23 pm
Hello,
Copy Database wizard is supposed to use DTS according to documentation and it does, but it includes detaching the source database. That is why I would really recommend restoring backup of the source database on the destination server for the copy.
Wizard as I understand gracefully detaches databases. So you may use sp_attach_db or sp_attach_single_file_db to attach the source database back. Please read first about both sdtored procedures on Books Online.
Yelena
Regards,Yelena Varsha
June 24, 2005 at 12:33 pm
Yelena,
Thanks for the reply. I've read over all that business in BOL and got everything sorted out. But my understanding of wizards : used by the relatively inexperienced to automate tasks (i.e. I have this here and want it there & don't care how it gets there). I've also come to the conclusion that I should skip the attempt to keep things at a high level and get my hands dirty w/ manual attach / dettach or backup /restore.
June 24, 2005 at 1:22 pm
James,
Wizards are not for inexpirienced, judging by your result and my own experience only very skilled DBAs who understand minor details how it works should use them.
I usually do things manually if I have to do it 3 times or less. If it is 4 times or more I write a script. I also write scripts even for simple tasks if I am preparing for the production run. For example, if I have to detach and re-attach database or backup and restore for the production upgrade, I would have a script, test it and then run it during the upgrade.
Yelena
Regards,Yelena Varsha
June 24, 2005 at 3:52 pm
In Must be an easier way (move logs) I put out a script that generates a script to detach, copy and re-attach multiple databases on the same server.
You might be able to use that in the QA to do the detach, copy and re-attach on the current server. The only problem is finding a way to do it on the foreign server. An I bet if you paly around you could do it using the xp_cmdshell and isql/osql script to the foreign server.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply