Visual Studio 2013 has Sql Server Data Tools built in. (Pro, Premium and Ultimate) We use it to compare and sync databases.
The first time I started the data compare it did not work. When comparing the data between databases no tables could be selected.
The MSDN article about the SQL Server Data Compare explains why:
Tables and views must meet two criteria to appear in the listing. First, the schemas of the objects must match between the source and target databases. Second, only tables and views that have a primary key, a unique key, a unique index, or a unique constraint appear in the list. If no tables or views meet both criteria, the list will be empty.
Since my database was created using the Sql Server Export Data feature no indexes or keys were created. Makes sense as it only copies the data (hence the name)
Schema compare
To correct missing keys and indexes or other object differences, do a schema compare. After the compare, click Update Target to create the missing indexes and keys.
To save the changes for deployment use Generate Script.
Data compare
Data compare will now show data differences for the tables. There are none, because no changes were made after the Sql Server Export Data had run.
When there are differences: as with schema compare you can Update Target for immediate sync or use Generate Script to save the changes for deployment.
Final thoughts
The data tools are a good option when you can connect to both environments with Visual Studio. You can create scripts for the changes and use that for your deployments.
For the initial setup with base data Microsoft has the bacpac files. You can create them with the commandline tool sqlpackage.exe. (download)
Hello, where do I find this features? I mean in the menu… thanks
That is in the menu
TOOLS > SQL Server > New Data Comparison …
I don’t have SQL Server in my tools menu. How can I install the SSDT in VS2013?