What is best tool to compare two SQL Server databases (schema and data)?

Question

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?

yep: http://stackoverflow.com/questions/193438/tool-to-compare-sql-server-tables — Mitch Wheat, Mar 26 '09 at 14:02
Also not a duplicate as the other question asks for just a table compare not a db — mmmmmm, Nov 28 '12 at 13:13
How about xSQL's [Schema Compare](http://www.xsql.com/products/sql_server_schema_compare/) and [Data Compare](http://www.xsql.com/products/sql_server_data_compare/) tools. Completely **free for SQL Server Express** and very reasonably priced for other editions. — xSQL Software, Dec 04 '13 at 20:30
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, Apr 29 '14 at 11:19
No more answers are being allowed, but I found that winmerge worked great and was free. — Mike Lyons, Dec 04 '14 at 16:36
Use Master Data extension for visual studio ... https://visualstudiogallery.msdn.microsoft.com/530c546a-fdd3-4589-b2b2-e3c4aab13fba — Moumit, Sep 16 '16 at 13:47
Try TOAD for SQL Server. It's a free trial and the compare schema utility is pretty good. Very simple and it generates a synchronisation script to merge changes from one database to another. To be honest I've tried TableDiff that comes with SQL Server 2005, 2008, etc, and it's horrid. — Fandango68, Nov 30 '16 at 03:17
No idea why this is marked as duplicated, as the other question is specific about free tool and this not.. — Arek, Jul 19 '17 at 07:26
Its actually funny to see that a duplicate has more views and points rather than the first one — Mr.J, Jun 07 '18 at 03:25
Gotta love stackoverflow. Marked as duplicate to a question that does NOT ask about comparing data. Slowly moderating the site to death, and there is no recourse for obvious abuses like this. — Douglas Gaskell, Dec 07 '18 at 18:46
@DouglasGaskell Yes, it is clear the other question is about comparing tables, while mine is about comparing databases. I am sad to see what happens here also. I have voted to reopen this question. — Tomasz Smykowski, Dec 09 '18 at 09:00

score 235 · Answer 1 · edited Jun 20 '20 at 09:12

235

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

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

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

_{(source: spaanjaars.com)}

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 26 '09 at 09:34

Konstantin Tarkus

35,208
14
127
117

6

I've had these sitting on my desktop for years and didn't even know it! Thanks for this tip. – Peter Mar 02 '11 at 03: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 Mar 04 '11 at 19:39
3

Missing data compare in VS2012? Update from Microsoft here: http://msdn.microsoft.com/en-us/jj650015 – Dan Esparza Jul 18 '13 at 19:28
Microsoft's tooling is only available in the Premium (expensive) and Ultimate (ridiculously expensive) editions of visual studio – Orion Edwards Dec 16 '13 at 19:41
1

@OrionEdwards this tool is availible in vs Professional as well. have it installed and works great! – arik Dec 23 '13 at 11:40
Visual Studio tool works great as long as you don't try to make comparison betwwen different versions of SQL Server. In this case, it will just refuse to compare, although comparison and update is actually possible – MadBender Feb 14 '14 at 13:21
1

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.) – ClearCloud8 Jun 10 '14 at 13:46
1

I used Redgate before. It's so useful but at first it's so expensive and the most important thing about that is generating so many unwanted script specially when you change schema for example add column between other columns in table. If you don't hesitate about clarify and in yourself control scripts and you have enough money then Redgate is good tools. But for me the VSSQLTools is good and proper. Thanks and +1 to Konstantin and Thanks again and +1 to @Dan Esparza for data comparing link for VS2012. – QMaster Aug 08 '14 at 23:07
I forgot about that feature! I got too used to RedGate at work. Now I can use this VS feature when working on my personal stuff. Cheers! – hatsrumandcode Apr 08 '15 at 19:19
Awesome! Thank you - I didn't even know it was there! The tool is almost effortless and the ability to copy data (backwards) to a previous version is a real plus, since I couldn't seem to import the data using import with the versions I was using. Thanks again! – HumbleBeginnings Apr 26 '15 at 18:31
The data comparison functionality in VS is very powerful but it lacks a major feature. You can select which tables to compare, or drill down on the columns for each table. That's very useful when you have a big database with a lot of moving parts, but you only want to export a specific subset. Also the columns are different on live, so naturally you want to ignore that. The problem is you can't save those filters. So imagine what a nightmare would be to do this on a 100 tables datasource. Not to mention error-prone... – Cristian Oct 16 '15 at 00:04
@Christian you can save the data or schema comparison settings into a file, then instead of starting a new comparison over again, you just open a corresponding `.dcmp` or `.scmp` file. – Konstantin Tarkus Oct 16 '15 at 17:19
This does not support SQL 2000 I believe. Any idea of a tool that support SQL Server 2000 data and schema comparison? – Bat_Programmer Nov 09 '15 at 01:49
Thank you, never knew about this feature in VS, folks **Note: Two types: 1. New Data Comparison 2. New Schema Comparison**, To start comparing , Go to Tools ==> SQL Server , i was trying [this](http://stackoverflow.com/a/175450/2218697) sql query to check the source and target database with multiple select statements, is there not a `sql query` to compare complete database ? – Shaiju T Dec 03 '15 at 11:24
Works like a charm in VS2017. Thanks. – Nurhak Kaya Jun 06 '17 at 14:06
1

Wow! First time saw, _the difference_ between _schemas_, **visually**, Thanks a bunch Konstantin! – Irfan Dec 21 '18 at 05:23
Is this not in SSMS? That's odd if it isn't. – Simon_Weaver Apr 30 '19 at 06:20

score 132 · Answer 2 · edited May 15 '20 at 14:58

132

I like Open DBDiff.

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

edited May 15 '20 at 14:58

James Skemp

7,386
9
58
95

answered Mar 26 '09 at 14:19

4

Simple, Easy and it work. My choice to replicate differencies from one database to another. – eka808 Apr 11 '12 at 11:49
1

I have been searching for a tool like this for years. I have tested plenty free tools against two of my databases, and this was the ONLY free tool generating the a proper synchronization upgrade script. Thanks for pointing it out! – Julio Nobre Oct 03 '12 at 17:53
38

This does not compare DATA – Daveo Mar 27 '13 at 04: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 Apr 29 '14 at 10:42
2

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 Dec 23 '14 at 13:23
2

It doesn't work ok in sql server 2014 – sotn Jul 21 '15 at 15:02
@Daveo select a table and in the right navigation pan you will see the compare data option being enabled. – ashubuntu Nov 21 '16 at 11:07
It doesn't compare permissions on objects :-( – viking_grll Jan 31 '19 at 12:58
it worked pretty good for me on SQL Server 2014 and 2017 – Daniël Tulp Mar 04 '20 at 13:36

score 86 · Accepted Answer · answered Mar 26 '09 at 09:31

86

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

answered Mar 26 '09 at 09:31

Joakim Backman

1,825
13
12

SQLCompare is very good. – Johnno Nolan Mar 26 '09 at 09:52
1

Yes - SQL Compare and SQL Data Compare are absolutely essential to our business - good stuff. – Mark Brittingham Mar 26 '09 at 12:23
davidsleeps, have you raised this with Red Gate's support engineers? We'd love to diagnose any issues you might have with SQL Compare (I'm SQL Compare's product manager). – David Atkinson Mar 28 '10 at 23:14
It may be "expensive" (a few hundred dollars), but when you compare that against what you'd pay a developer to spend a day search, learning, and working around the shortcomings in a free tool, I think it's a well worthwhile investment. – SqlRyan Aug 10 '10 at 16:39
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 Nov 11 '10 at 07: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 Aug 29 '12 at 08: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. – May 23 '13 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 Sep 11 '13 at 15:49

Sean Cleaver · Answer 4 · 2011-07-15T09:39:15.723

72

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]

edited Jul 15 '11 at 09:39

answered Jul 11 '11 at 14:08

Sean Cleaver

785
5
4

4

It's a little rough about the edges compared to some of the pay offerings, but it does work, and a good bit cheaper (as it's free) +1. – Feb 11 '12 at 01:35
8

Data Sync Studio doesn't seem to be free anymore. – Adriano Machado Nov 09 '12 at 21:29
Has all the features you need making it the best free solution IMO (data sync may not be free but I prefer to backup/restore in the instance I need data) – JDandChips Mar 20 '13 at 16:49
1

This does not work with sqlite as an FYI to anyone – LyricalPanda May 19 '14 at 19:59
Good job @SeanCleaver, Some issues with tables with non-primary key as clustered index otherwise overall 9/10. – Kasim Husaini Jan 03 '17 at 05:40
Your data compare works brilliantly with SQL server. Not so much luck with the schema compare because the column order can't be ignored but the data compare is what I was really looking for and it worked perfectly with different order columns. – Graymatter Apr 26 '18 at 00:59
free now and supports ***SQL Server 2016*** ? – Kiquenet Apr 26 '18 at 07:38
6

https://www.simego.com/install/sql-tools not found – Kiquenet Apr 26 '18 at 07:39
2

I got the software from https://download.cnet.com/SQL-Admin-Studio-64-Bit/3000-10254_4-75700837.html – Maksymilian Mulawa Jun 08 '20 at 11:52

score 13 · Answer 5 · edited Oct 26 '18 at 23:33

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.

edited Oct 26 '18 at 23:33

Lee Taylor

6,091
14
26
43

answered Mar 26 '09 at 13:57

Christopher Klein

2,782
4
37
58

5

+1 Adept SQL Diff is a superb piece of software and relatively cheap – Steve Weet Apr 08 '09 at 21:56
I do agree. It's very fast and reliable at a very convenient cost. We use it at work. – Maximiliano Rios Aug 28 '13 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 Apr 30 '19 at 06: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 Jun 12 '19 at 23:18

score 8 · Answer 6 · answered Nov 28 '12 at 09:02

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.

answered Nov 28 '12 at 09:02

Milind Thakkar

980
2
13
20

score 6 · Answer 7 · answered Mar 26 '09 at 12:17

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.

answered Mar 26 '09 at 12:17

Bluephlame

3,733
4
31
50

1

I've used DbGhost for 10 years and it's never let me down. The support they provide is second to none – penderi Feb 21 '10 at 10:31

score 4 · Answer 8 · answered Mar 26 '09 at 09:38

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

answered Mar 26 '09 at 09:38

JonoW

12,861
3
31
31

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 Apr 02 '15 at 15:54

score 4 · Answer 9 · answered Jan 18 '12 at 09:59

4

Try DBComparer, it's free and fast:

http://dbcomparer.com/

answered Jan 18 '12 at 09:59

Melad

716
10
14

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 May 18 '12 at 09: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 Apr 29 '14 at 11:34
Doesn't work with SQL Server 2000. – Kasim Husaini Jan 04 '17 at 06:21
1

Not comparing data – angularrocks.com Mar 07 '17 at 22:13

score 3 · Answer 10 · answered Apr 09 '10 at 11:24

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.

answered Apr 09 '10 at 11:24

Devart

110,991
22
156
173

15

Please add "a full disclosure". (Devart makes dbForge.) – Feb 11 '12 at 01: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! – May 22 '13 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 Jan 29 '16 at 18:11
can attest, really good. – anar khalilov Jan 03 '18 at 07:11

score 3 · Answer 11 · answered Apr 08 '09 at 21:52

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.

score 2 · Answer 12 · answered Mar 26 '09 at 10:15

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.

answered Mar 26 '09 at 10:15

Santiago Cepas

3,912
2
23
31

1

dead link. please provide another link. – toha Jul 30 '18 at 08:07
This seems to be it: https://github.com/OpenDBDiff/OpenDBDiff – Glen Little Jun 13 '19 at 00:26

score 1 · Answer 13 · answered Mar 24 '10 at 09:31

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)

score 1 · Answer 14 · answered Mar 26 '09 at 09:36

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

score 0 · Answer 15 · answered May 15 '12 at 14:17

0

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

answered May 15 '12 at 14:17

Senthil Kumar B

926
6
13

What is best tool to compare two SQL Server databases (schema and data)?

15 Answers15

Linked