SQL Server還原備份經典文檔

SQL備份 --------------- 1、SQL資料庫恢複模型 ---------------------------------------------------------------------- 1）完全恢複模型 ----------------- （1）備份時要備份資料庫的資料檔案和記錄檔 （2）還原時使用資料庫的備份的資料檔案副本和全部日誌資訊來恢複資料庫。 （3）能還原全部資料，並可以將資料庫恢複到任意指定的時刻。 （4）為保證實現即時間點復原，對資料庫的所有*作都將完整地記入日誌，這樣，日誌佔用空間較大，對效能也有所影響。 ------------------ （2）大容量日誌記錄恢複模型 ------------------ （1）備份時要備份資料庫的資料檔案和記錄檔 （2）還原時使用資料庫的備份的資料檔案副本和全部日誌資訊來恢複資料庫。 （3）日誌中不記錄*作細節（如select into、create index等），而只記錄*作的最終結果，因此佔用日誌空間小。 （4）只支援將資料庫還原到交易記錄備份的時刻，而不支援即時間點復原，因此可能產生資料丟失。 ------------------- （3）簡單恢複模型 ------------------- （1）備份時只備份資料檔案，還原時也用備份的資料檔案恢複資料庫。 （2）只能將資料恢複到資料檔案備份的時刻，可能產生最多的資料丟失。 （3）不適於生產系統和大規模*作環境下選用。 ----------------------------------------- alter database d1 set recovery simple    --設定資料庫恢複模型 alter database d1 set recovery bulk_logged alter database d1 set recovery full ---------------------------------------------------------------------- 2、備份裝置 ---------------------------------------------------------------------- 1）物理裝置 --------------------------- disk：支援本地磁碟或者網路備份 tape：支援磁帶機備份 name pipe：支援第三方備份軟體 --------------------------- 2）邏輯裝置 --------------------------- 永久備份檔案：可以重複使用，應該在備份前建立。 臨機操作備份檔案：用於一次性備份，在備份時建立。 ------------------------------------------------- exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --建立永久磁碟備份裝置 exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak' ---------------------------------------------------------------------- exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --建立網路永久磁碟備份裝置 exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak' ---------------------------------------------------------------------- exec sp_dropdevice 'bak5'              --刪除備份裝置 ---------------------------------------------------------------------- backup database d3 to bak3      --將Database Backup到備份裝置 backup database d4 to bak4 ---------------------------------------------------------------------- restore headeronly from bak2    --查看備份裝置中的內容 ---------------------------------------------------------------------- backup database d3 to disk='e:\back_file\d3.bak'    --將Database Backup到臨機操作備份檔案 backup database d4 to disk='e:\back_file\d4.bak' ---------------------------------------------------------------------- restore database d3 from bak3          --從備份裝置還原資料庫      restore database d4 from disk='e:\back_file\d4.bak' --從備份檔案還原資料庫 ---------------------------------------------------------------------- 3、使用多個備份檔案儲存備份 ---------------------------------------------------------------------- 1）SQL可同時向多個備份檔案進行寫*作。如果把這些檔案放到多個磁帶機或磁碟中，則可提高備份速度。 2）這多個備份檔案必須用同業型的媒體，並放到一個媒體集中。 3）媒體集中的檔案必須同時使用，而不能單獨使用。 4）可以通過format命令將媒體集重新劃分，但原備份組中的資料不能再使用。 -------------------------------------------------------------------- backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --備份D4並形成Media Set backup database d3 to bak4      --失敗，因Media set中檔案必須同時使用 backup database d3 to bak4,bak5,bak6    --成功，將D3也備份到Media Set中 restore headeronly from bak4,bak5,bak6--查看Media Set中的備份內容 ---------------------------------------------------------------------- backup database d4 to bak4 with medianame='bak4',format    --重新劃分Media Set backup database d3 to bak5,bak6 with medianame='bak56',format ---------------------------------------------------------------------- backup database d1 to bak1 with init    --with init重寫備份裝置中內容 backup database d2 to bak1 with noinit --with noinit將內容追加到備份裝置中 restore headeronly from bak1 ---------------------------------------------------------------------- 1）完全備份 ------------------------------------------- （1）是備份的基準。在做備份時第一次備份都建議使用完全備份。 （2）完全備份會備份資料庫的所有資料檔案、資料對象和資料。 （3）會備份交易記錄中任何未提交的事務。因為已提交的事務已經寫入資料檔案中。 -------------------------------------------- backup database d1 to bak1 with init    --完全備份 backup database d1 to bak1 with noinit ---------------------------------------------------------------------- 2）差異備份 --------------------------------------------- （1）基於完全備份。 （2）備份自最近一次完全備份以來的所有資料庫改變。 （3）恢複時，只應用最近一次完全備份和最新的差異備份。 ----------------------------------------------- backup database d2 to bak2 with init,name='d2_full' --差異備份，第一次備份時應做完全備份 create table b1(c1 int not null,c2 char(10) not null) backup database d2 to bak2 with differential,name='d2_diff1' insert b1 values(1,'a') backup database d2 to bak2 with differential,name='d2_diff2' insert b1 values(2,'b') backup database d2 to bak2 with differential,name='d2_diff3' insert b1 values(3,'c') backup database d2 to bak2 with differential,name='d2_diff4' restore headeronly from bak2 ---------------------------------------------------------------------- 3）交易記錄備份 ------------------------------------------------------------- （1）基於完全備份。 （2）為遞增備份，即備份從上一次備份以來到備份時所寫的交易記錄。 （3）允許恢複到故障時刻或者一個強制時間點。 （4）恢複時，需要應用完全備份和完全備份後的每次記錄備份。 ------------------------------------------------------------- backup database d3 to bak3 with init,name='d3_full' --記錄備份，第一次備份時應做完全備份 create table b1(c1 int not null,c2 char(10) not null) backup log d3 to bak3 with insert b1 values(1,'a') backup log d3 to bak3 with insert b1 values(2,'b') backup log d3 to bak3 with insert b1 values(3,'c') backup log d3 to bak3 with restore headeronly from bak3 ---------------------------------------------------------------------- create table b1(c1 int not null,c2 char(10) not null)    --Full+Log+Diff backup log d4 to bak4 with insert b1 values(1,'a') backup log d4 to bak4 with insert b1 values(2,'b') backup database d4 to bak4 with differential,name='d4_diff1' insert b1 values(3,'c') backup log d4 to bak4 with insert b1 values(4,'d') backup log d4 to bak4 with insert b1 values(5,'d') backup database d4 to bak4 with differential,name='d4_diff2' restore headeronly from bak4 ---------------------------------------------------------------------- 日誌清除 ----------------------------------------- 1）如果日誌空間被填滿，資料庫將不能記錄修改。 2）資料庫在做完全備份時日誌被截斷。 3）如果將'Trans log on checkpoint'選項設為TRUE，則結果為不儲存日誌，即沒有日誌記錄，不建議使用。 4）with truncate_only和with no_log設定日誌滿時清除日誌 5）with no_truncate則可以完整儲存日誌，不清除，即使在資料檔案已經損壞情況下。主要用於資料庫出問題後在恢複前使用。可以將資料還原到出故障的那一時刻。 ------------------------------------------- exec sp_dboption d3 exec sp_dboption sp_dboption 'd3','trunc. log on chkpt.','true'    --設定自動清除資料庫日誌 sp_dboption 'd3','trunc. log on chkpt.','false'    --將自動清除資料庫日誌的選項去除 ---------------------------------------------------------------------- backup log d4 with truncate_only    --設定D4日誌滿時清除日誌，並做清除記錄 ---------------------------------------------------------------------- backup log d4 with no_log      --設定D4日誌滿時清除日誌，但不做清除記錄 ---------------------------------------------------------------------- backup log d4 to bak4 with no_truncate--在D4資料庫損壞時馬上備份當前資料庫日誌(DEMO) -------- 使用no_truncate 完全+修改1+差異+修改2+差異+修改3+停止SQL，刪除資料庫資料檔案+重啟SQL backup log no_truncate 再還原，可還原到修改3 ---------------------------------------------------------------------- 4）檔案/檔案組備份 ------------------------------------------------------------------ （1）用於超大型資料庫。 （2）只備份選定的檔案或者檔案組。 （3）必須同時作記錄備份。 （4）還原時用檔案/檔案組備份和記錄備份進行還原。 （5）備份量少，恢複速度快。 create database d5      on primary (name=d5_data1, filename='e:\data\d5\d5_data1.mdf', size=2MB), filegroup FG2          --建立資料庫時建立filegroup FG2 (name=d5_data2, filename='e:\data\d5\d5_data2.ndf',    --並將檔案d5_data2放到FG2中 size=2Mb) log on (name=d5_log1, filename='e:\data\d5\d5_log1.ldf', size=2Mb) use d5 go alter database d5 add file (name=d5_data3, filename='e:\data\d5\d5_data5.ndf', size=2MB) to filegroup FG2          --將d5_data3加到檔案組FG2中 alter database d5 add filegroup FG3    --增加檔案組FG3 alter database d5          --將d5_data4加到檔案組FG2中 add file (name=d5_data4, filename='e:\data\d5\d5_data4.ndf', size=2MB) to filegroup FG3 sp_helpdb d5 create table t1(c1 int not null,c2 char(10) not null) on [primary] --將不同表放到不同filegroup中 create table t2(c1 int not null,c2 char(10) not null) on FG2 create table t3(c1 int not null,c2 char(10) not null) on FG3 ---------------------------------------------------------------------- backup database d5 to bak5 with init,name='d5_full'      --filegroup備份 backup database d5 filegroup='primary' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG2' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG3' to bak5 with backup log d5 to bak5 with ---------------------------------------------------------------------- backup database d5 to bak6 with init,name='d5_full'        --file備份 backup database d5 file='d5_data1' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data2' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data3' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data4' to bak6 with backup log d5 to bak6 with restore headeronly from bak6 ====================================================================== SQL還原 ====================================================================== 1、驗證備份 ------------------------------------------------------------ restore headeronly from bak3 restore filelistonly from bak3 with file=1 restore labelonly from bak3 restore verifyonly from bak3 ---------------------------------------------------------------------- 2、從備份中還原 ------------------------------------------------------------------------- restore headeronly from bak1 restore database d1 from bak1 with file=2        --從完全備份中恢複 ---------------------------------------------------------------------- restore headeronly from bak2              --從差異備份中恢複 restore database d2 from bak2 with file=1,norecovery    restore database d2 from bak2 with file=5,recovery ---------------------------------------------------------------------- restore headeronly from bak3              --從記錄備份中恢複 restore database d3 from bak3 with file=1,norecovery restore log    d3 from bak3 with file=2,norecovery restore log    d3 from bak3 with file=3,norecovery restore log    d3 from bak3 with file=4,norecovery restore log    d3 from bak3 with file=5,recovery ---------------------------------------------------------------------- restore database d3 from bak3 with file=1,norecovery      --恢複到指定時間 restore log    d3 from bak3 with file=2,norecovery restore log    d3 from bak3 with file=3,norecovery restore log    d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000' ---------------------------------------------------------------------- restore database d5 filegroup='FG2' from bak5 with file=4,norecovery --還原檔案組備份 restore log d5 from bak5 with file=5,norecovery restore log d5 from bak5 with file=7,recovery ---------------------------------------------------------------------- restore headeronly from bak6                --還原檔案備份 restore database d5 file='d5_data3' from bak6 with file=6,norecovery restore log d5 from bak6 with file=7,norecovery restore log d5 from bak6 with file=9,recovery ---------------------------------------------------------------------- restore database d5 from bak6 with replace    --刪除現有資料庫，從備份中重建資料庫 ---------------------------------------------------------------------- create database d6            --move to將資料庫檔案移動到新位置 on primary (name=d6_data, filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_Data.MDF', size=2MB) log on (name=d6_log, filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_log.ldf', size=2MB) go backupdatabase d6 to bak6 with init drop database d6 restore database d6 from bak6 with move 'd6_data' to 'e:\data\d6\d6_data.mdf', move 'd6_log'to 'e:\data\d6\d6_log.ldf' sp_helpdb d6 ---------------------------------------------------------------------- 3、分離與重串連資料庫 -------------------------------------- sp_detach_db 'd6'        sp_attach_db 'd6','e:\data\d6\d6_data.mdf','e:\data\d6\d6_log.ldf' -------------------------------------- sp_detach_db d6 go create database d6 on primary (filename='e:\data\d6\d6_data.mdf') for attach go ---------------------------------------------------------------------- 4、恢複損壞的系統資料庫 ---------------------------------------------------------------------- 1）先備份MASTER、MSDB 2）停止SQL服務，將MASTER資料庫檔案刪除或者重新命名。這樣，SQL服務將不能啟動。 3）系統資料庫的還原 ----------------------------------------------- （1）如果SQL服務還能啟動，則從備份中恢複系統資料庫。 （2）如果SQL服務不能啟動，則需要重建系統資料庫。 使用SQL檔案夾TOOLS\BINN目錄下的Rebuildm.exe重建master資料庫。 （3）建立備份裝置，指向以前的備份裝置。 （4）以單一使用者模式啟動SQL cd programe files\microsoft sql server\mssql\binn sqlservr.exe -c -m （5）進查詢分析器，從備份中恢複master資料庫。 restore database master from masterbak restore database msdb from disk='e:\bak\msdb.bak' MASTER還原後，SQL中使用者資料庫的資訊也會恢複。 （6）如果MASTER沒有備份，則需要用sp_attach_db命令將使用者資料庫附加到新的MASTER資料庫中。