Robert Davis wrote a great script back when he published his Mirroring book. I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins. His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”. It turns out that the publisher didn’t get the right script version published with the book. That’s when this post from Robert with the full script was born…
I also wrote about it HERE.
This script creates a stored procedure to handle the move and also uses Linked Servers. If you can’t have linked servers in your environment this is not a good choice for you. However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer. It breaks the rule but it does it fast enough maybe no one will notice.
Read the whole thing.