Hello fellow SQL Admin,
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:
Microsoft - Use the Copy Database Wizard
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.
JamesK - Thanks. I had to play around with the search function to get to the directory where the backup is stored. All set now.
Here are the step by step process to copy database from one Server to another. Go through it:
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.
Go through the following steps to move the database from one Server to another Server in SQL Server. Let’s have a look:
- First of all, launch the SQL Server Management Studio from Object Explorer and connect to the Source Server !
- Now, right-click on database, select an option Tasks, and then, choose Copy Database option
- After clicking on the Copy Database Wizard then, the following screen will appear. Press Next button !
- Then, type the Source name as well as Server authentication mode and then, click Next for establishing the connection to a source Server. !