SYMPTOMS
The "current value" for an IDENTITY property for a column can become out of
sync if the server is shut down without a checkpoint. If the identity
column is a primary key, you will encounter the following error message
when an INSERT statement runs:
Msg 2627
Violation of %s constraint '%.*s': Attempt to insert duplicate
key in object '%.*s'.
If the column has a unique index, you will receive the following error
message:
Msg 2601
Attempt to insert duplicate key row in object '%.*x' with unique
index '%.*s'
One of the more common symptoms occurs when you execute DUMP and LOAD
Transact-SQL statements. This happens because the server automatically
inserts rows into msdb..sysbackuphistory and msdb..sysrestorehistory when
either a DUMP or LOAD statement executes. These tables use an identity
column as a primary key.
When a DUMP or LOAD command runs and encounters this problem, the DUMP or
LOAD operation will be successful, but you receive the following error
messages. Note that this is an example of the messages you would receive
when the LOAD statement runs.
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'pk_restore_id': Attempt
to insert duplicate key in object 'sysrestorehistory'.
Msg 3009, Level 16, State 4
Could not insert a backup or restore history/detail record in
msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate
a problem with the MSDB database. DUMP/LOAD was still successful.
The error log will contain the following message:
A history record could not be written to msdb.sysbackuphistory
or msdb.sysrestorehistory