Identity columns reset on restore

Category: sql server express

Question

kptools on Mon, 01 Oct 2018 01:18:07


Using SQL EXPRESS 2017, I use BACKUP to create a database backup, the database comprising tables which have identity columns.  I just use plain vanilla BACKUP DATABASE x TO DISK='xxx'.  It all works just fine.  I was testing the restore using, again, plain vanilla RESTORE DATABASE x FROM DISK='xxx'.  I have tried both with and without using WITH REPLACE.  After the restore, the data are all present in the tables.  However, each table that has a column with IDENTITY=yes has lost the IDENTITY modifier.  The column is there; the data are there; the column is no longer an IDENTITY column.  Bulk copy is not an appropriate option for these procedures nor is table recreation and data copy.  Is there a switch or option i am not setting?

Replies

kptools on Mon, 01 Oct 2018 02:24:56


I found out that you have to use the KEEP_REPLICATION option, so the string would be something like this:

USE [MASTER]
RESTORE DATABASE [xxx] FROM DISK=N'D:\Backups\xxx.bak' WITH  FILE=1, MOVE N'xxx' TO N'D:\DATA\xx_.mdf',MOVE N'xx_log' TO N'D:\DATA\xx_log.ldf',KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 5

(this is the script generated by SSMS).

However, on to the next issue: the seed value is reset to one.