Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I've restored a database backup in a new SQL Server 2012 server from a .bak made in SQL Server 2008 R2, and one of the tables has it's primary key index disabled. This doesn't happen when I restore to a 2008R2 server.

The structure of the table is:

CREATE TABLE RouteSegment (
   RouteSegmentID bigint IDENTITY(1,1) NOT NULL,
   Representation geography            NOT NULL,
   LastTime                            int NOT NULL,
   SourceStop1ID                       bigint NULL,
   SourceStop2ID                       bigint NULL,
   Length  as ([Representation].[STLength]()) PERSISTED,
   CONSTRAINT PK_RouteSegment PRIMARY KEY CLUSTERED (RouteSegmentID ASC)
 )

Is this a known problem in SQL Server 2012, or is there something I've missed?

I know that I can fix the problem with an index rebuild, I ask because it's surprising behavior, and want to know how to prevent it.

share|improve this question
Can you post the table structure and the primary key definition? Also did you run any DBCC checks against the source database? – Aaron Bertrand Jul 11 '12 at 21:05
I'm outside the office. Tomorrow I'll upload a creation script. I didn't run a check, will do it and report back. Thanks! – Pablo Montilla Jul 11 '12 at 21:49
Just to clarify - run DBCC CHECKTABLE against RouteSegment on the source server. It's fairly likely that upgrading (which runs a full suit of checks as part of the upgrade) has found an extant issue (i.e. one that's just sitting there) that hasn't blown up yet. – Simon Righarts Jul 12 '12 at 4:08
DBCC CHECKTABLE(RouteSegment) on a SQL Server 2008 R2 server doesn't report any problems with the table. – Pablo Montilla Jul 12 '12 at 17:18

migrated from stackoverflow.com Jul 11 '12 at 22:56

1 Answer

up vote 3 down vote accepted

This has to do with any table which contains a geospatial field. I'm not sure why yet (is it a bug or a feature lol), but that's the cause.

Update: the problem only appears when there is a geospatial field and a PERSISTED column that uses it.

share|improve this answer
ALTER INDEX [YourPrimaryKey] ON [YourDatabase].[YourTable] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO – Chuck Straughn Sep 18 '12 at 18:32
You might want to indicate why you believe this is a problem for tables with geospatial fields. Did you find a bug report or have you found other users with this issue, or perhaps can you provide some code that definitively recreates the problem? – Max Vernon Sep 19 '12 at 3:54
Is this only a problem when restoring a table from SQL Server 2008 R2 that has a geospatial field, or is it a problem observed restoring any database version? – Max Vernon Sep 19 '12 at 3:57
Thanks to @ChuckStraughn I've found the condition which makes the problem appear. Apart from a spatial column, you need to have a PERSISTED column referencing it for the problem to manifest itself. Don't know what to do...edit Chuck's answer or add a new answer myself... – Pablo Montilla Sep 24 '12 at 14:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.