返回列表 发帖

不同版本SQLSERVER备份还原时造成索引被禁用

以下测试例子以SQL 2008备份,在SQL2014还原,造成索引被禁用.
    --备份环境(SQL Server 2008 R2)
    /*
    MicrosoftSQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
    Apr 2 2010 15:48:46
    Copyright (c) Microsoft Corporation
    Data Center Edition (64-bit) on WindowsNT 6.1(Build 7600: ) (Hypervisor)
    */
    --还原环境(SQL Server 2014)
    /*
    MicrosoftSQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on WindowsNT 6.3(Build 9600: ) (Hypervisor)
    */
    还原后在SQL 2014后查询时提示出错:
   
    检查方法:
    在还原环境运行
SELECT  OBJECT_NAME(object_id) AS TabName,name AS IndexName FROM sys.indexes WHERE is_disabled=1
    解决方法:(生成重建索引语句)
    1.生成语句
SELECT  'ALTER INDEX ' + a.name + ' ON ' + b.name + ' REBUILD;'FROM    sys.indexes AS a        INNER JOIN sys.tables AS b ON b.object_id = a.object_idWHERE a.name IS NOT NULLORDER BY 1
    2.生成执行语句,在还原环境(SQL2014)对象的DB执行
发现有个共同点,在还原对象(表)中列都有用来空间类型geometry类型

返回列表