Best Way to Copy SQL Server Database From One Server to Another

December 24, 2017 | Views: 19891

Begin Learning Cyber Security for FREE Now!

FREE REGISTRATIONAlready a Member Login Here

Most of the time users want to copy the database from one Server to another without any data loss. One of the most emerging queries nowadays is to copy SQL Server database from one Server to another Server. This is a challenging task for end users, therefore, not an easy to execute. Nevertheless, it will not more difficult from now onwards as in the forthcoming section of this blog; we will explain the best method to move database between SQL Servers in an efficient way.

Manual Method to Copy Database from one Server to Another

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.

Before proceeding, let us know about these terms:

a) Source Server: Used to identify the Server name where you want to move or copy on the databases. Manually enter the path, or click the ellipsis button to navigate towards the desired Server. The Server should be at least Microsoft SQL Server 2005.

b) Use Windows Authentication: It permits a user to connect with SQL Server by providing Username and Password.

c) Using SQL Server Authentication: It permits a user to connect with provided SQL Server Authentication username and password.

  1. Username: It can be used to enter the username for connecting to the database. This option is likely just available if you have been selected to connect with SQL Server Authentication.
  2. Password: It can be used to enter the valid password for the login. This option is available if you get selected to connect via SQL Server Authentication.
  • In the next steps, enter a Destination Server name and authentication mode. Press Next

Before that, let us understand all these terms:

a) Destination Server: That is used to identify the Server name to which the databases you need to move or copy to the desired location. Manually enter, or click on ellipsis to move towards the desired Server. The Server should be at least MS SQL Server 2005.

Note: You can utilize the destination that is a Server cluster; make sure that the Copy Database Wizard can be selected on the clustered destination Server.

b) Use Windows Authentication: It permits to connect through Microsoft user account in Windows machine.

c) Use SQL Server Authentication: It allows a user to connect by giving the SQL Server Authentication username and password.

  1. Username: This option can be used to enter a username to connect with. It is only available if you have chosen to connect with SQL Server Authentication.
  2. Password: This can be used to enter the exact password to log in. It is only available if you have been selected to connect with SQL Server Authentication.
  • You can now use the SQL Management Object method and go to Next button
  • From Select Databases, you can view “Which databases would you like to move or copy”?. Now, choose the Copy option and click Next option. (If we have selected Move then the database can be moved to a new Server).
  • After this, you can verify the MDF and log files path on the destination system
  • If you may have any job or procedure inside the master database then, select, further click Next button
  • Now, enter the log events if it fails to complete the ‘copy’ job. Here are two provided options:
    1. System event view
    2. Local path
    Afterward, select Windows event logs and then, click Next option
  • If you want to run or schedule the run then, choose “Run immediately”. Then, navigate to Next
  • In the last, you have to check the options and click the Finish button
  • Finally, you can view the status of progress report
  • Once close the windows successfully and then, refresh the Server B node. Now, you can appear Test DB is available within the specific data.

Let’s Wrap Up

Multiple users want to move their database from one Server to another in Microsoft SQL Server. In this blog, we have discussed a direct manual procedure that helps to copy SQL Server database from one Server to another Server. Further, you must follow each and every step carefully to accomplish the entire task. Although the procedure of migrating data from one Server to another Using Management Studio is lengthy and time taking, So, One can also go with SysTools SQL Server Database Migration Tool for faster accomplishment of migration task.

Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Use Cybytes and
Tip the Author!
Join
Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Ready to share your knowledge and expertise?
Comment on This

You must be logged in to post a comment.

Our Revolution

We believe Cyber Security training should be free, for everyone, FOREVER. Everyone, everywhere, deserves the OPPORTUNITY to learn, begin and grow a career in this fascinating field. Therefore, Cybrary is a free community where people, companies and training come together to give everyone the ability to collaborate in an open source way that is revolutionizing the cyber security educational experience.

Cybrary On The Go

Get the Cybrary app for Android for online and offline viewing of our lessons.

Get it on Google Play
 

Support Cybrary

Donate Here to Get This Month's Donor Badge

 
Skip to toolbar

We recommend always using caution when following any link

Are you sure you want to continue?

Continue
Cancel