228

I would like to compare two SQL Server databases including schema (table structure) and data in tables too. What is best tool to do this?

CC BY-SA 2.5
Improve this question
12

15 Answers 15

Reset to default
251

I use schema and data comparison functionality built into the latest version Microsoft Visual Studio Community Edition (Free) or Professional / Premium / Ultimate edition. Works like a charm!

img

http://channel9.msdn.com/Events/Visual-Studio/Launch-2013/VS108

Red-Gate's SQL data comparison tool is my second alternative:

img
(source: spaanjaars.com)

CC BY-SA 4.0
Improve this answer
18
144

I like Open DBDiff.

While not the most complete tool, it works great, it's free, and it's very easy to use.

CC BY-SA 4.0
Improve this answer
9
  • 4
    Simple, Easy and it work. My choice to replicate differencies from one database to another.
    – eka808
    Commented Apr 11, 2012 at 11:49
  • 41
    This does not compare DATA
    – Daveo
    Commented Mar 27, 2013 at 4:58
  • 4
    This is the worst database comparison tool I have come across. It does not save the preferences correctly, it often bombs out. It does not save the SQL instances you've used and then scans the network everytime for all PCs that it then lists - thus you have to copy and paste the instances every time and sit and wait until the network has been scanned before you can use it. This will probably work on very small databases but i couldn't get it to work once and a larger db. Its free, but so is malaria.
    – Peter PitLock
    Commented Apr 29, 2014 at 10:42
  • 3
    Note that it CAN compare data, but only for single tables. I personally found this useful, but if you need to diff many tables or large tables it probably is not sufficient.
    – Thomas N
    Commented Dec 23, 2014 at 13:23
  • 2
    It doesn't work ok in sql server 2014
    – yakya
    Commented Jul 21, 2015 at 15:02
90

I am using Red-Gate's software: http://www.red-gate.com

CC BY-SA 2.5
Improve this answer
8
  • 1
    Yes - SQL Compare and SQL Data Compare are absolutely essential to our business - good stuff.
    – Mark Brittingham
    Commented Mar 26, 2009 at 12:23
  • 13
    Expensive? $300 for something that can save you dozens of hours per year. Even at the lowest global developer rates this is a bargain.
    – Beep beep
    Commented Nov 11, 2010 at 7:32
  • 1
    SQL Compare has become a much better product over the years but still not the best one. I tried many of these tools over the last 10 years and the best one is SQL Delta with its superbly accurate script generator, the second is AdeptSql (only because its data compare tool does not work properly when there is a lot of referential integ. constraints), red gate is a distant 3rd along with many other tools
    – Kemal Erdogan
    Commented Aug 29, 2012 at 8:16
  • 1
    The tool is really good - but as it was told earlier very expensive. I've choosen the alternative - dbForge Data Compare for SQL Server from Devart. The functionality is almost the same. It suits all my needs and is much cheaper.
    – user1773378
    Commented May 23, 2013 at 11:03
  • 3
    As mentioned in the answer this question duplicates, this functionality is built into SQL Server as TableDiff.exe. It's worth trying the built in tools first before paying for a licences.
    – Chip McCormick
    Commented Sep 11, 2013 at 15:49
76

SQL Admin Studio from http://www.simego.com/Products/SQL-Admin-Studio is now free, lets you manage your SQL Database, SQL Compare and Synchronise, Data Compare and Synchronise and much more. Also supports SQL Azure and some MySQL Support too.

[UPDATE: Yes I am the Author of the above program, as it's now Free I just wanted to Share it with the community]

CC BY-SA 3.0
Improve this answer
10
13

I'm partial to AdeptSQL. It's clean and intuitive and it DOESN'T have the one feature that scares the hell out of me on a lot of similar programs. One giant button that it you push it will automatically synchronize EVERYTHING without so much as a by-your-leave. If you want to sync the changes you have to do it yourself and I like that.

CC BY-SA 4.0
Improve this answer
4
8

There is one tool with source code available at http://www.codeproject.com/Articles/205011/SQL-Server-Database-Comparison-Tool

That should give flexibility as code is available.

CC BY-SA 3.0
Improve this answer
0
6

dbghost is the best i have used to date. one of the best features i have seen is that it will generate SQL code to go between versions of a database based on the SQL you keep in source control, as well as a database. simple and easy to use.

CC BY-SA 2.5
Improve this answer
1
4

I've used SQL Delta before (http://www.sqldelta.com/), it's really good. Not free however, not sure how prices compare to Red-Gates

CC BY-SA 2.5
Improve this answer
1
4

Try DBComparer, it's free and fast:

http://dbcomparer.com/

CC BY-SA 3.0
Improve this answer
4
3

Database Workbench can made it too

http://www.upscene.com/products.dbw.index.php

Cross database development

Use the Schema Compare and Migration Tools to compare testing and deployed databases, migrate existing databases to different database systems.

you can also made it with database Comparer

http://www.clevercomponents.com/products/dbcomparer/dbcomparer.asp

I use it for Firebird and it works well.

CC BY-SA 2.5
Improve this answer
3

Try dbForge Data Compare for SQL Server. It can compare and sync any databases, even very large ones. Quick, easy, always delivers a correct result. Try it on your database and comment upon the product.

We can recommend you a reliable SQL comparison tool that offer 3 time’s faster comparison and synchronization of table data in your SQL Server databases. It's dbForge Data Compare for SQL Server and dbForge Schema Compare for SQL Server

Main advantages:

  • Speedier comparison and synchronization of large databases
  • Support of native SQL Server backups
  • Custom mapping of tables, columns, and schemas
  • Multiple options to tune your comparison and synchronization
  • Generating comparison and synchronization reports

Plus free 30-day trial and risk-free purchase with 30-day money back guarantee.

CC BY-SA 2.5
Improve this answer
4
2

I've used Red Gate's tools and they are superb. However, if you can't spend any money you could try Open DBDiff to compare schemas.

CC BY-SA 2.5
Improve this answer
2
1

We are using an inhouse developed solution that is basicly a procedure with arguments of what you want included in the comparision (SP's, Full SP code, table structure, defaults, indices, triggers.. etc)

Depending on your needs and budget, it might be a good way to go for you as well.

It is quite easily developed as well, then we just redirect output of procedure to textfiles and do text comparisions between the files.

One good thing about it is that its possible to save the output in source control.

/B

CC BY-SA 2.5
Improve this answer
0
1

I would definitely go with AdeptSQL if you're using MSSQL. It's the least good looking but the most talented db compare tool amongst the ones I've tried. It can compare both the structure and the data. It tells you which tables exist on one db but does not exist on the other, compares the structure and data of the common ones and it can produce the script to synchronize the two. It's not free but has a 30 day trial (as far as I can remember)

CC BY-SA 2.5
Improve this answer
1

I tried OpenDiff Tool . Great tool that is free and easy to use .

CC BY-SA 3.0
Improve this answer
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.