I need to copy all the databases from the one server to the another server. To prepare for the migration, I'm running a test on my test server. I've created a new database called "Test-DB". I've been trying the "Copy Database" method:
I created a using a simple blank "Test Database" on my test server, and
trying the above method to my current production server "Test Database" I
created. The problem I'm running into is that the destination server isn't
recognizing my alt login. (see attached)
Last I checked, I have all the permissions I need on both servers. Not sure why this error is occurring?
Alternatively, I tried just taking a simple copy of the test database, and do a
restore on the production server. The problem is, even though I copied the
backup file to the production server, when I goto "Restore Database", the
.bak file doesn't show in the directory.
Which is weird, no backup files are showing in the F:\backup folder and I know they are there!
I know there is the attached/detach method, but I'm trying to see if I can copy databases to another server without having to turn off the SQL Server services so there no downtime. All help is greatly appreciated. Thanks!
With regards to using the Copy Database Wizard, it is possible that SQL Agent is running under a different user or proxy and that user/proxy does not have the required permissions.
As far as backing up and restoring, when you browse for the backup file, what you see as F:\Backups is the local F: drive on the destination server, i.e., the server where you are restoring the file to and not the F drive of the machine where you are logged in.
jason_clark. Thank you. I followed the directions in the article, and I still end up with the same error in the first screen shot. Another one of my Admin suggested it's either a process that is backing up the DB badly, or there is a drive access permissions issue. Which makes so sense since I am the owner of the DB I'm trying to copy.
