How to keep identity while taking backup of database

Question

After taking the backup of database, when i tried to restore it, the seed of identity is reset to 0. So when i try to insert any new values, the id (primary key) is started 1.

Now the issue is, i have relational data tables. let say from table x after some operation the data is moved to table y. But as the seed is set to 0, the values of id of table x and table y clashes. and that gives me error.

Is there a any way while taking the backup, we can save the value of identity also.

P.S. :

size of database is huge and manual alteration is next to impossible.
seed of identity is reset to 0 only when the table is empty.

Edit :

Sorry for a bit confusion, by backup, i mean export (my bad). as the empty tables's seed is set to default, primary key in my tables are clashing, so with export of database, i need to store the values of identity of every table also.

Is there any way i can do that ?

Take a read of this one, may help: https://dba.stackexchange.com/questions/163876/restoring-database-backup-makes-identity-column-start-at-1-again — Keith, Jul 31 '17 at 08:33
This is not normal backup or restore behavior. Make sure your tool or script isn't causing the reset. — MWillemse, Jul 31 '17 at 08:33
@Leonidas199x already gone through that. but that post didn't helped a lot. — Mr.Bhanushali, Jul 31 '17 at 09:08
@MWillemse sorry for mistake, i am using backup, i am exporting database. — Mr.Bhanushali, Jul 31 '17 at 09:09
Use `DBCC CHECKIDENT('' RESEED)` (no third parameter) to have SQL server find out what the next value should be. — MWillemse, Jul 31 '17 at 10:01
I am having around 100 tables. it is not feasible to fire the DBCC CHECKIDENT on every table. @MWillemse — Mr.Bhanushali, Jul 31 '17 at 11:09

score 2 · Answer 1 · answered Jul 31 '17 at 08:32

2

The identity seed is not reset or modified when using

BACKUP DATABASE
RESTORE DATABASE

You must be exporting and importing data, which is not backup and restore.
I say again

BACKUP DATABASE is the only way to backup the database
RESTORE DATABASE is the only way to restore the database,

If you want to keep export/import method, then simply use DBCC CHECKIDENT to reset the seed.

Also read this to see why the seed is set to zero:
SQL server identity column values start at 0 instead of 1

answered Jul 31 '17 at 08:32

gbn

422,506
82
585
676

sorry for saying backup, yeah i am exporting my database and need to keep the value of identity seed. is there any way you can suggest. – Mr.Bhanushali Jul 31 '17 at 09:07
As I said, DBCC CHECKIDENT – gbn Jul 31 '17 at 10:13
i am having around 100 tables. it is not feasible to fire the DBCC CHECKIDENT on every table. – Mr.Bhanushali Jul 31 '17 at 11:08
@Mr.Bhanushali Use SET IDENTITY INSERT then on import. If this is too much because you have a 100 tables, then there is no other solution. – gbn Jul 31 '17 at 11:51

How to keep identity while taking backup of database

1 Answers1