SQL server数据库的数据恢复（来自实际案例和网上知识）

SQL server 数据库的数据恢复（来自实际案例和网上知识）

 1）Full Backup（全备份）
 将用户设定的整个目录或文件全部备份。
 2)Differential Incremental Backup（增量备份）
 备份上一次备份以来，更新过的文件。不管上一次备份是全备份、增量备份，还是累积备份。
 3）Cumulative Incremental Backup（累积备份）
 备份上一次全备份以来更新过的数据。
 
 如何来进行备份？快速恢复的步骤？如何定义策略和备份窗口

 

 master数据库在整体上控制SQL SERVER的所有方面。这个数据库中包括所有的配置信息、用户登录信息、当前服务器中运行的信息等。
是整个系统中最重要的数据库。master数据库被损坏，可能导致sql server实例无法启动，所以在创建了任何用户定义的对象后都要备份它。

一、master数据库损坏，无法打开实例，必须要重建master数据库
 重建master数据库前提：
  1、曾经备份过master数据库
  2、混合模式
  3、启用sa账户
  4、sql server安装盘
  
  cmd命令下：
  cd  *：program files\microsoft sql server\MSSQL\MSSQLbinn
  start /wait d:\setup.exe /qn INSTANCENAME="XXXX"  REINSTALL=SQL_ENGINE REBUILDDATABASE=1 SAPWD=XXXXXX
  
  sqlserv -c -m 输入命令行进单用户模式，还原整个数据库的master数据库，刚才只是重建了一个新的数据库
  
  启动企业管理器，sa登录，断开连接---新建sa----还原master数据库
  use master
  go
  restore database master from disk='c:\program files\microsoft sql server\mssql.1\mssql\backup\master.bak'
  //备份数据库文件路径
  with replace 

 
二、数据库置疑模式
 use master
 go
 sp_configure 'ALLOWUPDATES',1 RECONFIGURE WITH OVERRIDE
 GO
 UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='jhsy'  //jhsy为dbname
 go

 sp_dboption 'jhsy','single user','true'
 go

 dbcc checkdb('jhsy',repair_fast)
 go

 dbcc checktable('sysobjects')
 go
 dbcc checktable('sysindexes')
 go
 dbcc checktable('syscolumns')
 go

 

 use master
 go
 sp_configure 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
 GO
 UPDATE SYSDATABASES SET STATUS =-32768 WHERE NAME='jhsy'
 go

 use master
 go
 sp_dboption 'jhsy',single,true
 go

 dbcc rebuild_log('jhsy','c:\sqldata\jhsy_Log.LDF')
 go

 dbcc checkdb('jhsy')
 go

 dbcc checkdb('jhsy',repair_allow_data_loss)
 go

 update sysdatabases set status=-32768 where dbid=DB_ID('jhsy')
 go

 update sysdatabases set status=0 where name='jhsy'
 go

 restore database jhsy with recovery
 go
 exec sp_configure 'allow update',0 reconfigure with override
 go

 sp_dboption 'jhsy','single user','false'
 go

 

 use master
 go

 alter database jhsy set single_user with rollback immediate
 go

 sp_dboption 'jhsy','single user','false'
 go
 
 dbcc checkdb('jhsy')
 go
 dbcc checktable('sysobjects',repair_fast)
 go

 exec sp_configure 'allow update',1 reconfigure with override
 go

 update master.dbo.sysdatabases set status=16 where name='jhsy'
 go

 dbcc checkdb('jhsy')
 go
 
 
三、数据库只读、单用户、紧急状态
 1、将数据库设置成紧急状态
  sp_configure 'allow',1 reconfigure with override
  update sysdatabases set status =32768 where name='db'
 2、重建日志文件
  dbcc rebuild_log('db','d:\mssql\data\db_log.ldf')
 3、取消紧急模式
  update sysdatabases set status=0 where name='db'
  restore database db with recovery
  sp_configure 'allow',0 reconfigure with override
 
 
四、数据库的备份及恢复
 master数据库、model数据库及msdb数据库的备份和恢复
 model数据库和msdb数据库出现置疑时，由于这是基础库，所以不能删除
 解决方案1：
 use master
 go
 sp_configure 'allow updates',1 reconfigure with override
 go
 update sysdatabases set status=32768 where name='msdb'
 go
 sp_dboption 'msdb','single user','true'
 go
 dbcc checkdb('msdb')
 go
 update sysdatabases set status=28 where name='msdb'
 sp_configure 'allow updates',0 reconfigure with override
 go
 sp_dboption 'msdb','single user','false'
 go
 
 方法2：
 1停止数据库，将msdb的mdf和ldf文件复制到其他目录下；多备一份
 2将复制出来的文件，附加到一个新的数据库上去如msdb1
 3、将msdb1数据库进行一份备份
 4、选择置疑的msdb，选择恢复数据库，将刚才备份出来的数据库强行恢复到msdb上。
 
 
SQL SERVER系统表损坏的处理方法：
1、SQL SERVER中三张重要的系统表
 sysobjects:在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行
 sysindexes：数据库中的每个索引和表在表中各占一行
 syscolumns:每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行
 这3张表用ID（表ID）字段关联，这3张系统表一旦损坏，与之对应数据库对象将无法访问，其作用相当于DOS中FAT
 
2、系统表损坏的症状
  DBCC CHECKDB携带任何参数都无法修复数据库
  这3张表无法执行查询操作如select *　from sysobjects
  无法用SQL DTS或其他SQL脚本导库工具进行导库，导库的中途失败，连接中断
  在企业管理器或查询分析器中，部分用户数据表无法访问
  检测：
  dbcc checkdb('dbname')
  go
  看显示情况
  
  执行
  select * from sysobjects
  select * from sysindexes
  select * from syscolumns
  看是否正常
  
  
3、处理方法：
  第一步：处理可以访问的数据表
  ⑴找出哪些表不可访问，即系统表中哪些记录损坏
  新建一库，实体名为NEW，进入查询分析器，执行如下sql（深入理解）：
  use old//使用旧库
  declare @TbName VARCHAR(80)
  DECLARE FindErrTable SCROLL CURSOR FOR
   select name from new.dbo.sysobjects where xtype='u' order by name
  OPEN FindErrTable
  FETCH findErrTable INTO @TbName
   WHILE @@FETCH_STATUS<>-1
    BEGIN
     printf @TbName
     exec('select top 1 * from '+@TbName)
     FETCH  FindErrTable INTO @TbName
    END
  PRINT "SCAN COMPLATE"
  CLOSE FindErrTable
  DEALLOCATE FindErrTable
  
  会有提示：
  假设是jhsytable
  并显示未能在索引页中找到RID.....的索引条目（索引ID 0，数据库为）
  
  根据以上报告可以知道jhsytable表在sysobjects表中的对应记录出错，造成jhsytable不能访问。修改
  上面的SQL：在声明游标的记录集中屏蔽jhsytable表
  
  DECLARE FindErrTable SCROLL CUROSR FOR
     select name from new.dbo.sysobjects where xtype='u' and name!='jhsytable' order by name
    
     修改完毕，继续执行此SQL，如此反复，就能够不断报告出sysobjects中哪些表不能访问。
 
  
  ⑵用sql dts把能够访问的用户数据表导入一个新的database。在导库时，要避开已经损坏的数据库
  第二步：处理不可访问的数据表
  ⑴找出系统表中错误记录的ID；
  获得old库中jhsytable表在sysobjects中的id
  select id from old.dbo.sysobjects where name='jhsytable'===>456
  通常即使sysobjects表损坏，不能做select *　from sysobjects查询，但可以做select id,name from sysobjects查询
  。如果也不行，可以对照new和order两个数据库的同名表：syscolumns。根据old.dbo.syscolumns中找出jhsytable所 占得字段的个数
  以及各个字段的名称，在old.dbo.syscolumns中找出jhsytable所对应的记录，由此获得该table在old库的系统表中所分配的id
  
  select id from new.dbo.sysobjects where name="jhsytable"====>789
  
  
  ⑵根据错误记录的ID,删除sysobjects、sysindexes、syscolumns表错误的记录；
  DELETE OLD.dbo.sysobjects where id=456;
  DELETE OLD.dbo.sysindexes where id=456;
  DELETE OLD.dbo.syscolumns where id=456;
  
  
  ⑶根据错误纪录的ID，重建系统表记录
  
  重建old.dbo.sysobjects表中jhsytable表对应的记录
  INSERT INTO OLD.dbo.sysobjects(name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid)
  SELECT
   'jhsytable_b'，456，xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid
  FROM new.dbo.sysobjects where id=78   //jhsytable_b
  重建old.dbo.sysindexes表中jhsytable表对应的记录
  INSERT INTO old.dbo.sysindexes(id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rown odctr,reserved3,reserved4,xmaxlen,maxirow,origFillFactor,StatVersion,reserved2,FirstIAM,impid,lockflags,pgmodctr,key,name,statblob)
  SELECT
  123,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rown odctr,reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,name,statblob
  From new.dbo.sysindexes where id=789
  
  重建old.dbo.syscolumns表中的jhsytable表对应的记录：
  INSERT INTO old.dbo.syscolumens(name,id,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserve d,
  colstat,cdefault,domain,number,colorderby,autoval,offset,collationid,language)
  SELECT
  name,123,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserved,colstat,cdefault,domain,number,colorderby,autoval,offset,collationid,language
  FROM new.dbo.syscolumns where id=789
  
  
  
  ⑷重建完毕，如果该表可以访问，那么用DTS单独将此表导入新的database
  经过以上操作后，old中jhsytalbe与jhsytable_b共用同一个ID。b表也继承了原表中的全部数据。
  insert into new.dbo.jhsytable from old.dbo.jhsytable_b
  用户表扇区出问题，修复系统表并不能挽回用户数据
  

  
  
3624 msg
位置： q:\sphinx\ntdbms\storeng\drs\include\record.inl：SQLServerVersion
表达式： m_SizeRec > 0 & & m_SizeRec < = MAXDATAROW SPID: ProcessID
进程 ID： SQLServerEXEProcessID

1.进行磁盘扫描和碎片整理
2.安装SQL SERVER 最新的 Service Pack 和 补丁
3.执行 DBCC CHECKDB(‘数据库名’)
4.如果DBCC报错，需要在单用户模式下，进行数据库的修复，或者建议使用以前的正常备份，进行恢复

  
sql server 数据库的错误修复

DBCC   CHECKTABLE   ( 'bom_detail')

DBCC CHECKDB

服务器: 消息 7995，级别 16，状态 1，行 1
数据库 'cpxt' 在 sysobjects、sysindexes、syscolumns 或 systypes 中存在一致性错误，妨碍了进一步的 CHECKDB 处理。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

use master
sp_dboption cpxt, single, true   //数据库的单用户模式
use cpxt
dbcc checktable('syscolumns',REPAIR_ALLOW_DATA_LOSS)

dbcc checktable('sysobjects',REPAIR_ALLOW_DATA_LOSS)
dbcc checktable('syscolumns')
dbcc checktable('bom_detail')

------------------------------------------------------------------------------------------------------------------------

快速修复
DBCC CHECKDB ('数据库名', REPAIR_FAST)     
重建索引并修复
DBCC CHECKDB ('数据库名', REPAIR_REBUILD)
如果必要允许丢失数据修复
DBCC CHECKDB ('数据库名'', REPAIR_ALLOW_DATA_LOSS)

如果出现错误：未处理修复语句。数据库需处于单用户模式下。

可以先启用单用户模式，方法如下执行存储过程：

Use master
go
sp_dboption 数据库名, single, true

-----------------------------------------------------------------------------------------------------------

 

Transact-SQL 编程语言提供 DBCC 语句作为 Microsoft&reg; SQL Server? 2000 的数据库控制台命令。这些语句对数据库的物理和逻辑一致性进行检查。许多 DBCC 语句能够对检测到的问题进行修复。
数据库控制台命令语句被分为以下类别。
语句分类                               执行
维护语句        对数据库、索引或文件组进行维护的任务。 
杂项语句        诸如启用行级锁定或从内存中删除动态链接库 (DLL) 等杂项任务。
状态语句        状态检查。
验证语句        对数据库、表、索引、目录、文件组、系统表或数据库页的分配进行的验证操作。

SQL Server 2000 的 DBCC 语句使用输入参数和返回值。所有 DBCC 语句参数都可以接受 Unicode 和 DBCS 字面值。
使用 DBCC 结果集输出
许多 DBCC 命令可以产生表格格式的输出（使用 WITH TABLERESULTS 选项）。该信息可装载到表中以便将来使用。以下显示一个示例脚本：
-- Create the table to accept the results
CREATE TABLE #tracestatus (
TraceFlag INT,
Status INT
)
-- Execute the command, putting the results in the table
INSERT INTO #tracestatus
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
-- Display the results
SELECT *
FROM #tracestatus
GO
维护语句
DBCC DBREINDEX
DBCC DBREPAIR
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC UPDATEUSAGE
杂项语句
DBCC dllname (FREE)
DBCC HELP
DBCC PINTABLE
DBCC ROWLOCK
DBCC TRACEOFF
DBCC TRACEON
DBCC UNPINTABLE
状态语句
DBCC PINTABLE
DBCC OPENTRAN
DBCC OUTPUTBUFFER
DBCC PROCCACHE
DBCC SHOWCONTIG
DBCC SHOW_STATISTICS
DBCC SQLPERF
DBCC TRACESTATUS
DBCC USEROPTIONS
验证语句
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKCONSTRAINTS
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKIDENT
DBCC CHECKTABLE
DBCC NEWALLOC
 

alter database dbname set emergency

alter database dbname set_single_user
dbcc checkdb('dbname',REPAIR_ALLOW_DATA_LOSS)
alter database dbname set multi_user
alter database dbname set online

 

alter database dbname set emergency
go
alter database dbname set single_user with rollback immediate
go
use master
go
alter database dbname Rebuild Log on
(name=SuspectDB_log,filename=’D:\Log\SuspectDB_log.LDF’)
go
alter database dbname set multi_user
go

DBCC CHECKDB(‘dbname’)
go

 

use master
sp_dboption cpxt, single, true   //数据库的单用户模式
use cpxt
dbcc checktable('syscolumns',REPAIR_ALLOW_DATA_LOSS)

dbcc checktable('sysobjects',REPAIR_ALLOW_DATA_LOSS)
dbcc checktable('syscolumns')
dbcc checktable('bom_detail')

------------------------------------------------------------------------------------------------------------------------

快速修复
DBCC CHECKDB ('数据库名', REPAIR_FAST)     
重建索引并修复
DBCC CHECKDB ('数据库名', REPAIR_REBUILD)
如果必要允许丢失数据修复
DBCC CHECKDB ('数据库名'', REPAIR_ALLOW_DATA_LOSS)

如果出现错误：未处理修复语句。数据库需处于单用户模式下。

可以先启用单用户模式，方法如下执行存储过程：

Use master
go
sp_dboption 数据库名, single, true