Copying or moving databases is one of the most common tasks for data professionals who regularly deal with deploying scripts or new solutions across different environments. With SQL Server, we have multiple ways by which we can accomplish this natively without using third-party tools.
Native Solutions within SQL Server
Here are few ways by which we can accomplish this task natively in SQL Server:
- Within SQL Server, we normally perform full database backups on the source database, copy it over to the target server, and then restore. This could turn out to be a challenging task for newbies or for professionals who are not in the DBA role. Unless you have a proper scripting solution, you may run into issues and struggle to accomplish this while dealing with huge databases.
- Another option is the detach\attach method. This method is popularly used but again you have to be careful while dealing with large databases or with databases that include a number of data or log files. Also, the source database would go offline while using this approach. Unless you are an experienced Database Administrator, you may still end up having issues while dealing with large databases.
There are other options that can be used such as using the copy database wizard or import\export data wizard for the purpose of data import\export. You also need to ensure that the logins and permissions from the source server are migrated correctly to the target server otherwise application users may have issues connecting correctly to the database.
Using dbForge Studio for SQL Server to copy or move databases from one instance to another or from one server to another
Coming to third-party software, Devart’s dbForge Studio for SQL Server has a cool function built into the product to perform the copy or move of databases in a few simple steps. We will review the process with a demo in this article.
The trial version lasts for a month and gives you some sufficient time to test the other functionalities embedded within the product. The Enterprise trial version 5.5 is used for this demo.
Once you run dbForge Studio, you will get this screen to update the database connection details. With this, you will be able to connect to your SQL Server instance.
In order to perform additional connections to other SQL instances, click on the icon which will open the window for a new database connection.
Below, you can see that I have already made a connection to an existing SQL instance.
After you connected to your SQL Server instances, you can view the databases you plan to copy or move across the instances. In this demo, we are going to copy the database test from the SQL instance: Boulcott to Boulcott\INST2.
To copy the test database to INST2, go to the Database Sync option on the main tab and click Copy Database.
The Copy Database window opens. There you can enter the details of the source and target servers. In this demo, we are copying the database across instances that are on the same machine. You can use the dbForge tool to copy databases across different servers too. Enter the server details as shown, make sure that the connection is successful, and then click Next.
Once done, the list of available databases on the source server appears. Select the database you plan to copy across. In our example, it is the test database. Select the Copy option checkbox and click Next. Here I have selected only one database to be copied. But the tool is not limited to one database at a time as we can select multiple databases to copy or move at the same time.
Select options for the Target database location
As you can see, the data and log file locations for the target database can be changed to the ones you were planning to use. The values get auto-generated but can be easily modified. The name of the target database can be changed too.
Click Next. The Transfer settings window appears. You need to ensure that the SQL Server Agent account has sufficient privileges to perform the copy operation. Review this section carefully as it may lead to issues if the SQL Server Agent account does not have the required permissions. In my case, as the instances are within the same server, there is no requirement for a shared folder directory. However, a shared folder directory with sufficient permissions for the SQL Agent account is required if you have to copy the database across SQL instances on different servers. Once you are done with the review, click Next.
On the Copy logins tab, you may decide on the correct option based on your requirement. By default, Copy all logins used by selected databases is enabled. Make the appropriate choice in your case, review, and click Next.
Execute the copy process with dbForge Studio
The screenshot below shows where you can start the execution process for the copy task. We have the option to choose on the types of error handling possible during the execution process. By default, prompt a user for an action is selected. In this demo, I have also enabled the option to write a report to a log file which would be handy to deal with any sort of errors. The error log is quite verbose and gives you the reason for any process failure in details. Once done, click on the Execute option which will launch the execution process.
You can view the progress of the execution task.
Copy process completion
Once the process completes, you will see this notification that confirms that the copy process has completed.
By clicking the Show log file option, you can see the details of the process execution and status of the completed tasks. You can perform more copy operations by clicking the Copy more option.
Check target SQL instance
The next step would be to connect to your target SQL instance and validate that the database is copied across. On the target SQL instance, there is the test database that was copied successfully using dbForge Studio for SQL Server.
Move databases from source to target SQL instance
Earlier, we were able to successfully copy a database from the source server to the target server. We could also use this tool and the previous demo if you need to move databases instead.
On the Select databases window, select the Move option.
This would basically migrate the databases from the source server to the target one. Just go through the steps described previously. I had intentionally stopped the SQL Server Agent on the target server and faced this error screen.
When you click No, it will take you to the option to view the log files.
The Show log file… button will take you to the detailed log file where the actual failure reason can be identified.
Here, you can see precisely the reason for the failed operation. You will also see that a SQL Agent job gets created on the Target server during the execution and can be viewed.
This job exists only for the duration of the execution process and gets deleted once the process completes. Connect to both the Target and Source SQL instances to confirm that the databases have indeed moved across. The below screenshot confirms that the databases 1 and 2 were successfully moved from the source server to the target one.
Copy databases from the source SQL instance to the target one and override
So far, we have copied and moved databases from the source server to the destination one. Then, perform an additional step to copy and override the WideWorldImporters databases.
This process is similar to the previous steps for copying or moving databases with only one additional step to select the override option.
Just follow the rest of the steps similar to what was done earlier and the process would succeed once it completes. To summarize, using the Copy database option in the dbForge Studio tool, you could perform the following functions:
- Copy databases from the source server to the target one
- Move databases from the source server to the target one
- Copy and override databases from the source server to the target one
- Move and override databases from the source server to the target one
Conclusion
- With this demo, you have seen the ease with which a database can be copied from one SQL instance to another with a very little user input using dbForge Studio
- Another advantage of this tool is that multiple databases can be copied or moved across different instances at once
- Very little technical expertise is required to perform these tasks as the tool is user-friendly and GUI-based
- This tool can be quite handy especially if you are rolling out deployments quickly across different server environments and if the time is a constraint
Useful tool:
dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.
