SQLTeam.com | Weblogs | Forums

Copy Database to another server without downtime - Help


#1

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!


#2

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.


#3

JamesK - Thanks. I had to play around with the search function to get to the directory where the backup is stored. All set now.


#4

Here are the step by step process to copy database from one Server to another. Go through it:
https://www.cybrary.it/0p3n/best-way-copy-sql-server-database-one-server-another/


#5

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.


#6

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. !