Not sure if this actually qualifies for this forum so please by all means move this topic to the appropriate place if it isn't.
I'm writing a C# Windows Application (not a web app) and it uses either an SQL or Access database to store its data for multiple network users.
With an Access database, backing up and restoring data is easy, just copy the file and everything should be fine. But what about backing up and restoring data on a SQL server?
I am building an SQL database design that makes use of MANY one-to-many and many-to-many relationships. Some of the relationships are based off of the auto increment field "ID." If I back the data up, I would imagine if there was a break in the ID field sequence at backup time (which is VERY possible if a record is deleted)...when I restore that database, how will the server know which records are related because a record after backup might not be assigned the same auto increment ID number that it was before backup? I know the relationship rules will be restored, but you can't assign a value to an auto increment field, so record 20 in table1 that used to reference many records in table2 by using the ID number of "20" might not be assigned the number 20 when it is restored right? It could be assigned 10 or 5 or 1 depending on where it is restored.
Am I missing something? Is this something to be worried about or is it better to not use the ID field and assign key index values myself and keep track of them, possibly in a different table that can be referred at restore time?
