Synchronizing databases is a common task for many database administrators. When multiple copies of a database exist, it is often necessary to keep them synchronized with each other. This can be accomplished using various tools and methods, but in this article, we will focus on using the Synchronize Database Wizard in Microsoft SQL Server Management Studio (SSMS).
Synchronize Database
The Synchronize Database Wizard is a tool that can be used to compare and synchronize the schema and data of two different databases. It can also be used to generate scripts that can be used to manually synchronize the databases.
In this article, we will walk through the steps necessary to use the wizard to synchronize two databases.
We will assume that you have two databases that you would like to keep synchronized. For the purposes of this article, we will call these databases DB1 and DB2.
Step 1: Open SSMS and connect to the instance that contains the databases you would like to synchronize.
Step 2: In the Object Explorer pane, expand the node for the instance, and then expand the Databases node.
Step 3: Right-click on DB1 and select Tasks, then click on Generate Scripts.
Step 4: In the Choose Objects page of the wizard, select Script all objects in the selected database and click next.
Step 5: On the Set Scripting Options page, select save scripts to a specific location and choose a location for the script. In the File name text box, enter a name for the script.
Step 6: Make sure that the Script for server version checkbox is selected and that the correct server version is selected from the list.
Step 7: Under the Types of data to the script section, select Schema and data and click next.
Step 8: Review the summary page and click finish generating the script.
Step 9: Repeat steps 3 through 8 for DB2.
Step 10: Now that you have a schema and data script for each database, you will need to modify the scripts so that they can be used to synchronize the databases.
To do this, open the scripts in a text editor such as Notepad++ or Microsoft Word.
Step 11: In the script for DB1, find the line that says CREATE DATABASE and delete it.
Step 12: In the script for DB2, find the line that says USE master and delete it.
Step 13: Save the scripts and close the text editor.
Step 14: Now that the scripts have been modified, you will need to execute them on each database. To do this, open SSMS and connect to the instance that contains the databases.
Step 15: In the Object Explorer pane, expand the node for the instance, and then expand the Databases node.
Step 16: Right-click on DB1 and select New Query.
Step 17: In the query window, open the script for DB1 and click the Execute button.
Step 18: Repeat steps 16 and 17 for DB2.
Step 19: Now that the scripts have been executed, you will need to synchronize the databases using the Synchronize Database Wizard.
To do this, right-click on DB1 and select Tasks, then click on Synchronize Database.
Step 20: In the Choose Data Sources page of the wizard, select DB1 and DB2 as the source and destination databases, respectively. Click Next to continue.
Step 21: On the Choose Options page, select Generate SQL Server Agent Jobs and click Next.
Step 22: On the Set Options page, select the options that you would like to use and click Next.
Step 23: On the Choose Scripting Options page, select Save scripts to a specific location and choose a location for the scripts. In the File name text box, enter a name for the script.
Step 24: Make sure that the Script for server version checkbox is selected and that the correct server version is selected from the list.
Step 25: Click Finish to generate the script.
Conclusion:
In this article, we have walked through the steps necessary to use the Synchronize Database Wizard to synchronize two databases. We have also shown how to modify the scripts generated by the wizard so that they can be used to manually synchronize the databases.
