Sql-server – How to retain the current value of a sequence when copying a database in SQL Server

sequencesql serversql server 2014

I have a template database that contains tables with some data I'd like to have always when starting a new project. The idea is to copy the template when starting a new project. I use sequences for getting id column values (in code) for data rows in tables.

The problem is this: when I copy the database using SQL Server Management Studio's Tasks -> Copy Database, and select Use the SQL Management Object method as the transfer method, the sequence values seem to reset to source database's sequence's "reset value" instead of retaining the sequences current value.

Is there any way to retain the current value of sequence automatically when copying the database? Now when I add some data to the template database, I basically have to every time update related table's reset value, or otherwise the data and the current value of sequence will be off in the copied database.

I'm using SQL Server Management Studio 2012 and the server version is 12.0.4237 (so SQL Server 2014).

Best Answer

You can get the current seed values by running:


SELECT 
   s.name as [schema],
   t.name as [table],
   IDENT_CURRENT( s.name + '.' +t.name )  NextIdentValue,
   'DBCC CHECKIDENT ('+char(39)+s.name+'.' +t.name+char(39)+',RESEED,'+ CAST(IDENT_CURRENT( s.name + '.' +t.name ) as varchar(50))+ '); ' as cmdSQL
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

Run the cmdSQL script column on the target system.

Depending on your requirements, you can store the output to a table/file and then execute the synchronization script after restore.

Related Question