Re: Identity column re-using existing numbers?

I have seen this before (primarily with backups taken on a replicated
database) How did you do the migration ?

What I have seen is case(s) where records are replicated and applied to a
target database on a remote machine but because of how the write on the
remote side is performed the identity column does not get incremented.
Obviously the replicated record contains the identity column value so
either the write somehow bypasses identity column checks (maybe by turning
off a constraint or the identity column attribute is turned off while
replication is being applied. I don't know the exact mechanism but
conceptually something like that happens to stop the database incrementing
the identity column. The counter for the identity column remains at
whatever it was when it was "turned off".

Later when tables with identity columns are restored from a backup taken on
the target system the identity column start value has to be set to the
current maximum + 1 to avoid duplicate key errors.


On Wed, Dec 5, 2018 at 8:18 AM Matt Olson <Matt.Olson@xxxxxxxx> wrote:

We migrated some of our tables to another partition for testing and we
have noticed that their identity columns which should generate the next
available number is infact generating an existing number and failing the
primary key check. Has anyone see this before?

There some easy command to restart identity value to the next available
number?

We are mystified why this didn't come over during the restore of that
file...

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com