SqlServer 備份和恢復(一)

SqlServer 備份和恢復

use chenjch 
select * into t1 from sys.objects;
select COUNT(*) from t1; ---54

---資料庫全備
BACKUP DATABASE chenjch
TO DISK='F:\backup\sqlserver\chenjch_full.bak'
GO

已為資料庫 'chenjch'，檔案 'chenjch' (位於檔案 1 上)處理了 192 頁。
已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 6 頁。
BACKUP DATABASE 成功處理了 198 頁，花費 0.175 秒(8.816 MB/秒)。

---資料庫恢復
insert into t1 select * from t1;
select COUNT(*) from t1; ---108

restore filelistonly from disk='F:\backup\sqlserver\chenjch_full.bak';

RESTORE DATABASE chenjch_0617
  FROM DISK = 'F:\backup\sqlserver\chenjch_full.bak' 
  WITH 
  MOVE 'chenjch' TO 'F:\backup\sqlserver\data\chenjch_0617.mdf',   
  MOVE 'chenjch_log' TO 'F:\backup\sqlserver\data\chenjch_0617_log.LDF';
  
已為資料庫 'chenjch_0617'，檔案 'chenjch' (位於檔案 1 上)處理了 192 頁。
已為資料庫 'chenjch_0617'，檔案 'chenjch_log' (位於檔案 1 上)處理了 6 頁。
RESTORE DATABASE 成功處理了 198 頁，花費 27.674 秒(0.055 MB/秒)。

use chenjch_0617 
select COUNT(*) from t1; ---54
只透過全備恢復資料庫，預設恢復到備份時刻的資料；

---資料庫日誌備份
BACKUP LOG chenjch TO DISK='F:\backup\sqlserver\chenjch_log.bak';

已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 22 頁。
BACKUP LOG 成功處理了 22 頁，花費 0.085 秒(2.022 MB/秒)。

---資料庫+日誌恢復
RESTORE DATABASE chenjch_0617_001
  FROM DISK = 'F:\backup\sqlserver\chenjch_full.bak' 
  WITH NORECOVERY,
  MOVE 'chenjch' TO 'F:\backup\sqlserver\data\chenjch_0617_001.mdf',   
  MOVE 'chenjch_log' TO 'F:\backup\sqlserver\data\chenjch_0617_001_log.LDF';
RESTORE LOG chenjch_0617_001 from disk='F:\backup\sqlserver\chenjch_log.bak' 
  WITH RECOVERY;

已為資料庫 'chenjch_0617_001'，檔案 'chenjch' (位於檔案 1 上)處理了 192 頁。
已為資料庫 'chenjch_0617_001'，檔案 'chenjch_log' (位於檔案 1 上)處理了 6 頁。
RESTORE DATABASE 成功處理了 198 頁，花費 27.525 秒(0.056 MB/秒)。
已為資料庫 'chenjch_0617_001'，檔案 'chenjch' (位於檔案 1 上)處理了 0 頁。
已為資料庫 'chenjch_0617_001'，檔案 'chenjch_log' (位於檔案 1 上)處理了 22 頁。
RESTORE LOG 成功處理了 22 頁，花費 0.085 秒(2.022 MB/秒)。

select COUNT(*) from t1; ---108 
資料庫全備+日誌 恢復，可以將資料庫恢復到最新狀態；

---基於時間點恢復
use chenjch
select * into t1 from sys.objects;
select COUNT(*) from t1; ---54

BACKUP DATABASE chenjch
TO DISK='F:\backup\sqlserver\chenjch_full001d.bak'
GO

已為資料庫 'chenjch'，檔案 'chenjch' (位於檔案 1 上)處理了 192 頁。
已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 2 頁。
BACKUP DATABASE 成功處理了 194 頁，花費 0.174 秒(8.688 MB/秒)。

---第一次刪除資料
delete t1 where TYPE='S';
--儲存刪除表的時間   
SELECT dt=GETDATE() INTO a;  
select * from a;  ---2018-06-18 14:17:56.387
select COUNT(*) from t1; ---9

---第二次刪除資料
delete t1;
--儲存刪除表的時間   
SELECT dt=GETDATE() INTO b;  
select * from b;  ---2018-06-18 14:21:53.940
select COUNT(*) from t1; ---0

測試將資料恢復到第一次刪除資料的時刻；
BACKUP LOG chenjch TO DISK='F:\backup\sqlserver\chenjch_log001d.bak';

已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 18 頁。
BACKUP LOG 成功處理了 18 頁，花費 0.036 秒(3.797 MB/秒)。

use master
RESTORE DATABASE chenjch FROM DISK='F:\backup\sqlserver\chenjch_full001c.bak'
WITH REPLACE,NORECOVERY;

已為資料庫 'chenjch'，檔案 'chenjch' (位於檔案 1 上)處理了 192 頁。
已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 2 頁。
RESTORE DATABASE 成功處理了 194 頁，花費 27.487 秒(0.054 MB/秒)。

RESTORE LOG chenjch FROM DISK='F:\backup\sqlserver\chenjch_log001d.bak' 
WITH RECOVERY,STOPAT='2018-06-18 14:18:00'; 

已為資料庫 'chenjch'，檔案 'chenjch' (位於檔案 1 上)處理了 0 頁。
已為資料庫 'chenjch'，檔案 'chenjch_log' (位於檔案 1 上)處理了 18 頁。
RESTORE LOG 成功處理了 18 頁，花費 0.086 秒(1.589 MB/秒)。

use chenjch
select count(*) from t1; ---9


SQLSERVER WITH選項如下：


WITH 選項
一：指定要用於備份操作的選項。
(1)CREDENTIAL
適用範圍： SQL Server（ SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2017）和 SQL 資料庫託管例項。
僅在建立到 Windows Azure Blob 儲存服務的備份時使用。

(2)DIFFERENTIAL
適用範圍： SQL Server。
只能與 BACKUP DATABASE 一起使用，指定資料庫備份或檔案備份應該只包含上次完整備份後更改的資料庫或檔案部分。 
差異備份一般會比完整備份佔用更少的空間。 
對於上一次完整備份後執行的所有單個日誌備份，使用該選項可以不必再進行備份。
預設情況下，BACKUP DATABASE 建立完整備份。

(3)ENCRYPTION
用於指定將備份加密。 
可指定加密備份所用的加密演算法，或指定 NO_ENCRYPTION 以不加密備份。 
建議進行加密以幫助保護備份檔案的安全。 
可指定的演算法的列表如下：
AES_128,AES_192,AES_256,TRIPLE_DES_3KEY,NO_ENCRYPTION

二：備份集選項
這些選項對此備份操作建立的備份集進行操作。
(1)COPY_ONLY 
適用範圍：
SQL Server 和 SQL 資料庫託管例項。
指定備份為“僅複製備份”，該備份不影響正常的備份順序。 
僅複製備份是獨立於定期計劃的常規備份而建立的。 
僅複製備份不會影響資料庫的總體備份和還原過程。
應在出於特殊目的而進行備份的情況下使用僅複製備份，例如在進行聯機檔案還原前備份日誌。 
通常，僅複製日誌備份僅使用一次即被刪除。
與 BACKUP DATABASE 一起使用時，COPY_ONLY 選項建立的完整備份不能用作差異基準。 
差異點陣圖不會被更新，因此差異備份的表現就像僅複製備份不存在一樣。 
後續差異備份將最新的常規完整備份用作它們的基準。
如果將 DIFFERENTIAL 和 COPY_ONLY 一起使用，則忽略 COPY_ONLY 並建立差異備份。
與 BACKUP LOG 一起使用時，COPY_ONLY 選項將建立“僅複製日誌備份”，該備份不會截斷事務日誌。 
僅複製日誌備份對日誌鏈沒有任何影響，因此其他日誌備份的表現就像僅複製備份不存在一樣。

(2){ COMPRESSION | NO_COMPRESSION }
僅適用於 SQL Server 2008 Enterprise 和更高版本；
指定是否對此備份執行備份壓縮，覆蓋伺服器級預設設定。

安裝時，預設行為是不進行備份壓縮。
但此預設設定可透過設定 backup compression default 伺服器配置選項進行更改。 
有關檢視此選項的當前值的資訊，請參閱檢視或更改伺服器屬性皮膚 (SQL Server)。

COMPRESSION
顯式啟用備份壓縮。
NO_COMPRESSION
顯式禁用備份壓縮。

(3)DESCRIPTION = { 'text' | @text_variable }*
指定說明備份集的自由格式文字。 該字串最長可達 255 個字元。

(4)NAME = { backup_set_name | @backup_set_var }
指定備份集的名稱。 名稱最長可達 128 個字元。 如果未指定 NAME，它將為空。

(5){ EXPIREDATE ='date' | RETAINDAYS = 天數 }
指定允許覆蓋該備份的備份集的日期。 
如果同時使用這兩個選項，RETAINDAYS 的優先順序別將高於 EXPIREDATE。
如果這兩個選項均未指定，則過期日期由 mediaretention 配置設定確定。 
有關詳細資訊，請參閱 伺服器配置選項 (SQL Server)版本的組合自動配置的最大工作執行緒數。

(6)RETAINDAYS = { days | @days_var } 
指定必須經過多少天才可以覆蓋該備份媒體集。如果作為變數 (@days_var) 提供，則必須指定為整數。

三：媒體集選項
這些選項作為一個整體對介質集進行操作。
(1){ NOINIT | INIT }
控制備份操作是追加到還是覆蓋備份介質中的現有備份集。 預設為追加到介質中最新的備份集 (NOINIT)。
NOINIT
表示備份集將追加到指定的介質集上，以保留現有的備份集。 如果為介質集定義了介質密碼，則必須提供密碼。 NOINIT 是預設設定。
INIT
指定應覆蓋所有備份集，但是保留介質標頭。 
如果指定了 INIT，將覆蓋該裝置上所有現有的備份集（如果條件允許）。 
預設情況下，BACKUP 將檢查下列條件，如果其中的任一條件存在，都不會覆蓋備份介質：
所有備份集都未過期。 有關詳細資訊，請參閱 EXPIREDATE 和 RETAINDAYS 選項
如果 BACKUP 語句給出了備份集名，則該備份集名與備份介質上的名稱不匹配。 有關詳細資訊，請參閱本部分前面介紹的 NAME 選項。

(2){ NOSKIP | SKIP }
控制備份操作是否在覆蓋介質中的備份集之前檢查它們的過期日期和時間。
NOSKIP
指示 BACKUP 語句在可以覆蓋介質上的所有備份集之前先檢查它們的過期日期。 這是預設行為。
SKIP
禁用備份集的過期和名稱檢查，這些檢查一般由 BACKUP 語句執行以防覆蓋備份集。

(3){ NOFORMAT | FORMAT }
指定是否應該在用於此備份操作的捲上寫入介質標頭，以覆蓋任何現有的介質標頭和備份集。
NOFORMAT
指定備份操作在用於此備份操作的介質捲上保留現的有介質標頭和備份集。 這是預設行為。
FORMAT
指定建立新的介質集。 FORMAT 將使備份操作在用於備份操作的所有介質捲上寫入新的介質標頭。 
卷的現有內容將變為無效，因為覆蓋了任何現有的介質標頭和備份集。
重要
請謹慎使用 FORMAT。 格式化介質集的任何一個卷都將使整個介質集不可用。 例如，如果初始化現有條帶介質集中的單個磁帶，則整個介質集都將變得不可用。
指定 FORMAT 即表示 SKIP；SKIP 無需顯式宣告。

(4)MEDIADESCRIPTION = { text | @text_variable }
指定介質集的自由格式文字說明，最多為 255 個字元。

(5)MEDIANAME = { media_name | @media_name_variable }
指定整個備份介質集的介質名稱。 
介質名稱的長度不能多於 128 個字元，如果指定了 MEDIANAME，則該名稱必須匹配備份捲上已存在的先前指定的介質名稱。 
如果未指定該選項或指定了 SKIP 選項，將不會對介質名稱進行驗證檢查。

(6)BLOCKSIZE = { blocksize | @blocksize_variable }
用位元組數來指定物理塊的大小。 
支援的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 位元組。 
對於磁帶裝置預設為 65536，其他情況為 512。 
通常，由於 BACKUP 自動選擇適合於裝置的塊大小，因此不需要此選項。 顯式宣告塊大小將覆蓋自動選擇塊大小。

如果要建立一個計劃在 CD-ROM 上進行復制和還原的備份，請指定 BLOCKSIZE=2048。
通常，只有寫入磁帶裝置時，此選項才會影響效能。

四：資料傳輸選項
BUFFERCOUNT = { buffercount | @buffercount_variable }
指定用於備份操作的 I/O 緩衝區總數。 可以指定任何正整數；但是，較大的緩衝區數可能導致由於 Sqlservr.exe 程式中的虛擬地址空間不足而發生“記憶體不足”錯誤。
緩衝區使用的總計空間由以下內容確定：buffercount/maxtransfersize

(2)MAXTRANSFERSIZE = { maxtransfersize | @* maxtransfersize_variable* } 
指定要在 SQL Server 和備份介質之間使用的最大傳輸單元（位元組）。 
可能的值是 65536 位元組 (64 KB) 的倍數，最多可到 4194304 位元組 (4 MB)。

五：錯誤管理選項
使用這些選項可以確定是否為備份操作啟用了備份校驗和，以及備份操作是否在遇到錯誤時停止。
(1){ NO_CHECKSUM | CHECKSUM }
控制是否啟用備份校驗和。
NO_CHECKSUM
顯式禁用備份校驗和的生成（以及頁校驗和的驗證）。 這是預設行為。
CHECKSUM
如果此選項已啟用並且可用，則指定備份操作將驗證每頁的校驗和及頁殘缺，並生成整個備份的校驗和。
使用備份校驗和可能會影響工作負荷以及備份吞吐量。

(3){ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
控制備份操作在遇到頁校驗和錯誤後是停止還是繼續。
STOP_ON_ERROR
如果未驗證頁校驗和，則指示 BACKUP 失敗。 這是預設行為。
CONTINUE_AFTER_ERROR
指示 BACKUP 繼續執行，不管是否遇到無效校驗和或頁撕裂之類的錯誤。
資料庫損壞時，如果無法使用 NO_TRUNCATE 選項備份日誌尾部，則可以透過指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 嘗試執行尾日誌備份。

六：相容性選項
RESTART
從 SQL Server 2008 開始不起作用。 此版本接受該選項，以便與舊版本的 SQL Server 保持相容。

七：監視選項
STATS [ = percentage ]
每當另一個百分比完成時顯示一條訊息，並用於測量進度。 如果省略百分比，則 SQL Server 在每完成 10% 就顯示一條訊息。
STATS 選項報告截止報告下一個間隔的閾值時的完成百分比。 
這是指定百分比的近似值；例如，當 STATS=10 時，如果完成進度為 40%，則該選項可能顯示 43%。 
對於較大的備份集，這不是問題，因為完成百分比在已完成的 I/O 呼叫之間變化非常緩慢。

八：磁帶選項
適用範圍：SQL Server
這些選項只用於 TAPE 裝置。 如果使用的是非磁帶裝置，則會忽略這些選項。
{ REWIND | NOREWIND }
REWIND 適用範圍：SQL Server。指定 SQL Server 釋放和倒帶磁帶。 REWIND 是預設設定。
NOREWIND 適用範圍：SQL Server。
指定 SQL Server 在備份操作之後讓磁帶一直處於開啟狀態。 在對磁帶執行多個備份操作時，可以使用此選項來幫助改進效能。
NOREWIND 包含 NOUNLOAD，並且這些選項在單個 BACKUP 語句中不相容。

{ UNLOAD | NOUNLOAD }
適用範圍：SQL Server
UNLOAD 適用範圍：SQL Server
指定在備份完成後自動重繞並解除安裝磁帶。 會話開始時 UNLOAD 是預設值。
NOUNLOAD 適用範圍：SQL Server，指定在 BACKUP 操作之後磁帶繼續在磁帶機中載入。

九：特定於日誌的選項
適用範圍：SQL Server
這些選項僅與 BACKUP LOG 一起使用。
(1){ NORECOVERY | STANDBY = undo_file_name }
NORECOVERY 適用範圍：SQL Server
備份日誌的尾部並使資料庫處於 RESTORING 狀態。 當將故障轉移到輔助資料庫或在執行 RESTORE 操作前儲存日誌尾部時，NORECOVERY 很有用。
若要執行最大程度的日誌備份（跳過日誌截斷）並自動將資料庫置於 RESTORING 狀態，請同時使用 NO_TRUNCATE 和 NORECOVERY 選項。

(2)STANDBY = standby_file_name
適用範圍：SQL Server*******
備份日誌的尾部並使資料庫處於只讀和 STANDBY 狀態。 
將 STANDBY 子句寫入備用資料（執行回滾，但需帶進一步還原選項）。 
使用 STANDBY 選項等同於 BACKUP LOG WITH NORECOVERY 後跟 RESTORE WITH STANDBY。

(4)NO_TRUNCATE
適用範圍：SQL Server
指定不截斷日誌，並使 資料庫引擎 嘗試執行備份，而不考慮資料庫的狀態。 
因此，使用 NO_TRUNCATE 執行的備份可能具有不完整的後設資料。 該選項允許在資料庫損壞時備份日誌。
NO_TRUNCATE
適用範圍：SQL Server
指定不截斷日誌，並使 資料庫引擎 嘗試執行備份，而不考慮資料庫的狀態。 
因此，使用 NO_TRUNCATE 執行的備份可能具有不完整的後設資料。 該選項允許在資料庫損壞時備份日誌。