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