Reset Identity Value To 1 After Replication

Question

I use SQL Server and I created a replication. Replication works very well. But i've create a backup subscriber's db to check values. I check tables with "IDENT_CURRENT" and i see that identity column's values are 1. Is it normal? I can't a new insert to table because it's raise "Violation of PRIMARY KEY" error. How can i fix this?

Answer 1

I've write my own solution. This code reset all table's identity values to max value. I use it after database replication.

DECLARE @TableName AS NVARCHAR(MAX)
DECLARE @ColName AS NVARCHAR(MAX)

DECLARE emp_cursor CURSOR FOR     
select COLUMN_NAME, TABLE_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    order by TABLE_NAME    
  
OPEN emp_cursor   

FETCH NEXT FROM emp_cursor     
INTO @ColName,@TableName 

WHILE @@FETCH_STATUS = 0    
BEGIN    
    --PRINT @ColName
    --PRINT @TableName
      
    

    DECLARE @MaxId  AS INT

    --PRINT @TableName
    EXEC ('DECLARE @MaxId  AS INT;
            DECLARE @TableName  AS NVARCHAR(MAX)='''+@TableName+''';
            SELECT @MaxId=ISNULL(MAX(' + @ColName + '),0)+1 FROM '+@TableName+';'+
            'DBCC CHECKIDENT ('+@TableName+', RESEED,@MaxId);'+
            'PRINT ''Table: ''+ @TableName +'', New Identity Val:''+CAST(@MaxId AS NVARCHAR);PRINT CHAR(13);'
            
    )

FETCH NEXT FROM emp_cursor
INTO @ColName,@TableName

END     
CLOSE emp_cursor;    
DEALLOCATE emp_cursor;