I have 1 sbs 2003 server with sql server 2005 and we purchased another server as a backup which also has sql server 2005. Our goal was to be able to switch to the new server in the cse of an emergency. I am trying to copy a database to the new server using the copy database method in sql server.
When I try the detach and copy method (preferred) i am using the trusted login on the source and the sa user on the destination. Everything goes ok until the very end when the sql agent on the dstination machine cannot login anonymously on the destination machine. So I get that error in the event viewer of the destination machine and the copy fails.
I would like to set up a copy package to move the database on a nightly basis. Right now I would like the original package to work. I can provide access through remote desktop to both machines. But before doing this I would like to know that someone has a possible solution that I haven't tried. If possible I would work through it with help from you. If? someone can help me solve this I am willing to pay $100. I am in a hurry to solve.
Thanks