Tabellen in SQL Server vergleichen – Tablediff.exe

2010-08-13 | Toni Pohl

Arbeiten Sie viel mit SQL-Datenbanken? Dann kennen Sie (oder hatten Sie) sicher die Anforderung, Daten aus einer Datenbank (gescriptet) in eine andere Datenbank zu transferieren und dabei nur die Änderungen zu übertragen.

Das lässt sich mit mehreren Methoden bewerkstelligen, je nach Anforderung, Know How und technischer Umgebung – vom SSIS Import-Tool über selbst scripten bis hin zu kostenpflichtigen Tools und Backup und Restore oder DB-Mirroring (dazu später mal mehr in einem eigenen Artikel).

Ich hatte vor kurzem einen solchen Fall. Die Quell-Datenbank ist viele GB groß und es sollten nur Inhalte aus einer Tabelle in eine andere Test-Datenbank übertragen werden. In der Quell-Tabelle wurden Datensätze aktualisiert, gelöscht und hinzugefügt. In der Ziel-Tabelle in einer zweiten Datenbank sind manche Datensätze bereits vorhanden und sollen durch die Quell-Tabelle aktualisiert werden.

Und das Ganze Aktualisieren (weils ja eine Testdatenbank ist, die sich immer wieder ändert) soll oftmals durchgeführt werden können. Also am besten irgendwie scripten…

tablediff_db_script

Fesch. Wie sag ichs meinem Computer – sprich SQL Server?

Genau dafür gibt es seit SQL Server 2005 ein kleines Kommandozeilen-Tool – das allerdings wenig bekannt (wie das mit vielen Command Line-Tools so der Fall ist) und sehr praktisch ist:

TableDiff

“The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.”

Das klingt mal interessant. Und TableDiff macht auch genau das: Es vergleicht Tabellen und kann auch ein Script mit den Unterschieden der Tabelleninhalte erstellen um die Änderungen auf einem Zielsystem sofort ausführen zu können. Es “synchronisiert” allerdings nicht die Unterschiede aus beiden Tabellen, sondern nur in eine Richtung – wie in der Grafik oben.

In meiner Anforderung ist TableDiff super-fein, denn das kleine Script (im Vergleich zur mehreren GB großen Datenbank) kann auch sehr rasch erzeugt und auf das Zielsystem transportiert und ausgeführt werden, ohne irgendwelche erforderlichen Verbindungen oder Konfigurationen – also “straight forward”.

Also, wo findet man TableDiff.exe? Bei SQL Server 2008 hier:

C:\Program Files\Microsoft SQL Server\100\COM

tablediff_tool

Der Pfad zu TableDiff.exe in SQL Server 2005 lautet übrigens “Program Files\Microsoft SQL Server\90\COM”.

Wie funktioniert es und wie sieht der Aufruf aus?

tablediff.exe -sourceserver server1 -sourcedatabase db1 -sourcetable table1 -destinationserver server1 -destinationdatabase db2 -destinationtable table2

Hinweis: Um explizite Credentials zum SQL Server anzugeben sind diese Parameter erforderlich:

-sourceuser <SourceLogin> -sourcepassword <SourcePassword>

bzw.

-destinationuser <DestinationLogin> -destinationpassword <DestinationPassword>

Hinweis: “To compare tables, you need SELECT ALL permissions on the table objects being compared.” und “..To use the -et option, you must be a member of the db_owner fixed database role…”
D.h. am besten einen dbowner o.ä. für das Tool verwenden, dann gibts keinerlei Einschränkungen.

Wenn die beiden Tabellen-Schemata nicht übereinstimmen, folgt ein Hinweis:

Table [db1].[dbo].[table1] on SERVER1 and Table [db2].[dbo].[table2] on SERVER1 have different schemas and cannot be compared.

Eine weitere Voraussetzung: Die Tabellen müssen eine eindeutige ID-Spalte besitzen:

The replication table difference tool requires the comparison tables/views to have either a primary key, identity, rowguid or unique key column.

So, nun zu meinem Fall-Beispiel:

Ich habe auf der SQL-Maschine “DAISY” zwei Datenbanken “db1” und “db2” und hier jeweils eine Tabelle “WEB_Node”. Die Änderungen (Inhalte) von “WEB_Node” sollen aus “db1” in “db2” transportiert werden – hier sind Webinhalte gespeichert, die in die Testdatenbank transferiert werden sollen.

So sieht mein kleines Script C:\Temp\SQL\doit.cmd aus:

tablediff_cmd

cd "C:\Program Files\Microsoft SQL Server\100\COM"
tablediff -sourceserver "DAISY" -sourcedatabase "db1" -sourcetable "WEB_Node" -destinationserver "DAISY" -destinationdatabase "db2" -destinationtable "WEB_Node" -et Diff -f C:\Temp\SQL\diff.sql
pause

Damit wir auch sehen, WAS die Unterschiede sind, fügen wir zum Statement “-et Diff” hinzu – damit werden die Unterschiede in eine temporäre Tabelle “Diff” geschrieben und können daraus ausgegeben werden.

Und noch besser: -f <pfad><sqlfile> erzeugt ein T-SQL-File, das die Änderungen scriptet!
Damit können die Änderungen ganz leicht am Zielsystem “ausgeführt” werden – voila!

Auch ganz praktisch: Um mal rasch einen Überblick zu erhalten, sind die Parameter -c -q gut, z.B.:

Table [db1].[dbo].[WEB_Node] on DAISY and Table [db2].[dbo].[WEB_Node] on DAISY
have different row counts.
Table [db1].[dbo].[WEB_Node] on DAISY has 123 rows.
Table [db2].[dbo].[WEB_Node] on DAISY has 101 rows.
The requested operation took 0,0950095 seconds.

So, lassen wir es mal (ohne –c –q) laufen:

tablediff_compare

Naja, da sind schon einige Unterschiede vorhanden.
Das erzeugte File diff.sql sieht dann in meinem Fall so aus:

tablediff_diff

Das File ist laang, am Ende folgen dann die neuen Datensätze (INSERTS).

tablediff_diff_end

Achja, eines sollte ich noch im Script anpassen: Dass die Änderungen in die “richtige” Datenbank geschrieben werden – bei mir in “db2” – der Hinweis steht zwar im Script, aber nur als Kommentar. Also am Beginn ergänzen:

use db2
go

tablediff_diff_target_db

So, F5 (Run) drücken – here we go.

Voller Spannung erwarte ich, dass SQL Server nun die ganze Arbeit erledigt … FAIL!

tablediff_diff_error

Es gibt einen “Converting error” beim Umwandeln von Datentypen.

Erste Analyse: Das erste INSERT Command will in ein Feld vom Typ smalldatetime einen Datumswert als String N'Null' (also kein Datum vorhanden) einfügen…

Dass das nicht klappen kann erscheint logisch. Also im Script mit Suchen und Ersetzen workaround-en:
Ersetze alle Vorkommen von N‘NULL’ durch NULL:

tablediff_diff_workaround

Alles ersetzen.

Achja und noch ein zweiter Stolperstein: Das verwendete Datumsformat N'2010-08-12 10:38:00' war auf meinem SQL Ziel-System auch noch ein Problem. Also auch zu Beginn das Datumsformat setzen:

set dateformat ymd;

tablediff_diff_workaround2

RUN!
Jipee, es funktioniert jetzt brav, frei nach dem Motto “Kaum macht mans richtig, gehts…”.

Noch ein Tipp: Der zusätzlichen Parameter –strict kann auch bei der Fehleranalyse helfen (Source and destination schema are strictly compared).

Das Scripten eines NULL-Datum ist mein einziger Wehrmutstropfen am coolen tablediff-Tool: Einen Parameter zum Ändern des N’NULL’ Verhaltens habe ich bislang nicht gefunden. Vielleicht hat ja ein Leser einen Tipp?

Nun gut, aber der Workaround mit Search & Replace tuts auch – wenn man es weiß. ;-)

Noch ein Tipp zum Vergleichen von GROSSEN Tabellen: Der Parameter -bf <number_of_statements> schränkt die Anzahl der Befehle auf die angegebene Anzahl ein und erzeugt danach ein neues, weiteres File. Damit sind große Update-Scripts durchführbar und bearbeitbar.

Weitere Links zu tablediff:

Meine Empfehlung:

TableDiff ist ein äußerst hilfreiches Tool zum automatisierten (bzw. mit dem Search & Replace Workaround halbautomatisierten) Abgleich von Tabellen und kann DB-Admins viel Zeit und Arbeit abnehmen!



Categories: Microsoft, Developer, SQL Server, Tools

Source: https://blog.atwork.at/post/Tabellen-in-SQL-Server-vergleichen-e28093-Tablediffexe