I have a table with a identity primary key column. Its last value is 5228491220 using IDENT_CURRENT.

I make a full backup of the database, restore it on a second server. (We backup and restore daily from one server to the other one.) Immediately I pull IDENT_CURRENT from this table and the value is 5228501279.

That is, the restore jumps the last value by more than 10000.

I can't make sense of it. Why would the last value of this identity column not be carried properly through the backup?

More practically, how do I make sure the value carries over properly?

  • 1
    If you're on SQL Server 2012+, this may explain it. – Erik Darling Oct 4 '17 at 1:52
  • @sp_BlitzErik hits it - this is 100% by design. It really shouldn't matter, if you're OCD I'd suggest not using identity. – Sean Gallardy - Retired User Oct 4 '17 at 1:56
  • yep, you nailed it, this column is a bigint, exactly the gap we observe. You guys are good. thanks guys! – fredito Oct 4 '17 at 2:18

If you're on SQL Server 2012 (or newer), you may be running into a planned change, where identity values are retrieved in batches, and the next ID value may be incremented after a shutdown, or a restore.

Quick summary: SQL grabs its IDENTITY values in batches, and if it's not sure what the last ID used was, it will increment the next IDENTITY value by 10 (for tinyint) to 10000 (for bigint) when starting back up.

(Compiled from comments)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.