How to re-seed a table identity in SQL Server 2008 and undo it all safely?

I need to do this for testing only, but then undo it when the test is done.

I have seen some tutorials online on how to re-seed a table, but not so much on how to undo it.

Let's say the table definition is the following:

create table beer
(
 beer_id  numeric(10) not null,
 mnemonic        nvarchar(8)
);
go

Let's say that I want the new identities to temporarily start at 12345, and at the end delete the new rows and set the next identity to what it would have been.

Solution 1:

The command to reset the identity property is

DBCC CHECKIDENT (tablename, RESEED, new_reseed_value)

When you want to set the column identity to 12345 you run this

DBCC CHECKIDENT (beer, RESEED, 12345)

When you want to delete test rows and restore the value to the previous value, you do the following.

DELETE
FROM beer
WHERE beer_id >= 12345 ;

DECLARE @NewSeed NUMERIC(10)
SELECT @NewSeed = MAX(beer_id)
FROM beer ;

DBCC CHECKIDENT (beer, RESEED, @NewSeed)

Here is a demonstration for your scenario. Note that the beer_id column is created with the IDENTITY (1, 1) property, which seeds the identity to 1 with an increment of 1.

CREATE TABLE beer
    (        
    beer_id NUMERIC(10) IDENTITY (1,1) NOT NULL,
    mnemonic NVARCHAR(8)
    );

GO

INSERT INTO beer(mnemonic) VALUES ('Beer 1')
INSERT INTO beer(mnemonic) VALUES ('Beer 2')

SELECT *
FROM beer ;

DBCC CHECKIDENT (beer, RESEED, 12345)
GO

INSERT INTO beer(mnemonic) VALUES ('Beer 3')
INSERT INTO beer(mnemonic) VALUES ('Beer 4')

SELECT *
FROM beer ;

DELETE
FROM beer
WHERE beer_id >= 12345 ;

DECLARE @NewSeed NUMERIC(10)
SELECT @NewSeed = MAX(beer_id)
FROM beer ;

DBCC CHECKIDENT (beer, RESEED, @NewSeed)
GO

INSERT INTO beer(mnemonic) VALUES ('Beer 5')
INSERT INTO beer(mnemonic) VALUES ('Beer 6')

SELECT *
FROM beer ;

Solution 2:

Sometimes we have Schema restriction from some users and thus causes error (Cannot find a table or object with the name "TableName". Check the system catalog), hence the best thing is to follow the below code.

Schema.TableName should be enclosed in apostrophe

DECLARE @SeedValue INT
SET @SeedValue = (SELECT MAX(ColumnName) FROM Schema.TableName)
DBCC CHECKIDENT ('Schema.TableName',RESEED,@SeedValue)