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?
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:
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.