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選項如下：
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

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 執行的備份可能具有不完整的元數據。 該選項允許在數據庫損壞時備份日志。