Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: DB restore requires Identity reseed?

    We have ran into this problem a few times. Restoring a DB to a new server and all of the identity seed values are messed up requiring us to run something like:

    DBCC CHECKIDENT (foo, RESEED, 1)

    It's not that big of a deal, but if there is something I can do to prevent it from happening for every restore it would be nice.

    We're running this version of SQL 2008 Standard:
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Messed up how?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Violation of PRIMARY KEY constraint 'PK_Foo'. Cannot insert duplicate key in object 'dbo.Foo'. The statement has been terminated.

    Probably reset to 1.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A database restore should never do that. The restored tables should have the same seed value as they had in the original database at the time the backup occurred.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I get the feeling that we don't have all of the pieces to this puzzle. If you restore a whole database (just backup and restore), then I've never seen this kind of problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    I believe it just happened again, this is using Simple Recovery model if that matters.

    DBCC CHECKIDENT returns:
    Checking identity information: current identity value 'NULL', current column value '122850'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Version:
    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

    Running:

    DBCC CHECKIDENT ( 'dbo.foo', RESEED )

    and then:
    DBCC CHECKIDENT ("dbo.foo");
    GO

    returns:
    Checking identity information: current identity value '122850', current column value '122850'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Looks like I need to reseed every table with an identity column, FUN!

    Looks like it is a common problem (perhaps specific to SIMPLE recovery model):
    http://www.eggheadcafe.com/software/...r-restore.aspx (excuse the weird characters in the post, but you get the gist).

    It's easiest enough to fix, just a little annoying:
    Code:
    EXEC sp_msforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED )'
    Looks like after talking with a coworker, it is due to the tables originally being replicated (they are not replicated on the destination server).
    Last edited by Gagnon; 02-10-11 at 17:38.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When you're restoring previously replicated database as a non-replicated db you need to be aware of left-overs from replication. The IDENTITY case is just one of those left-overs. Look through this site, identify your replication topology, and handle it accordingly.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •