数据库置疑处理文档

修订记录


日期

Date

修订版本

Revision Version

修改描述

Change Description

作者

Author

2010-04-261.0格式化UltraSQL

目 录

     

    一、 知识点简介

    1.    DBCC 中的 CHECKDB 命令

    2.    重置置疑状态

    3.    sp_add_log_file_recover_suspect_db
    4.    DBCC 中的 DBRECOVER 命令

    二、 损坏的数据库

    1.    思路介绍
    2.    经验  
    3.    处理建议

一、知识点简介

1. DBCC 中的 CHECKDB 命令：

DBCC CHECKDB

检查指定数据库中的所有对象的分配和结构完整性。

 

1） 语法：

DBCC CHECKDB

( ‘database_name’

[ , NOINDEX

| { REPAIR_ALLOW_DATA_LOSS

| REPAIR_FAST

| REPAIR_REBUILD

} ]

) [ WITH { [ ALL_ERRORMSGS ]

[ , [ NO_INFOMSGS ] ]

[ , [ TABLOCK ] ]

[ , [ ESTIMATEONLY ] ]

[ , [ PHYSICAL_ONLY ] ]

}

]

 

2） 参数：

‘database_name’

是要对其中的所有对象分配和结构完整性进行检查的数据库。如果未指定，则默认为当前数据库。数据库名称必须符合标识符的规则。有关更多信息，请参见使用标识符。

 

NOINDEX

指定不检查非系统表的非聚集索引。NOINDEX 减少执行总时间，因为它不对用户定义的表的非聚集索引进行检查。NOINDEX 对系统表没有影响，因为 DBCC CHECKDB 总是对所有系统表索引进行检查。

 

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

指定 DBCC CHECKDB 修复发现的错误。给定的 database_name 必须在单用户模式下以使用修复选项，它可以是下列值之一。

 

值 描述

REPAIR_ALLOW_DATA_LOSS 执行由 REPAIR_REBUILD 完成的所有修复，包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象。这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。修复完成后，备份数据库。

 

REPAIR_FAST 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。

 

REPAIR_REBUILD 执行由 REPAIR_FAST 完成的所有修复，包括需要较长时间的修复（如重建索引）。执行这些修复时不会有丢失数据的危险。

 

WITH

指定有关下列内容的选项：返回错误信息的数量、获得的锁或估计的 tempdb 要求。

 

ALL_ERRORMSGS

显示每个对象不受限制的错误数。如果没有指定 ALL_ERRORMSGS，每个对象至多显示 200 个错误信息。按对象 ID 对错误信息进行排序（从 tempdb 中生成的消息除外）。

 

NO_INFOMSGS

禁止显示所有信息性消息（严重级别 10）和关于所用空间的报告。

 

TABLOCK

导致 DBCC CHECKDB 获得共享表锁。TABLOCK 可使 DBCC CHECKDB 在负荷较重的数据库上运行得更快，但 DBCC CHECKDB 运行时会减少数据库上可获得的并发性。

 

ESTIMATE ONLY

显示估计的 tempdb 空间大小，要运行带有所有其它指定选项的 DBCC CHECKDB 则需要该空间。不执行该检查。

 

PHYSICAL_ONLY

仅限于检查页和记录标题物理结构的完整性，以及页对象 ID 和索引 ID 与分配结构之间的一致性。该检查旨在以较低的开销检查数据库的物理一致性，同时还检测会危及用户数据安全的残缺页和常见的硬件故障。PHYSICAL_ONLY 始终意味着 NO_INFOMSGS，并且不能与任何修复选项一起使用。

 

3） 注释：

DBCC CHECKDB 对索引视图执行物理一致性检查。只用于向后兼容的 NOINDEX 选项也适用于索引视图上的任何辅助索引。

 

DBCC CHECKDB 是最安全的修复语句，因为它对最多的可能出现的各种错误进行标识和修复。如果只报告数据库中有分配错误，请执行带修复选项的 DBCC CHECKALLOC 以对这些错误进行修复。然而，若要确保正确修复所有错误（包括分配错误），请执行带修复选项的 DBCC CHECKDB，而不要执行带修复选项的 DBCC CHECKALLOC。

 

DBCC CHECKDB 对数据库中所有内容的完整性进行验证。如果当前正在执行或最近已执行 DBCC CHECKDB，则不需要运行 DBCC CHECKALLOC 或 DBCC CHECKTABLE。

 

DBCC CHECKDB 执行同样的检查，仿佛是对数据库中的每个表执行 DBCC CHECKALLOC 语句和 DBCC CHECKTABLE 语句。

 

默认情况下，DBCC CHECKDB 不获取表锁。但它获取架构锁，该锁防止对元数据进行更改，但允许更改数据。获取的架构锁将防止用户得到排它表锁，在生成聚集索引、除去任何索引或截断表时需要排它表锁。

DBCC 语句收集信息，然后扫描日志以查找所做的任何其它更改，并在扫描的结尾将两组信息合并在一起以产生数据的一致视图。

 

如果指定 TABLOCK 选项，DBCC CHECKDB 将获取共享表锁。这样可允许某些类别的错误有更详细的错误信息，并通过避免使用事务日志数据而将所要求的 tempdb 空间大小降为最低。TABLOCK 选项不阻止日志截断并使命令可以更快地运行。

 

DBCC CHECKDB 对数据库中每个表的 text、ntext 和 p_w_picpath 页的链接和大小及数据库中所有页的分配进行检查。

 

对于数据库中每个表，DBCC CHECKDB 检查其：

◇ 索引和数据页是否已正确链接。

◇ 索引是否按照正确的顺序排列。

◇ 各指针是否一致。

◇ 每页上的数据是否均合理。

◇ 页面偏移量是否合理。

 

错误表示数据库中的潜在问题，应该立即改正。

 

默认情况下，DBCC CHECKDB 对对象执行并行检查。并行度由查询处理器自动确定。最大并行度的配置方式与并行查询相同。使用 sp_configure 系统存储过程限制可用于 DBCC 检查的最大处理器数。有关更多信息，请参见 max degree of parallelism 选项。

 

使用跟踪标记 2528 可禁用并行检查。有关更多信息，请参见跟踪标记。

 

4） 结果集：

不管是否指定任何选项（NO_INFOMSGS 或 NOINDEX 选项除外），如果未指定数据库，DBCC CHECKDB 返回当前数据库的以下结果集（值可能会变化）：

 

DBCC results for ‘master’.

DBCC results for ‘sysobjects’.

There are 862 rows in 13 pages for object ‘sysobjects’.

DBCC results for ‘sysindexes’.

There are 80 rows in 3 pages for object ‘sysindexes’.

‘…’

DBCC results for ‘spt_provider_types’.

There are 23 rows in 1 pages for object ‘spt_provider_types’.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘master’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

如果指定 NO_INFOMSGS 选项，DBCC CHECKDB 将返回以下结果集（消息）：

The command(s) completed successfully.

 

如果指定 ESTIMATEONLY 选项，DBCC CHECKDB 将返回以下结果集。

Estimated TEMPDB space needed for CHECKALLOC (KB)

————————————————-

13

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)

————————————————–

57

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

5） 权限：

DBCC CHECKDB 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

 

6） 示例：

A. 检查当前数据库和 pubs 数据库

下例对当前数据库和 pubs 数据库执行 DBCC CHECKDB。

 

-- Check the current database.
DBCC CHECKDB
GO
-- Check the pubs database without nonclustered indexes.
DBCC CHECKDB ('pubs', NOINDEX)
GO

 

B. 检查当前数据库，禁止显示信息性消息

下例检查当前数据库，并禁止显示所有信息性消息。

 

DBCC CHECKDB WITH NO_INFOMSGS
GO

 

7） DBCC CHECKDB 建议：

第三方在 Microsoft? SQL Server? 2000 中，可以在用户使用数据库时运行 DBCC CHECKDB，因为 DBCC CHECKDB 在检查每个数据库表时在表上控制的锁的类型均更改。

 

在 SQL Server 7.0 和早期版本中，DBCC CHECKDB（依次在数据库的每个表上运行 DBCC CHECKTABLE 和 CHECKALLOC）常常在表上控制共享锁 (S)，因而阻塞了所有的数据修改语言 (DML) 语句。

 

在 SQL Server 2000 中，当检查表时 DBCC CHECKDB 在表上控制架构锁以防止元数据的更改，因而允许在正在检查的表上使用除任何数据定义语言 (DDL) 语句之外的 DML 语句。该变化对于决定何时运行 DBCC CHECKDB 提供了更大的灵活性，因为 DBCC CHECKDB 并不完全拒绝用户对系统的使用。

 

DBCC CHECKDB 是大量占用 CPU 和磁盘的操作。每一个需要检查的数据页都必须首先从磁盘读入内存。另外，DBCC CHECKDB 使用 tempdb 排序。

 

如果在 DBCC CHECKDB 运行时动态执行事务，那么事务日志会继续增长，因为 DBCC 命令在完成日志的读取之前阻塞日志截断。

 

建议在服务器负荷较少的时候运行 DBCC CHECKDB。如果在负荷高峰期运行 DBCC CHECKDB，那么事务吞吐量性能和 DBCC CHECKDB 完成时间性能都会受到影响。

 

要获得好的 DBCC 性能的一些建议

在系统使用率较低时运行 CHECKDB。

请确保未同时执行其它磁盘 I/O 操作，例如磁盘备份。

将 tempdb 放到单独的磁盘系统或快速磁盘子系统中。

允许 tempdb 在驱动器上有足够的扩展空间。使用带有 ESTIMATE ONLY 的 DBCC 估计 tempdb 将需要多少空间。

避免运行占用大量 CPU 的查询或批处理作业。

在 DBCC 命令运行时，减少活动事务。

使用 NO_INFOMSGS 选项显著减少处理和 tempdb 的使用。

考虑使用带有 PHYSICAL_ONLY 选项的 DBCC CHECKDB 来检查页和记录首部的物理结构。当硬件导致的错误被置疑时，这个操作将执行快速检查。

 

8） 经验谈：

在数据库的每个表上运行 DBCC CHECKDB 与 DBCC CHECKALLOC 和 DBCC CHECKTABLE 是一样的。如果有某个特定的表正在报告错误，你可以运行其他命令，否则，应该总是运行 DBCC CHECKDB命令。

 

地方运行这个命令不需要参数。如果不带上面的任何参数运行 DBCC CHECKDB，这个命令将检查当前数据库的一致性。语法中最重要的参数是那些与修复（repair）相连的参数。要是用3个修复选项中的任意1个，数据库必须处于单用户模式。

 

修复参数范围可以从 REPAIR_FAST 到 REPAIR_ALLOW_DATA_LOSS，前者修复非聚集索引中的次关键问题，后者修复主要问题并允许 SQL Server 删除损坏的文本对象。位于这两者之间的参数是 REPAIR_REBUILD，它提供 REPAIR_FAST 的全部特性，此外，还重建有问题的索引。执行 REPAIR_REBUILD 和 REPAIR_FAST 没有丢失数据的风险。你可以用下面的语法运行这个命令并修复次要问题：

 

ALTER Database Northwind
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (Northwind, REPAIR_FAST)

 

在多GB的大型数据库上执行 DBCC CHECKDB 命令时，这个命令可能会花数小时的时间来执行，并占用 TempDB 数据库的大量空间。在执行这个命令之前，你可以考虑用 ESTIMATEONLY 参数来运行它，这样会显示将需要多少 TempDB 空间来运行这个命令。例如下面的命令：

 

DBCC CHECKDB (Northwind, REPAIR_REBUILD) WITH ESTIMATEONLY

 

输出下面的结果：

Estimated TEMPDB space needed for CHECKALLOC (KB)

————————————————-

14

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)

———————————————–

17

(1 row(s) affected)

DBCC execution completed. If DBCC printed error message,Contace your system administrator.

 

可以使用下面的语法，对系统上的每一个数据库运行 DBCC CHECKDB 命令：

 

Sp_MSforeachdb @command1 = “Print ‘?’”, @command2 = “DBCC CHECKDB (?)”

 

也可以使用 TABLOCK 参数在执行期间捕获表的共享锁。这样会提高 DBCC CHECKDB命令的执行速度。为此，可使用下面的语法：

 

DBCC CHECKDB (Northwind) WITH TABLOCK

 

包括 DBCC CHECKDB 在内的一些 DBCC 命令在执行期间会显著影响性能。为了改善 DBCC 命令的性能，我提供以下建议：

◇ 在非高峰时间运行。

◇ 限制命令执行期间的事务数量。

◇ 将数据库置于单用户模式。

◇ 将 TempDB 放在一个单独的驱动器上，并保证此驱动器有足够的空间。

◇ 使用 NO_INFOMSGS 选项来减少对 TempDB 的使用。

◇ 尽量不要在服务器上运行 I/O 密集的进程（尽可能停止不必要的服务）。

 

2. 重置置疑状态：

sp_resetstatus

重置置疑数据库的状态。

 

1) 语法：

sp_resetstatus [ @DBName = ] ‘database’

 

2) 参数：

[@DBName =] ‘database’

是要重置的数据库名。database 的数据类型为 sysname，无默认值。

返回代码值

0（成功）或 1（失败）

 

3) 注释：

sp_resetstatus 关闭数据库上的置疑标记。此过程更新 sysdatabases 中的命名数据库的模式和状态列。在运行此过程之前，应参考 SQL Server 错误日志并解决所有问题。执行 sp_resetstatus 后停止并重新启动 SQL Server。

 

由于某些原因，数据库可能成为置疑状态。可能的原因包括操作系统拒绝对数据库资源的访问，以及一个或多个数据库文件不可用性或已损坏。

4) 权限：

只有 sysadmin 固定服务器角色成员才能执行 sp_resetstatus。

 

5) 示例：

下例重置 PUBS 数据库的状态。

 

EXEC sp_resetstatus 'PUBS'

 

6) 重置建议：

如果 SQL Server 因为磁盘驱动器不再有可用空间，而不能完成数据库的恢复，那么 Microsoft? SQL Server? 2000 会返回错误 1105 并且将 sysdatabases 中的 status 列设为置疑。按下面的步骤解决这个问题：

① 执行 sp_resetstatus。

② 用 ALTER DATABASE 向数据库添加一个数据文件或日志文件。

③ 停止并重新启动 SQL Server。

用新的数据文件或日志文件所提供的额外空间，SQL Server 应该能完成数据库的恢复。

④ 释放磁盘空间并且重新运行恢复操作。

sp_resetstatus 关闭数据库的置疑标志，但是原封不动地保持数据库的其它选项。

注意 只有在您的主要支持提供者指导下或有疑难解答建议的做法时，才可以使用 sp_resetstatus。否则，可能会损坏数据库。

 

由于该过程修改了系统表，系统管理员必须在创建这个过程前，启用系统表更新。要启用更新，使用下面的过程：

 

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

 

过程创建后，立即禁用系统表更新：

 

sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

 

只有系统管理员才能执行 sp_resetstatus。执行该过程后，立即关闭 SQL Server。

语法为：

 

sp_resetstatus database_name

 

下面的例子将关闭 PRODUCTION 数据库的置疑标志。

 

sp_resetstatus PRODUCTION

 

下面是结果集：

Database ‘PRODUCTION’ status reset!

WARNING: You must reboot SQL Server prior to accessing this database!

 

sp_resetstatus 存储过程代码

 

下面是 sp_resetstatus 存储过程的代码：

 

IF EXISTS ( SELECT * from sysobjects where name = 'sp_resetstatus' )
DROP PROCEDURE sp_resetstatus
GO
CREATE PROC sp_resetstatus @dbname varchar(30) AS
DECLARE @msg varchar(80)
IF @@trancount > 0
BEGIN
PRINT 'Can''t run sp_resetstatus from within a transaction.'
RETURN (1)
END
IF suser_id() != 1
BEGIN
SELECT @msg = 'You must be the System Administrator (SA)'
SELECT @msg = @msg + ' to execute this procedure.'
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname) != 1
BEGIN
SELECT @msg = 'Database ' + @dbname + ' does not exist!'
PRINT @msg
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'sp_resetstatus can only be run on suspect databases.'
RETURN (1)
END
BEGIN TRAN
UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = @dbname
IF @@error != 0 OR @@rowcount != 1
ROLLBACK TRAN
ELSE
BEGIN
COMMIT TRAN
SELECT @msg = 'Database ' + @dbname + ' status reset!'
PRINT @msg
PRINT ''
PRINT 'WARNING: You must reboot SQL Server prior to '
PRINT ' accessing this database!'
PRINT ''
END
GO

 

3. sp_add_log_file_recover_suspect_db：

由于数据库上”日志空间不足”(9002) 错误造成恢复不能完成时，请将日志文件添加到文件组中。添加日志文件后，该存储过程关闭置疑设置并完成数据库的故障恢复。参数与 ALTER DATABASE ADD LOG FILE 中的参数相同。

重要 此存储过程应按故障诊断恢复部分中的描述使用。

 

1） 语法

sp_add_log_file_recover_suspect_db [ @dbName = ] ‘database’ ,

[ @name = ] ‘logical_file_name’ ,

[ @filename = ] ‘os_file_name’ ,

[ @size = ] ‘size’ ,

[ @maxsize = ] ‘max_size’ ,

[ @filegrowth = ] ‘growth_increment’

 

2） 参数

[@dbName =] ‘database’

是数据库名。database 的数据类型为 sysname，没有默认设置。

 

[@name =] ‘logical_file_name’

在 Microsoft SQL Server 中引用文件时的名称。此名称在服务器上必须唯一，logical_file_name 的数据类型为 nvarchar(260)，没有默认设置。

 

[@filename =] ‘os_file_name’

由操作系统使用的文件的路径和文件名。此文件必须驻留在安装有 SQL Server 的服务器上，os_file_name 的数据类型为 nvarchar(260)，没有默认设置。

 

[@size =] ‘size’

文件的初始大小。可使用 MB 和 KB 后缀指定兆字节或千字节。默认值为 MB。指定一个整数，不要包含小数位。文件大小的最小值是 512 KB，如果没有指定大小，则默认设置为 1 MB。size 的数据类型为 nvarchar(20)，默认设置为 NULL。

 

[@maxsize =] ‘max_size’

文件可以增长到的最大大小。可使用 MB 和 KB 后缀指定兆字节或千字节。默认值为 MB。指定一个整数，不要包含小数位。如果 max_size 没有被指定，则文件将增长到磁盘充满为止。当磁盘快要变满时，Microsoft Windows NT? 应用程序日志会警告管理员。max_size 的数据类型为 nvarchar(20)，默认设置为 NULL。

 

[@filegrowth =] ‘growth_increment’

每次需要新的空间时为文件添加的空间大小。0 值表示不增长。该值可用 MB、KB 或百分比 (%) 来指定。指定一个整数，不要包含小数位。当指定 % 时，增长增量是此增量发生时文件大小的指定百分比。如果未在数量后面指定 MB、KB 或 %，则默认值为 MB。如果没有指定 growth_increment，则默认设置为 10%，且最小值为 64 KB。指定的大小四舍五入到最接近 64 KB。growth_increment 的数据类型为 nvarchar(20)，默认设置为 NULL。

 

3） 返回代码值

0（成功）或 1（失败）

 

4） 结果集

无

 

5） 权限

执行权限默认赋予 sysadmin 固定服务器角色的成员。这些权限是不可传递的。

 

6） 示例

在此示例中，数据库 db1 在故障恢复期间由于日志空间不足（错误 9002）而标记为置疑。

 

sp_add_log_file_recover_suspect_db db1, logfile2,
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\db1_logfile2.ldf',
'1MB'

 

4. DBCC 中的 DBRECOVER 命令：

此命令在特定数据库上运行恢复进程，前滚提交的事务，回滚失败的事务。此命令有两个参数，可选参数“IgnoreErrors”，在恢复过程中忽略错误。

如：

 

DBCC DBRECOVER('ProdDB')

 

结果集：

Transactions rolled forward in database ‘ProdDB'(8). 0 transactions rolled back in database ‘ProdDB'(8).

 

 

二、损坏的数据库

1. 思路介绍：

数据库被标记为损坏的这种情况是很罕见的，但是万一发生了这种情况，你可以使用 sp_resetstatus 存储过程来使数据库摆脱损坏状态。数据库可能因为多种原因而被标记为损坏的。一般情况下，这是出现以下一种或多种条件的结果：

◇ 数据库或日志文件丢失。

◇ SQL Server 可能没有足够的时间来恢复数据库。

◇ 数据库中的数据页可能被损坏。

 

一旦有证据表明数据库损坏了，及时维护是至关重要的，否则，这个损坏就会在数据库中传播到其他页。要解决这个问题，请执行以下步骤：

① 检查 SQL Server 和 Windows NT 错误日志，看是否能找出问题所在。例如，可能硬盘驱动器空间已满。

② 以单用户模式启动 SQL Server。

③ 用 @dbname 参数执行 sp_resetstatus （例如, sp_resetstatus @dbname = “pubs”）。

④ 以单用户模式重新启动 SQL Server。

⑤ 如果数据库仍处于置疑模式，可将它重设回正常模式，并试着用下面的命令转储置疑的数据库的事务：

 

DUMP TRANSACTION Northwind WITH NO_LOG

 

⑥ 再次以单用户模式重新启动 SQL Server，如果数据库出现了。可对其进行详细的 DBCC 检查 （CHECKDB，CHECKALLOC，等等）。

⑦ 运行一些随机查询，看看是否会遇到问题。

⑧ 如果没有问题出现，可停止并重新启动 SQL Server，然后将数据库投入生产应用。

 

如果在启动 SQL Server 后，它仍然显示数据库是损坏的，那应该如何办呢？有时你可能必须接受损失，从最后已知的良好备份还原数据库，以使终端用户能重新连接到数据库。如果无法从备份还原，下一步就是将数据库置于紧急模式，以便把数据复制出来。

（备注：务必记住，即使能够成功地将数据从损坏的数据库中复制出来，这些数据也可能是不正确的，或者是已经损坏的。）

 

要将数据库置于紧急模式，可使用下面的命令：

 

sp_configure 'allow updates ', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases SET status = -32768 WHERE name = 'pubs '
GO
Sp_configure 'allow updates ', 0
RECONFIGURE WITH OVERRIDE
GO

 

(备注：状态值为-32768)

clip_p_w_picpath002

 

运行这个命令之后，数据库会以“只读（Read-Only）”\“脱机（Offline）”\“紧急（Emergency）”模式出现在企业管理器中。当数据库处于这种模式时，你只能从中读取数据。如果试图更新任何值，你会收到下面的错误信息：

 

Server: Msg 3908, Level 16, Status 1, Line 1

Could not run BEGIN TRANSACTION in database ‘pubs’

Because the database is in bypass reconvery mode.

The statement has been terminated.

（备注：Microsoft 不支持把数据库置于“紧急”模式，并且这样做可能对数据库造成潜在的损害。）

 

在找回数据并对新数据库进行了备份之后，请将状态重设为正常模式，并使用下面的命令以常规模式重新启动 SQL Server：

 

sp_configure 'allow updates ', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases SET status = 0 WHERE name = 'pubs '
GO
Sp_configure 'allow updates ', 0
RECONFIGURE WITH OVERRIDE
GO

 

表损坏的情况尽管不多见，但也是会出现的。表或者数据库损坏通常是因为 SQL Server 的突然停止而造成的。当你发现问题时，可首先运行 DBCC CHECKTABLE 来确定问题的严重程度。 SQL Server 不允许删除损坏的表，因为可能已经发生了某些分配错误。这意味着如果删除了一个表，你也可能同时删除了其他表的数据页。

 

要解决这个问题，可执行下面的任意一个步骤：

◇ 从最后已知的良好备份中还原。

◇ 把数据和模式从损坏的表复制到一个单独的数据库。用另外的名称重命名损坏的表，然后将新数据库重命名为旧数据库的名称。

 

2. 经验：

遇到置疑的数据库，尽量不要分离，以防数据无法找回。

遇到无法复制的数据，可能是硬盘坏了，此时只能通过导出数据并迁移服务器的方式解决。

慎用建同名库替换数据文件，重建日志的方法。

如果做好了数据库的备份，那么可以再数据文件遭到破坏时，通过日志文件尽可能地恢复数据。

 

3. 流程建议：

1） 检查 SQL Server 和 Windows NT 错误日志，看是否能找出问题所在。

例如：磁盘驱动器空间已满，非正常关机，磁盘损坏等。

 

2） 停掉SQL Server服务，备份置疑数据库的数据文件和日志文件，再重启服务。

   

3） 检查备份的完整性和连续性。

   

4） 做尾日志备份。在生产数据库损坏时，用 NO_TRUNCATE 从句备份事务日志。

--尾日志备份
BACKUP LOG ProdDB
TO prodbackupdev
WITH NAME = 'Tail-Log Backup', NO_TRUNCATE;
GO

 

5） 如果数据库完整性要求很高，且备份完整。通过备份序列恢复到故障点。

--恢复完整备份
RESTORE DATABASE ProdDB
FROM prodbackupdev
WITH FILE = 14, NORECOVERY;
GO
--恢复日志备份
RESTORE LOG ProdDB
FROM prodbackupdev
WITH FILE = 15, NORECOVERY;
GO
--恢复尾日志备份到故障点
RESTORE LOG ProdDB
FROM prodbackupdev
WITH FILE = 16, RECOVERY;
GO

 

6） 如果磁盘可用空间不足，而不能完成数据库的恢复，报9002或1105错误，通过重置置疑数据库的状态来恢复。

① 释放磁盘空间

② 单用户模式启动 SQL Server，命令提示符下运行

SQLSERVER -f –m

备注：-m 开关以单用户模式启动 SQL Server。在单用户模式下，只能成功建立一个连接。

③ sysadmin 角色成员登陆，重置置疑数据库的状态

 

USE master
GO
--设置允许修改系统表
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
--设置为置疑状态
UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = 'Database_Name'
--重置置疑状态
sp_resetstatus 'ProdDB'
GO
--恢复原值
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

 

(备注：由于该过程修改了系统表，系统管理员必须在创建这个过程前，启用系统表更新。)

 

下面是结果集：

Database’ProdDB’status reset!

WARNING: You must reboot SQL Server prior to accessing this database!

 

④ 用 ALTER DATABASE 向数据库添加一个数据文件或日志文件

⑤ 停止并重新启动 SQL Server。用新的数据文件或日志文件所提供的额外空间，SQL Server 应该能完成数据库的恢复。  
⑥ 若不行，执行 DBCC DBRECOVER 前滚提交的事务，回滚未提交的事务，此命令含有两个参数，可选的 IgnoreErrors 标记。

 

DBCC DBRECOVER('ProdDB')

 

⑦ 若不行，执行 sp_detach_db 分离数据库，再执行 sp_attach_db 附加数据库。

⑧ 若不行，向置疑数据库添加一个日志文件，然后执行

 

sp_add_log_file_recover_suspect_db

 

以便在数据库上运行恢复操作。

 

7） 其他情况下，先做数据库一致性检查，视情况修复。

① DBCC CHECKDB检查

USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysdatabases SET status = 32768 WHERE name = 'ProdDB'
GO
sp_dboption 'ProdDB', 'single user', 'true'
GO
DBCC CHECKDB('ProdDB')
GO
UPDATE sysdatabases SET status = 28 WHERE name = 'ProdDB'
GO
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
Go
sp_dboption 'ProdDB', 'single user', 'false'
Go

 

② 视错误类型，选择相应的级别参数来修复

REPAIR_FAST | REPAIR_REBUILD | REPAIR_ALLOW_DATA_LOSS

REPAIR_FAST：是简单快速的修复方式，修复非聚集索引中的次关键问题。

REPAIR_REBUILD：是简单快速的修复方式，提供 REPAIR_FAST 的全部特性，此外，还重建有问题的索引。

REPAIR_ALLOW_DATA_LOSS：是比较高级的修复方式，修复主要问题并允许 SQL Server 删除损坏的文本对象。

（备注：执行 REPAIR_REBUILD 和 REPAIR_FAST 没有丢失数据的风险。）

 

8） 如果依然置疑，通过删除日志文件，重建日志文件的方式

① 停掉 SQL Server 服务，删除掉源目录下的日志文件，再重启。

② 修改数据库为紧急模式。

USE Master
GO
UPDATE sysdatabases SET status = 32768 WHERE name='ProdDB'
GO
SHUTDOWN WITH NOWAIT
GO

 

③ 退出 SQL SERVER，在命令行模式中通过下面的代码重新启动 SQL Server 服务。

–安全模式启动SQL SERVER

sqlservr -c -T3608 -T4022

④ 在查询分析器中执行以下语句来查看刚刚修改过状态的数据库状态。

 

SELECT name,status FROM sysdatabases WHERE name='ProdDB'

 

⑤ 执行以下代码新建日志文件。

 

–跟踪

DBCC TRACEON(3604)

–文件名要有完整路径和扩展名

DBCC REBUILD_LOG(‘数据库名称’,’日志文件完整路径’)

如：

DBCC REBUILD_LOG('ProdDB','D:\MSSQL\Data\ProdDB_Log.ldf')

 

⑥ 将数据库置回正常状态

 

UPDATE sysdatabases SET status=0 WHERE name='ProdDB'

 

⑦ 重新启动数据库后执行以下语句检查数据库

 

DBCC CHECKDB('ProdDB')

 

⑧ 如果有错误，继续修复。要修复数据库必需将数据库改为单用户模式。

 

sp_dboption 'ProdDB','single user','true'

 

⑨ 视错误类型，选择相应的级别参数来修复

REPAIR_FAST | REPAIR_REBUILD | REPAIR_ALLOW_DATA_LOSS

REPAIR_FAST：是简单快速的修复方式，修复非聚集索引中的次关键问题。

REPAIR_REBUILD：是简单快速的修复方式，提供 REPAIR_FAST 的全部特性，此外，还重建有问题的索引。

REPAIR_ALLOW_DATA_LOSS：是比较高级的修复方式，修复主要问题并允许 SQL Server 删除损坏的文本对象。

（备注：执行 REPAIR_REBUILD 和 REPAIR_FAST 没有丢失数据的风险。）

 

9） 删除置疑数据库，通过文件附加的方式

① 删除掉置疑数据库，将备份数据文件和日志文件拷贝到源目录下。

② 查询分析器执行附加脚本。

sp_attach_db [ @dbname = ] ‘dbname’

, [ @filename1 = ] ‘filename_n’ [ ,…16 ]

如：

 

EXEC sp_attach_db @dbname = N'ProdDB',
@filename1 = N'D:\MSSQL\Data\ProdDB.mdf',
@filename2 = N'D:\MSSQL\Data\ProdDB_Log.ldf'

 

③ 如果没有恢复，删除日志文件，单文件附加，生成日志文件的方式

sp_attach_single_file_db [ @dbname = ] ‘dbname’

, [ @physname = ] ‘physical_name’

如：

 

EXEC sp_attach_single_file_db @dbname = N'ProdDB',
@physname = N'D:\MSSQL\Data\ProdDB.mdf'

 

10） 如果仍然置疑，通过新建同名数据库、替换数据文件、重建日志文件的方式（只有mdf文件的恢复技术）

① 新建一个同名数据库（文件名、文件组都和原来的一样），然后停止 SQL Server 服务，用原置疑数据库的数据文件替换掉新建的数据文件，删掉日志文件，启动数据库，该数据库显示置疑状态。

② 设置允许对系统表直接操作。

 

USE master
GO
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO

 

③ 设置为紧急恢复模式。

 

UPDATE sysdatabases SET status = -32768 WHERE dbid=DB_ID('ProdDB')

 

此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读\置疑\脱机\紧急模式”可以看到数据库里面的表，但是仅仅有系统表

④ 下面执行真正的恢复操作，重建数据库日志文件

 

–文件名要有完整路径和扩展名

DBCC REBUILD_LOG(‘数据库名称’,’日志文件完整路径’)

如：

 

DBCC REBUILD_LOG('ProdDB','D:\MSSQL\Data\ProdDB_Log.ldf')

 

⑤ 验证数据库一致性

 

DBCC CHECKDB('ProdDB')

 

⑥ 设置数据库为正常状态

 

sp_dboption 'test','dbo use only','false'
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO

 

11） 以上系列操作到正常模式后，通过 DBCC CHECKDB 3项可选参数依然无法修复时，抢救数据。

① 直观法：

通过企业管理器-导出数据，导出到一个新库，选择相应的导出对象，如：视图、表、存储过程等，并注意包含扩展属性。数据导出完成后，删除原库，重命名新库为原库名。

对于标示导入失败的表，重新导入，如果还不行，就BCP导入。

 

② BCP法：

利用 SQL Server 数据库的 sysobjects 表可以生成批量的BCP命令。

在D:\recovery下建立目录BCP，再在D:\recovery\BCP下建立error目录。

从数据库中各表导出所有表的数据：

 

USE %1
SELECT 'BCP %1..' + name + ' out D:\recovery\BCP\' +name + '.txt -c -S%2 -U%3 -P%4 >D:\recovery\BCP\error\out_'+name+'.txt' FROM sysobjects WHERE type = 'U' ORDER BY name

 

从数据文件导回到数据库中的各表：

 

USE %1
SELECT 'BCP %1..' + name + ' in D:\recovery\BCP\' +name + '.txt -c -S%2 -U%3 -P%4 >D:\recovery\BCP\error\in_'+name+'.txt' FROM sysobjects WHERE type = 'U' ORDER BY name

 

注意：type = “U”，U必须大写，表用户自定义表。-S服务器，-P密码，%1为数据库，%2为本机SQL Server实例名，一般就是计算机名，%3为登录账户，一般为sa，%4为登录密码。

 

具体示例：

 

SELECT 'BCP ProdDB..' + name + ' out D:\recovery\BCP\' +name + '.txt -c –S(local) -Usa -P123456 >D:\recovery\BCP\error\out_'+name+'.txt' FROM sysobjects WHERE type = 'U' ORDER BY name

 

将上述SQL脚本的查询结果全选后另存为批处理文件ProdDB_Out.bat。用于从数据库ProdDB中各表导出以表名命名的txt文件：

 

SELECT 'BCP ProdDB_New..' + name + ' in D:\recovery\BCP\' +name + '.txt -c –S(local) -Usa -P123456 >D:\recovery\BCP\error\in _'+name+'.txt' FROM sysobjects WHERE type = 'U' ORDER BY name

 

将上述SQL脚本的查询结果全选后另存为批处理文件ProdDB_In.bat。用于将上述各表的txt文件导回到数据库ProdDB。执行ProdDB_In.bat，执行完毕后要检查D:\recovery\BCP\error下的每个out_表名.txt文件，查看是否有误。当然导入后要检查in_表名.txt文件，查看是否有误,这是必须的。

 

新建ProdDB_New要保证跟ProdDB一样的表结构（如主键、索引等，本文不再赘述，本文是从ProdDB中生成SQL脚本，然后新建数据库ProdDB_New后执行创建表、主键、索引脚本。当然一般还有视图、存储过程等加密过的，是不能导出为SQL脚本，那得想其它的办法，还原备份文件来实现）。

 

执行ProdDB_In.bat，执行完毕后要检查D:\recovery\BCP\error每个in_表名.txt文件。如果遇到表导入失败，分段导入。恢复的时候一定要细心，并且记录关键步骤的操作内容，否则做到哪一步，自己都晕菜了，更不用说成功地恢复数据库了！如果在ProdDB_New数据库中能看到数据，那就是大功告成了。不过不要得意哦，记得要备份数据库哦。

 

最后，有个问题要说明一下：如果表间有关联，使用BCP恢复起来就麻烦一点。一般的做法可以把外键之类的表间关系去掉，等导入数据后在用SQL脚本重新创建这些关系。这个做法数据的有效性得由我们自己去判断了，如果有问题还得手工修改（一般需要开发人员协助，因为只有他们才能去查代码）。