How to Merge Two SQL Server Databases & Files
The general way to merge SQL server databases is by using the native SQL queries. But that consumes a lot of time and effort since you have to create and test a query before applying it and it’s fairly complex.
This article takes you through a unique approach to consolidate multiple databases in SQL server. It works for both scenarios:
- Two databases in a single SQL server
- Two databases on different SQL server
The good thing is that even if the server resides on different systems, this method will still work as long as they are connected via a network.
We will even include a way to merge database files (MDF) into one SQL server database at the end of the blog.
SO, let us keep reading.
Method to Merge Two Databases in SQL Server (Same Server or Different)
We will be using SQL Server data migration wizard for this task. The main functionality of this tool is to migrate objects from one server to another. And that includes the complete database. You can even choose the same SQL server as source and destination to facilitate database merging on the same server.
The exact instructions to achieve your required merge operation is as follows:
- Download, install and start the migrator wizard on your Windows system.
- Click the Open button.
- Enter the SQL server name and the authentication type.
- Select the database you want to merge to another.
- Click Ok to move to the preview window.
- Here, view all the objects of the selected SQL Server database, then click Export to proceed.
- Select the SQL Server Database and enter the destination SQL server name here.
- To merge SQL database of the same server, enter the same server name as in Step 2.
- To merge SQL databases of different servers, enter the server name of that server (you can connect the machine on a network if the server resides on a different system).
- Select Export to Existing Database and choose the second database that you wish to merge with the previously selected first database.
- Choose the objects that you wish to move to the other database and click the Export button.
- Wait for the process to complete and click OK on the confirmation window.
- Open the destination SQL server to find all the objects of the source database in the destination database.
This concludes the merge operation for two live databases.
The tool does not contain any duplication checker, so you need to apply redundancy check yourself in the merged databases.
Method to Consolidate SQL Server Database Files (MDF)
This method focuses on merging offline SQL Server Database files i.e., MDF files. It will require a SQL server for the execution of this process. Once you have DB files ready on the system running SQL Server, follow the order of steps given below:
- Download, install and run the same Migrator wizard and described in the previous section.
- Click on the Open
- Now, select the Offline mode and browse the MDF file from the system.
- Choose the Scan mode and click the OK
Note: When dealing with database files, the software also offers to recover deleted objects of SQL database.
- When the scanning completes, click the Close
- The wizard will preview all your database objects, click the Export menu button to proceed.
- Select SQL Server Database and provide the server name for the destination SQL server.
- Choose to Create new database option and provide a name for the database that will contain merged SQL database files.
- Finally, click the Export button to start moving the files into the SQL server database.
- Wait for the process to complete and click the OK button on the confirmation window.
With this, we have moved one database file into the server. Now, repeat all the steps to move another database file into the server.
This way we moved both files in a single database, thus effectively merging two SQL server database files together.
The tool does not contain any duplication checker, so you need to apply redundancy check yourself in the merged database files.
Summary
In this article, we went through the steps to merge two databases in SQL server regardless of the source and destination being the same or different. We also looked at the instructions to consolidate the database files into one. This tool also allows you to generate scripts with data in SQL Server. Make sure to download and run the FREE DEMO version of the tool to execute the merge process once. It will help you in deciding whether this wizard is the right choice for you or not.
FAQs
Can you merge two databases?
Yes, we provided the steps for the same.
How do I join tables from two SQL databases?
Use the following template:
SELECT
table_1.*,
table_2.*
FROM [Database_1].[Table_Schema].[Table_Name_1] table_1
JOIN [Database_2].[Table_Schema].[Table_Name_2] table_2 ON table_1.id = table_2.id
Which merge method is used when we try to merge two different tables into one?
You can use MERGE statement to synchronize two tables by specifying the Source and Target table and the Join condition.
