Laden...

MSSQL-DB nach Wiederherstellung: "auto_increment" nicht gesetzt und Primary Key fehlt

Erstellt von Kapkan vor 5 Jahren Letzter Beitrag vor 5 Jahren 2.729 Views
K
Kapkan Themenstarter:in
6 Beiträge seit 2013
vor 5 Jahren
MSSQL-DB nach Wiederherstellung: "auto_increment" nicht gesetzt und Primary Key fehlt

verwendetes Datenbanksystem: <SQL-Express 2017>

Hallo Leute,

ich habe ein großes Problem und zwar hatte ich eine Datenbanksicherung gemacht und habe gestern auf einen anderen Computer wiederhergestellt und müsste feststellen das bei einigen Tabellen die Id kein Primärschlüssel hat und die Eigenschaft "auto_increment" ist auch nicht mehr gesetzt. Könnte ich das wieder setzen ohne die Ids zu verändern? Ich habe das mit den AFTER befehl schon mal ausprobiert das Problem da, ist das er die Id neu generiert und das passt natürlich zu meine ganze Verknüpfungen nicht mehr.

Mfg
Jürgen

286 Beiträge seit 2011
vor 5 Jahren

Auto-Inkrement besagt nur, dass bei einem INSERT automatisch eine unique Id erzeugt wird, auf die bestehenden Ids hat es keine Auswirkung ob es aktiviert ist oder nicht.

2+2=5( (für extrem große Werte von 2)

T
708 Beiträge seit 2008
vor 5 Jahren

Da bin ich ganz anderer Meinung.
AutoIncrement basiert auf einem Feld in einer Shadow-Tabelle. Dieses wird hochgezählt und damit sehr performant der PK für die Tabelle erstellt. Diese schert sich nicht um Daten innerhalb der Tabelle. Deshalb werden Datensätze dann einfach überschrieben, bzw. ruft jeder Insert einen Fehler hervor, dass der Datensatz bereits existiert.

Also ja, setze nach einer Datenübernahme den AutoIncrement Wert auf den PK Deiner Tabelle. Da der PK noch nicht existiert, kannst Du das eigentlich mit
IDENTITY(X,1) machen. Wobei X für den Startwert und die 1 für die Erhöhung steht.

T
2.219 Beiträge seit 2008
vor 5 Jahren

Wenn es sich hier um eine Datenwiederherstellung handelt, habt ihr dann etwas die Tabellen selbst nict mitgesichert?
Also wirklich nur die reinen Daten ohne Tabellen, Keys und Indizies?
Normalerweise sollte in der Datensicherun auch die Tabelle vollständig drin sein um genau solche Probleme nicht zu bekommen.
Bei auto increment sollten die Tabellen auch möglichst leer sein, da es genau wegen doppelten Einträgen zu Problemen kommen kann.
Dies ist übrigens auch ein Grund warum man auto increment nicht mehr verwenden sollte.

Wir nutzen für eindeutige IDs, was der ursprungszwecke von auto increment ist, nur Guid.
Hier ist die Eindeutigkeit in der DB gegeben und man bekommt dann nur Probleme mit doppelten Keys wenn man wirklich doppelte Einträge in die DB inserted.
Bei einer Datenwiederherstellung werden dann nur die ursprünglichen Daten ohne Kollision eingespielt.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

F
10.010 Beiträge seit 2004
vor 5 Jahren

Man kann durchaus im SQL-Server eine Spalte mit Identity anlegen und dann trotzdem ( von Hand ) Daten in die ID Spalte einfügen.
Man muss nur vorher in der selben SqlConnection ein "SET IDENTITY_INSERT [Tablename] ON" machen.

1.696 Beiträge seit 2006
vor 5 Jahren

Eigentlich hast du bei der DB-Sicherung was falsch gemacht, daher ist die Eigenschaft nach Wiederherstellung nicht vorhanden. Um nachträglich zu ändern und dennoch die Werte der bisherigen autoincrement-Spalte beizubehalten, kannst du eine neue Tabelle mit der autoincrement Eigenschaft erstellen, dann wie FZelle sagt, SET IDENTITY_INSERT <neue Tabelle> ON absetzen, dann alle Daten von der alten Tabelle in die neue kopieren.

Ich bin verantwortlich für das, was ich sage, nicht für das, was du verstehst.

**:::

T
433 Beiträge seit 2006
vor 5 Jahren

Wie kann sich denn bei einem normalen Backup/Restore die Tabellenstruktur ändern? ...

Ich würde an deiner Stelle mal dein Backup und Restore anschauen. Das muss ja irgendwie ausserhalb der Norm sein.

Den Increment von einer Tabelle kannst du auch nachträglich mit


DBCC CHECKIDENT('<SCHEMA.TABELLENNAME>')

korrigieren.

Siehe https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017

286 Beiträge seit 2011
vor 5 Jahren

Diese schert sich nicht um Daten innerhalb der Tabelle. Deshalb werden Datensätze dann einfach überschrieben, bzw. ruft jeder Insert einen Fehler hervor, dass der Datensatz bereits existiert.

afaik kannst du im mssql eine Spalte nachträglich als autoincrement definieren und bei einem weiteren INSERT wird stets von der letzten ID ausgegangen.

Habe es auch gerade mal getestet:
->Eine bestehende Tabelle kopiert
-> auto-inkrement für den PK deaktiviert
-> nen bissel mit der tabelle rumgespielt (datensätze gelöscht und einen neuen Datensatz in dem ich den PK per Hand auf letzte Id + 4 gesetzt habe)
-> auto-inkrement wieder eingestellt

-> neuer Datensatz erhält als ID die letzte Id (die ich per Hand angelegt habe) + 1

Ich verstehe Kapkans Problem genau so:
Er hat eine gesicherte Tabelle mit einer Spalte "ID" welche vor der Sicherung der PK mit auto-inkrement war. Nun fehlen in der Tabelle die beiden Eigenschaften PK + auto-inkrement und die Frage war, ob man diese für die betroffene Spalte nun wieder gefahrlos setzen kann ohne dass die Datensätze invalide wären. Und die Antwort darauf ist imho ganz klar: Ja, kann man problemlos machen

Beste Grüße
emuuu

2+2=5( (für extrem große Werte von 2)

T
708 Beiträge seit 2008
vor 5 Jahren

Danke für den Test emuuu. Da habe ich (zumindest in der Vergangenheit) andere Erfahrungen gemacht.
Da hatte ich entweder eine andere Konstellation oder ggf. eine andere SQL Version im Einsatz.

Wir nutzen für eindeutige IDs, was der ursprungszwecke von auto increment ist, nur Guid.

Der Horror einer jeden Tabelle: 32 stellige Primärschlüssel!
Die Sortierung passiert willkürlich, es müssen die Datensätze also laufend "dazwischen" eingefügt werden, was ebenfalls extrem schlecht für die Performance und Festplatte ist.
Mit dieser Sortierung kann niemand etwas anfangen. Daher braucht es mindestens einen Sekundärschlüssel, der die Daten nach einer sinnvollen Reihenfolge ausgeben kann.
Jede Applikation, die die Daten bereitstellen möchte, muss die Sortierung auf den SK ändern, wenn die Daten linear angezeigt werden sollen.

Bin mir ziemlich sicher, dass es performanter wäre, mit LOCK den letzten Datensatz zu holen und den Wert zu inkrementieren (also ein "händisches" autoincrement aber mit LOCK) als eine GUID für den PK zu verwenden.

T
2.219 Beiträge seit 2008
vor 5 Jahren

@trib
Die Performance war mit Guids noch nie ein Problem.
Hier haben wir Tabellen mit mehreren Mio. Datensätzen mit Guid als PK die regelmäßig gelesen und beschrieben werden.
Klar haben wir dann einen sekundären Index, der dann die Reihenfolge über benötigten Spalten entsprechend vorhält.
Das kostet zwar wegen dem zusätzlichen Index mehr Speicherplatz, aber das ist kein Argument gegen eine Guid als PK.
Da der sekundäre Index auch nicht eindeutig ist, wäre dieser als PK nicht nutzbar.

Ich weiß zwar nicht wie deine Erfahrungen im dem Bereich aussehen, aber sowohl unter SQL Server ab 2008 bis zum 2016 haben wir nie Performance Probleme gehabt.
Auch unter PostgreSQL habe ich schon tests mit UUID gefahren, aber hier ist auch die Struktur des Index anders.
Heir hält jeder Index direkt die Row ID, weshalb es keinen unterschied zwischen primären und sekundären Index gibt.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

16.807 Beiträge seit 2008
vor 5 Jahren

GUID kann sich negativ auf die Performance auswirken; muss aber nicht.
Das liegt alleine schon an der Sache, dass eine GUID 16 statt nur 4 Bytes im Index benötigt.

I.d.R. überwiegen aber die Vorteile von Guid, deswegen andere Datenbanken (gerade Not-only SQL-Datenbanken wie MongoDB oder CosmosDB) nur eine Guid zulassen.

W
955 Beiträge seit 2010
vor 5 Jahren

Die Sortierung passiert willkürlich, es müssen die Datensätze also laufend "dazwischen" eingefügt werden, was ebenfalls extrem schlecht für die Performance und Festplatte ist. Niemand zwingt dich diesen Primärschlüssel als clustered Index anzulegen.

K
Kapkan Themenstarter:in
6 Beiträge seit 2013
vor 5 Jahren

Hallo Leute
Danke erstmal für eure Antworten.

Emuu hatte mich richtig verstanden. Warum das so passiert ist weiß ich nicht wir haben ein vollständiges Backup gemacht.

Problem gelöst.

Ich könnte das mit Visual Studio lösen können. Da Visual Studio das irgendwie anders als Management Studio macht. Visual Studio hat mir das wieder auf auto_increment gesetz ohne den Index zu verändern.

Danke nochmal