3

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).

  • Not using the copy database method, though it shouldn't be too hard to use alter sequence in your scripts to reset the value: docs.microsoft.com/en-us/sql/t-sql/statements/… – Sean Gallardy - Retired User Apr 6 '18 at 2:26
  • Yes, I know how to reset the sequence to a desired value. I was just wondering if it's possible to retain the sequence's current value in the copy process (which would be the easiest way to maintain integrity between table and sequence), or is the only way to write a script that checks the currently highest primary key from related table and the resets the sequence according to that. – Xlayman Apr 6 '18 at 8:16
1

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.

| improve this answer | |
0

Any reason you can't backup and restore the template db to a different db name? I would guess that would do the trick.

| improve this answer | |

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.