Thought of Genius

復原模式(Recovery Model) :
發生檢查點(check point)之後，是否繼續存留交易紀錄在資料庫的交易紀錄檔之中。

完整備份(Full Backup) : Data + Uncommitted Log
將整個資落庫的資料與部分交易。在作業的過程中任何的作業完成與未完成的交易都會進行備份。

差異備份(Differential Backup) :
1.從最近一次的完整備份，到現在的資料量變化的備份作業。
2.僅能將資料庫還原至差異備份的時間點。
3.最近一次的差異備份可以取代前一次的差異備份。

交易紀錄備份(Log Backup) : Backup Log + Clear Log
1.進行交易備份之後，系統會自動截斷不在使用的交易紀錄檔，並釋放空間。
2.每一份交易備份的備份資料，是屬於接續性的關係。
3.可以將系統狀態還原至特定的時間點。

結尾交易備份(Tail-log Backup) :
當系統要進行還原時，第一個首要工作就是，將剩餘的交易紀錄檔進行備份。

<br />-- Create database<br />USE master<br />GO<br />IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Lab') DROP DATABASE Lab <br />GO<br />CREATE DATABASE [Lab] ON PRIMARY <br />( NAME = N'Lab_DATA', FILENAME = N'C:\Lab_DATA.MDF' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG01] <br />( NAME = N'Lab_FG01', FILENAME = N'C:\Lab_FG01.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG02] <br />( NAME = N'Lab_FG02', FILENAME = N'C:\Lab_FG02.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG03] <br />( NAME = N'Lab_FG03', FILENAME = N'C:\Lab_FG03.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)<br /> LOG ON <br />( NAME = N'Lab_LOG', FILENAME = N'C:\Lab_LOG.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)<br />GO<br /><br />-- Full Backup<br />USE master<br />GO<br />BACKUP DATABASE Lab <br />TO DISK='C:\Lab.bak'<br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Full_Backup', <br />NOFORMAT, INIT <br />GO<br /><br />-- Differential Backup<br />USE master<br />GO<br />BACKUP DATABASE Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Differential_Bckup',<br />DIFFERENTIAL,<br />NOFORMAT, NOINIT <br />GO<br /><br />-- Log Backup<br />USE master<br />GO<br />BACKUP LOG Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Log_Backup', <br />NOFORMAT, NOINIT<br />GO<br /><br />--Tail Log Backup<br />USE master<br />GO<br />BACKUP LOG Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Tail_Log_Backup', <br />NORECOVERY<br />GO<br /><br />-- View and check backup set information<br />RESTORE HEADERONLY FROM DISK = N'C:\Lab.bak'<br />RESTORE FILELISTONLY FROM DISK = N'C:\Lab.bak'<br /><br />-- Restore database according to column position and move datafile<br />USE master<br />GO<br />RESTORE DATABASE Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 1,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE DATABASE Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 2,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE LOG Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 3,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE LOG Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH RECOVERY, FILE = 4,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />-- Drop database<br />DROP DATABASE Lab<br />-- Execute cmd.exe<br />DEL C:\Lab.bak<br /><br />