I have a table in which the primary key is a GUID with a default of NewID. If the PK column had a value of XYZ when I backed it up, I would expect XYZ in the restored table. In fact I get PQR (well the GUID equivalent). It looks like the GUID gets regenerated on restore. Is this in fact happening (it seems a little crazy), and if so, how can it be avoided?
+2
A:
Nobody is changing your data on restore (I assume you mean backup/restore as the true BACKUP/RESTORE statement, not some custom data copy solution). If the guid has changed you either have restored different data, or you're changing the data yourself and you don't exactly know when (triggers? application?).
Remus Rusanu
2009-06-18 15:54:04
+1 exactly - the default ONLY kicks in if you INSERT a new row and don't probvide a value for that particular column. This should never happen on a restore.
marc_s
2009-06-18 16:01:34
Here's what I found in my testing: I was using the WITH REPLACE option b/c I wasn't backing up the tail of the log. It looks to me like this causes the issue I describe with GUIDS being re-gened. Interestingly, if I do backup the log and use WITH REPLACE, everything is OK. I'm not sure if this is b/c WITH REPLACE is ignored in this scenario, or if something else is up.
2009-06-18 17:01:24
So basically you're restoring *different* data. Is not your GUIDs that are at 'issue', is your *entire* database, you simply looking at a different one after the RESTORE.
Remus Rusanu
2009-06-18 18:21:55
A:
no this can not happen with just a restore
I also want to point out that if you have a PK on a uniqueidentifier column to use NEWSEQUENTIALID() instead of NEWID() because NEWID() will cause horrible pagesplits on a clustered index (which a primary key is by default)
I have some code just showing the difference here: Some Simple Code To Show The Difference Between Newid And Newsequentialid
SQLMenace
2009-06-18 16:27:17