Best Way to Copy SQL Server Database From One Server to Another
Manual Method to Copy Database from one Server to AnotherGo 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.
- 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.
- 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.
Learn more about Cyber Security from this FREE course >
- 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.
- 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 UpMultiple 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.
Take our course in AWS to learn more about database storage >