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 databases from one Server to another Server. This is a challenging task for end-users; therefore, not easy to execute. Nevertheless, it will not be more difficult from now onwards as in the forthcoming section of this blog; we will explain the best method to move databases between SQL Servers efficiently.
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.
- Right-click on the database, select the option Tasks and then choose the Copy Database option.
- After clicking on the Copy Database Wizard then, the following screen will appear. Press the Next button.
- Then, type the Source name and Server authentication mode and then click Next to establish the connection to a source Server.
The Copy Database Wizard is one of the most significant utilities in SQL Server. It transfers or copies databases and particular server objects easily from one instance of SQL Server to another. The important thing to remember here is that it executes this with no server downtime. By using this wizard, users can do the following:
- Select a source and the target server.
- Pick database(s) to move or copy.
- Name the file location for the database(s).
- Copy logins to the target server.
- Copy new objects, jobs.
- Copy user-defined stored procedures and error messages.
- Configure when to move or copy the database(s).
Limitations and constraints
There are certain limitations. For instance, the Copy Database Wizard is not available in the Express edition, and it cannot be used to copy or move system databases. The wizard cannot be used to move or copy databases marked for replication, and such databases are inaccessible, loading, offline, recovering, or in emergency mode. The databases with data or log files stored in Microsoft Azure storage also can't be moved or copied.
The other important thing to remember is that the detach and attach method detaches the database. It also moves or copies the database files like .mdf, .ndf, .ldf, and reattaches the new target database.
Before proceeding, let us know about these terms:
a) Source Server: Used to identify the server name 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 a Username and Password.
c) Using SQL Server Authentication: It permits a user to connect with the 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 the 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 connecting through a Microsoft user account on a 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 same 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 the Next button.
- From Select Databases, you can view "Which databases would you like to move or copy"?. Now, choose the Copy option and click the 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 the Next button.
- Now, enter the log events if it fails to complete the 'copy' job. Here are two provided options:
- System event view
- Local path
Afterward, select Windows event logs and then click the 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 the 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 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 the faster accomplishment of the migration task.
