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?

Improve this question
12
  • 1
    yep: stackoverflow.com/questions/193438/…
    – Mitch Wheat
    Commented Mar 26, 2009 at 14:02
  • is there anything For SqlSever 2008R2 and Sql Azure ?
    – joshua
    Commented Sep 10, 2012 at 10:58
  • 30
    Also not a duplicate as the other question asks for just a table compare not a db
    – mmmmmm
    Commented Nov 28, 2012 at 13:13
  • 2
    How about xSQL's Schema Compare and Data Compare tools. Completely free for SQL Server Express and very reasonably priced for other editions.
    – xSQL Software
    Commented Dec 4, 2013 at 20:30
  • 17
    Can we get a tool to downvote the users who mark as duplicate without bothering to post the original links. At least give users opportunity to distinguish between their question, and existing questions. This is the top ranked google search wrt Comparison tools. It has merit
    – Peter PitLock
    Commented Apr 29, 2014 at 11:19

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)

Improve this answer
18
  • 7
    I've had these sitting on my desktop for years and didn't even know it! Thanks for this tip.
    – Peter
    Commented Mar 2, 2011 at 3:27
  • 4
    I also found that Visual Studio's Data and Schema comparison tool works better than Red-Gates's one (it syncs schema more accurately)
    – Konstantin Tarkus
    Commented Mar 4, 2011 at 19:39
  • 3
    Missing data compare in VS2012? Update from Microsoft here: msdn.microsoft.com/en-us/jj650015
    – Dan Esparza
    Commented Jul 18, 2013 at 19:28
  • 1
    @OrionEdwards this tool is availible in vs Professional as well. have it installed and works great!
    – arik
    Commented Dec 23, 2013 at 11:40
  • 2
    I have had Visual Studio 2013 crash on me multiple times while trying to use the built-in tools to sync data from our Prod database to the Test database. It works good for small batches, but when you try to sync the entire database..... well, good luck. RedGate's tool has worked perfectly for me, being able to handle the heavy lifting to sync the entire DB's data. (And it seems to be faster as well.)
    – Jason Parker
    Commented Jun 10, 2014 at 13:46
144

I like Open DBDiff.

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

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

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]

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.

Improve this answer
4
  • 5
    +1 Adept SQL Diff is a superb piece of software and relatively cheap
    – Steve Weet
    Commented Apr 8, 2009 at 21:56
  • I do agree. It's very fast and reliable at a very convenient cost. We use it at work.
    – Max Rios
    Commented Aug 28, 2013 at 15:19
  • Lol I know - in the Visual Studio tool the button to sync is right next to the generate script button. I haven't clicked either yet but still scared!
    – Simon_Weaver
    Commented Apr 30, 2019 at 6:21
  • This is one of the best tools... but is no longer available and is now unable to connect to some SQL servers.
    – Glen Little
    Commented Jun 12, 2019 at 23:18
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.

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.

Improve this answer
1
  • 1
    I've used DbGhost for 10 years and it's never let me down. The support they provide is second to none
    – penderi
    Commented Feb 21, 2010 at 10:31
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

Improve this answer
1
  • Solid product, used it for a few years. Nice interface and doesn't add install a whole other bunch of junk that you may not want. Price is reasonable (single license for $370)
    – Sameer Alibhai
    Commented Apr 2, 2015 at 15:54
4

Try DBComparer, it's free and fast:

http://dbcomparer.com/

Improve this answer
4
  • 2
    This tool is very iffy. For example, it does a reverse engineer of both databases and then it throws up many tables and views that it claims are different. However, what is actually different is the way the CREATE scripts look. For example, it says a view is different because the one comes up as: CREATE VIEW dbo.ViewName and the other db comes up as CREATE VIEW [dbo].[ViewName]
    – Ozzy
    Commented May 18, 2012 at 9:59
  • Couldn't get this to work at all. Literally took hours to compare a DB, even after deselecting most options that it needs to compare.
    – Peter PitLock
    Commented Apr 29, 2014 at 11:34
  • Doesn't work with SQL Server 2000.
    – Kasim Husaini
    Commented Jan 4, 2017 at 6:21
  • 1
    Not comparing data
    – angularconsulting.au
    Commented Mar 7, 2017 at 22:13
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.

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.

Improve this answer
4
  • 17
    Please add "a full disclosure". (Devart makes dbForge.)
    – user166390
    Commented Feb 11, 2012 at 1:36
  • 1
    My choice is Devart's SQL Data and Schema Compare tools. Red Gate products are good too, but Devart's are much cheaper and also very professional. Thank you guys!
    – user1773378
    Commented May 22, 2013 at 12:22
  • I have used this and it worked great for me. So far I am a fan. It's not free at all, but rather, a free trial.
    – Camille Sévigny
    Commented Jan 29, 2016 at 18:11
  • can attest, really good.
    – anar khalilov
    Commented Jan 3, 2018 at 7:11
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.

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

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)

Improve this answer
1

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

Improve this answer
1

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