SQL Server 資料庫備份

備份是非常重要的工作
資料庫有隨時會掛的風險 (常常被圍歐)
掛了沒關係，但資料要救得回來
這時就要靠平常的備份維護了



首先呢，要備份一定要有相應的權限
在SQL Server中有三個角色才有權力進行備份行為
分別是sysadmin、DB_owner、DB_backupoperator
備份也只支援磁碟、磁帶二種備份的儲存體
磁帶在備份中只能用在本機上，儲存個體的寫入方法也不同
不可以同時備份一份在磁帶、一份在磁碟


備份有3種復原模型(或模式)：

一、FULL
　　全部復原，也會還原LOG紀錄，會會建立過程的Log紀錄
　　假設復原過程中到第75%當掉了，那可以依紀錄回塑到74%

二、BULK_LOGGED
　　跟FULL模式很像，但會略過建立過程Log紀錄，缺點就是
　　要嘛就整個完整復原，過程中一但出問題就全部重來
　　依說明檔是建議還是要做FULL模式為主

三、SIMPLE
　　簡單模式，不還原LOG紀錄，過程也不紀錄
　　舊有紀錄會中止，說明檔是說：
　　「您願意失去失敗和前次備份之間的所有更新內容」
　　「您願意承擔失去記錄檔部分資料的風險」
　　比較適合用在吞吐量期間比較長的資料庫
　　如每個月才會進新書的圖書管理系統
　　只有在月初時才會做資料更新，如此就不太需要用完整或大量紀錄
　　每個月做一次備份，有問題就回遡到上個月初即可

備份就有滿多種模式
SIMPLE模式只支援 FULL 及 Differential 2種模式
而其他模式只有大量紀錄及完整的復原模式才能進行

類型	說明
FULL	最完整，全部備份下來
Differential	1.一定要先有完整備份，才可做差異備份 2.會備份與跟上一次完整備份間的所有差異 例如： 先做一次完整備份叫：A 再做一次差異備份叫：1 再做一次差異備份叫：2 此時還原只需要A及2的檔即可
LOG	1.一定要先有完整備份，才可做LOG備份 2.會備份與跟上一次LOG備份間的差異 看起來跟差異備份很像，但是有點不同的是 差異備份不需要之間的備份檔，但LOG是連貫的 所有LOG備份檔都一定要在。
FILE/FILE GROUP	挑著MDF、NDF檔或群組備份 會增加管理成本不建議使用
COPY ONLY	就是複製，很像完整備份但不會影備份排程

比較特別的應該是COPY ONLY
備份是有排程的，通常不會每次都做完整備份
有可能是1個月做一次完整，每個禮拜就做差異或LOG備份
假設今天情況：

一、在第2個禮拜中的星期三，因某原因(可能程式測試之類的)需要整個資料庫的複本
二、這時做了完整備份也交出去了
三、到了禮拜五資料庫掛了
四、需要還原，此時需要「月初的完整備份檔」及「第二個禮拜做的差異備份

還原成功了
但問題是：
第二個禮拜一到禮拜四都有做交易行為，怎麼辦???
(這時先不考慮結尾交易紀錄檔)
禮拜三被拿走的複本已經做不少修改，拿回來也沒用
禮拜三做完整備份的行為，就會影響整個備份排程
所以當時應該要指定COPY ONLY


以上是觀念釐清，實際程式碼如下：

--更改還原模式------------------------------------------------------------
--改為大量紀錄復原
ALTER DATABASE 中文北風 SET RECOVERY BULK_LOGGED
--改為完整復原
ALTER DATABASE 中文北風 SET RECOVERY FULL


--備份模式------------------------------------------------------------
--完整備份
BACKUP DATABASE 中文北風 TO DISK = 'C:\backup\中文北風.bak'
--BACKUP DATABASE 中文北風 TO TAPE = 'E:\...'
--磁帶機，一定要在本機上
--BACKUP DATABASE 中文北風 TO DISK= '\\SERVER...\..'
--網路上的磁碟

--刪掉看看---
--drop database 中文北風

--還原---
RESTORE DATABASE 中文北風 FROM DISK ='C:\backup\中文北風.bak'

--備份集、媒體集，用途是加速備份，應該要在2個磁碟-----------------------------
BACKUP DATABASE 中文北風 TO 
DISK= 'C:\backup\中文北風1.bak' ,DISK= 'C:\backup\中文北風2.bak'

RESTORE DATABASE 中文北風 FROM 
DISK='C:\backup\中文北風1.bak',DISK='C:\backup\中文北風2.bak'

--壓縮，通常都會壓一下，節省空間--------------------------------------------
BACKUP DATABASE 中文北風 TO DISK='C:\backup\中文北風壓.bak' WITH COMPRESSION


--鏡像備份，就是一次備份2個檔放在不同的地方----------------------------------
BACKUP DATABASE 中文北風 TO
DISK='C:\backup\中文北風鏡1-1.bak',DISK='C:\backup\中文北風鏡1-2.bak'
MIRROR TO
DISK='C:\backup\中文北風鏡2-1.bak',DISK='C:\backup\中文北風鏡2-2.bak'
WITH FORMAT,INIT,CHECKSUM,COMPRESSION --先格式化備份位置，並初始化，且檢查備份完整，最後壓縮


--可以將不同資料庫放進同個備份檔，但有壓縮的備份檔不能跟沒壓過的放一起-----------------
BACKUP DATABASE 中文北風 TO DISK='C:\backup\資料庫備份.BAK'
BACKUP DATABASE 新新 TO DISK='C:\backup\資料庫備份.BAK'
BACKUP DATABASE 練練 TO DISK='C:\backup\資料庫備份.BAK'

--可以備份時加入名稱及備份說明------------------------------------------------
BACKUP DATABASE 中文北風 TO DISK='C:\backup\資料庫備份.BAK'
 WITH NAME='中文北風完整備份',DESCRIPTION='還原後請1...2...說明事項'

--查詢該檔所有資料庫的資訊----------------------------------------------------
RESTORE HEADERONLY FROM DISK='C:\backup\資料庫備份.BAK'

--查詢該檔「指定」資料庫的資訊-------------------------------------------------
RESTORE FILELISTONLY FROM DISK='C:\backup\資料庫備份.BAK' WITH FILE = 2 --第2個資料庫





-------------------------------------------------------------------------
--差異備份過程--------------------------------------------------------------

--先備份完整備份
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風FULL.BAK'
--塞一下資料
SELECT * INTO 中文北風.DBO.新供應商 FROM 中文北風.DBO.供應商

--差異備份  
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風DIFFERE.BAK' WITH DIFFERENTIAL

--刪掉看看
DROP DATABASE 中文北風

--還原時因為同時多個檔案，只有最後的檔是RECOVERY
--而其中的檔都要加NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風FULL.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風DIFFERE.BAK' WITH RECOVERY