February 25, 2016 at 7:03 am
I have a database on a SQL2005 server.
I do a full backup. Copy the .bak to a 2012 server.
Restore the database to the 2012 server.
Run checkdb on the newly restored database.
Column 'p_qry_grp' was created NOT NULL, but is NULL in the row.
One record in this table (with several hundred rows) now has a null where there ought not be a null as this field is a uniqueidentifier.
I check the original database on the 2005 server. No problems. Same record has a value for this field.
I restored the same backup file to a 2008 server. No problems. Same record has a value for this field.
I did a full backup from the 2008 server.
Restored to the 2012 server.
Same problem running checkdb.
I've used tablediff to confirm this is the only difference between the databases
95B69CEA-CED2-4C3A-B5C9-91E514B04314
10000004-3B00-5F00-3256-583055554137
C52CC5CD-7EB4-4676-BFD6-25B6987D04F9
The middle GUID is the one that doesn't make it through the restore process. It does look odd compared to the others in the table.
Any ideas why this GUID does offend SQL 2012 so much?
tia,
Steve
February 25, 2016 at 8:07 am
This is a very odd situation!! It is troubling on numerous levels (different values on restores, non-null column with NULL value). Please pass this off to Microsoft as first blush sounds like at least one bug happening.
You could look at each data page in various copies to see if something wonky is there. Could be something with the redo/undo phase that is different too (that's at least part of the issue I suspect).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2016 at 9:04 am
Have you run a DBCC?
Theoretically, this could happen, but I've never seen any values different from a restore. The one thing I'd note is that the value could have changed since the backup started. The value at the time the backup started is the value that would be stored in the file. If there was a transaction in flight, it would roll back.
I'd save off this backup file and make a new one and determine if you see the same thing.
February 25, 2016 at 9:25 am
Odd is right. This is a third party database. We have several instances of this database (for different clients on our end). I just tried this process with a different copy and got the same results. I guess consistency is good?
I think I'll try detaching and reattaching to see what happens.
February 25, 2016 at 9:28 am
Yeah - the dbcc checkdb is what brought the inconsistency to light. I've restored the database to a 2008 server without issue, but the same backup, restored to a 2012 server, has this problem. I've tried several backups and it has happened every time.
February 25, 2016 at 9:31 am
I have to agree with Kevin, contact Microsoft.
February 25, 2016 at 10:41 am
+1. Call MS, and let us know what they say. That is disturbing. I suspect a broken page somewhere with corruption, but it would be good to know what they think.
February 25, 2016 at 11:02 am
I tried the detach/attach thing. Same problem. I'll call MS and post a solution if it's not overly embarrassing.
Steve
February 25, 2016 at 11:24 am
Steve Hoyer (2/25/2016)
I tried the detach/attach thing. Same problem. I'll call MS and post a solution if it's not overly embarrassing.Steve
Please post even it if is "overly embarrassing". You have NO IDEA how many clients I have seen burned by such things, and it would have been nice some times where they DID a Binoogle search they could have found something like your post to keep them out of trouble. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 26, 2016 at 1:21 pm
2005 to 2012 is greater than 2 major versions. Try this, 2005 to 2008 or 08R2, up the compatibility level, new bak, then restore to 2012.
Basically "90 compatibility level and upgrade from version 90 (SQL Server 2005 Database Engine)." is a deprecated feature in 2012:
https://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx
February 26, 2016 at 1:27 pm
TheSQLGuru (2/25/2016)
Steve Hoyer (2/25/2016)
I tried the detach/attach thing. Same problem. I'll call MS and post a solution if it's not overly embarrassing.Steve
Please post even it if is "overly embarrassing". You have NO IDEA how many clients I have seen burned by such things, and it would have been nice some times where they DID a Binoogle search they could have found something like your post to keep them out of trouble. 😎
Yes, please post back even it's embarrassing. If this is a bug with the restore, it's beyond disturbing.
February 26, 2016 at 2:44 pm
What compatibility level are you running?
Try the two step upgrade, I've got $ it will work. (90 to 100 or 110, new backup to 120)
April 19, 2016 at 5:30 am
Jon.Morisi (2/26/2016)
2005 to 2012 is greater than 2 major versions. Try this, 2005 to 2008 or 08R2, up the compatibility level, new bak, then restore to 2012.Basically "90 compatibility level and upgrade from version 90 (SQL Server 2005 Database Engine)." is a deprecated feature in 2012:
https://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx
That is a good piece of information to have in mind, as I have legacy apps and databases I'm supporting. Unfortunately, I did try this to no avail.
Steve.
April 19, 2016 at 5:40 am
I have spent some time going back and forth with MS Support, convinced them a workaround of "copy the data to another table in another database then copy it back" wasn't a confidence building measure, and after not hearing anything for a few weeks checked back in and got this reply:
The escalation team is going to file an RFC for this as it seems to be a bug. It will be tested by the product group and it will take time for the patch to be released as it will require regressive testing and coding for the patch.
So I guess I come up with a work around which will provide confidence that further backup/restore operations will be successful. Then test a bunch 🙂
Steve.
April 19, 2016 at 5:48 am
Ed Wagner (2/26/2016)
TheSQLGuru (2/25/2016)
Steve Hoyer (2/25/2016)
I tried the detach/attach thing. Same problem. I'll call MS and post a solution if it's not overly embarrassing.Steve
Please post even it if is "overly embarrassing". You have NO IDEA how many clients I have seen burned by such things, and it would have been nice some times where they DID a Binoogle search they could have found something like your post to keep them out of trouble. 😎
Yes, please post back even it's embarrassing. If this is a bug with the restore, it's beyond disturbing.
I've always owned up before when it was a PEBCAK. I'm firmly in the camp of "let me learn from your mistakes and I'll let you learn from mine".
However, in this case, MS is calling it a bug.
Steve
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply