0

We have recently done a full restore on a database from a backup (schema & data).

When trying to insert new rows we are getting the "Cannot insert duplicate key in object" error message, it is trying to insert duplicate keys into an identity column. We are not manually specifying the values for those columns.

We can fix the issue for a specific table by running

DBCC CHECKIDENT ('Table', RESEED, 10);

(changing 10 to be the next available identity value)

However, this issue appears to affect every single table on the whole database. (We use identity PK columns on every table)

Do you know

a) What might have caused this to happen in the first place?

b) If there is an easy way to reseed all tables to use the next available identity value.

  • 2
    When you write "We have recently done a full restore on a database from a backup (schema & data)" - I'm a little suspicious when you say "schema & data". Were you doing just a plain RESTORE DATABASE command, or how did you do it? Thanks! – Brent Ozar Feb 26 '18 at 13:52
  • 2
    Please tag your SQL Server version – McNets Feb 26 '18 at 13:52
  • @BrentOzar We right clicked databases inside SSMS > Restore Database > Device > Selected Backup. Sorry I'm not a DBA so my phrasing might be a bit off – Jacob Regan Feb 26 '18 at 13:56
  • @JacobRegan OK, perfect! I was worried that there was some kind of scripting involved, but you did good then. – Brent Ozar Feb 26 '18 at 17:04
3

Next script generates a DBCC CHECKIDENT command for each table in your server adding 1 to the current IDENT_CURRENT value.

Notice I've commented:

--EXEC (@SCRIPT);

within the CURSOR, I strongly suggest you to check each generated COMMAND before to execute it.

Additionally you can un-comment within the SELECT statement

--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'

just to execute it for one catalog.

Instead of execute each command, you can get results as text, and execute commands from SSMS.

CREATE TABLE T1 (ID INT IDENTITY, FOO INT);
CREATE TABLE T2 (NID INT IDENTITY, BAR INT);
INSERT INTO T1 VALUES (1),(2),(3);
INSERT INTO T2 VALUES (1),(2),(3),(4),(5);
GO
8 rows affected
DECLARE @TABLE_CATALOG NVARCHAR(128),
        @TABLE_SCHEMA NVARCHAR(128),
        @TABLE_NAME NVARCHAR(128),
        @COLUMN_NAME NVARCHAR(128);

DECLARE @SCRIPT NVARCHAR(MAX);
SET @SCRIPT = '';

BEGIN TRY

    CREATE TABLE #SCRIPTS
    (
        COMMANDS NVARCHAR(500)
    );

    DECLARE CURIDENT CURSOR READ_ONLY LOCAL FAST_FORWARD FOR
        SELECT   C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
        FROM     INFORMATION_SCHEMA.COLUMNS C
        JOIN     INFORMATION_SCHEMA.TABLES T
        ON       C.TABLE_CATALOG = T.TABLE_CATALOG
        AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND      C.TABLE_NAME = T.TABLE_NAME
        WHERE    TABLE_TYPE = 'BASE TABLE'
        AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
        --AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
        ORDER BY C.TABLE_NAME;

    OPEN CURIDENT;

    FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SCRIPT = N'DBCC CHECKIDENT('''
                        + (@TABLE_SCHEMA + N'.' + @TABLE_NAME)
                        + N''', RESEED, '
                        + CAST(COALESCE(IDENT_CURRENT(@TABLE_SCHEMA + N'.' + @TABLE_NAME) + 1, 0) AS VARCHAR(30))
                        + ');'


        INSERT INTO #SCRIPTS VALUES (@SCRIPT);

        --UNCOMENT TO EXECUTE FOR EACH ROW
        --EXEC (@SCRIPT);

        FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;
    END

    CLOSE CURIDENT;
    DEALLOCATE CURIDENT;

    SELECT * FROM #SCRIPTS;

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END TRY
BEGIN CATCH

    IF CURSOR_STATUS('LOCAL','CURIDENT') > -1
    BEGIN
        CLOSE CURIDENT;
        DEALLOCATE CURIDENT;
    END

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END CATCH


GO
| COMMANDS                              |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here

If don't want to execute the whole script, you can get the same result by executing next query:

SELECT   N'DBCC CHECKIDENT('''
         + (C.TABLE_SCHEMA + N'.' + C.TABLE_NAME)
         + N''', RESEED, '
         + CAST(COALESCE(IDENT_CURRENT(C.TABLE_SCHEMA + N'.' + C.TABLE_NAME) + 1, 0) AS NVARCHAR(30))
         + ');'
FROM     INFORMATION_SCHEMA.COLUMNS C
JOIN     INFORMATION_SCHEMA.TABLES T
ON       C.TABLE_CATALOG = T.TABLE_CATALOG
AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND      C.TABLE_NAME = T.TABLE_NAME
WHERE    TABLE_TYPE = 'BASE TABLE'
AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
ORDER BY C.TABLE_NAME;
GO
| (No column name)                      |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here

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.