~楓花雪岳~: [SQL] COPY

SQL Server 進行備份時會變更資料庫，且會影響往後其他備份的還原方式，但假如備份時指定 COPY_ONLY 參數，則不會影響。

COPY_ONLY 是從 SQL Server 2005 開始推出的功能，在 SQL Server 2005 無法透過 SSMS 進行 COPY_OLNY 備份，只能透過 T-SQL 進行，在 SQL Server 2008 時，才可以在 SSMS 內操作。

此為 SQL Server 2012 截圖

COPY_ONLY 通常用在臨時需要對資料庫或交易記錄檔進行備份（完整備份或交易記錄備份），但不希望影響現有備份計畫和避免需要還原時，不需要用到 COPY_ONLY 這一個備份。

查詢備份記錄 T-SQL 語法

SQL Server 備份記錄會儲存在 MSDB 的 backupset 資料表中，可以利用下述語法查出，文章內 Demo 會使用此語法來查詢 LSN

SELECT 
    a.[database_name] as '資料庫名稱',
    CASE a.[type]
        WHEN 'D' THEN N'資料庫'
        WHEN 'I' THEN N'差異資料庫'
        WHEN 'L' THEN N'紀錄'
        WHEN 'F' THEN N'檔案或檔案群組'
        WHEN 'G' THEN N'差異檔案'
        WHEN 'P' THEN N'部分'
        WHEN 'Q' THEN N'差異部分'
        ELSE N'NULL'
    END as '備份類型',
    a.[first_lsn] as '備份組中第一個LSN',
    a.[last_lsn]  as '備份組之後下一個LSN',
    a.[database_backup_lsn] as '最近的完整資料庫備份之LSN',
    a.[differential_base_lsn] as '差異備份的基底 LSN',
    a.[backup_finish_date] as '備份完成的日期和時間'
FROM msdb..backupset a 
    INNER JOIN master..sysdatabases b ON a.database_name COLLATE DATABASE_DEFAULT = b.name COLLATE DATABASE_DEFAULT 
WHERE a.database_name = 'AdventureWorks2012'
ORDER BY a.database_name, a.backup_finish_date

COPY_ONLY 影響原有備份計畫 Demo

執行差異備份時，必須先有一個完整備份，因此對 AdventureWorks2012 進行五次備份

步驟 1 和 4 為完整備份、步驟 2、3 和 5 為差異備份
備份計畫為步驟 1 完整備份，之後進行 3 次差異備份，步驟 4 的完整備份，會執行正常備份和指定 COPY_ONLY，來模擬臨時對資料庫進行完整備份
觀察步驟 2、3 和 5 的差異備份是根據哪一步驟的完整備份來動作

備份計畫 Demo1

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak'                   -- 完整備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak'                   -- 完整備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份

從下圖可以觀察

步驟 2 和 3 的差異備份是根據步驟 1 的完整備份
步驟 5 的差異備份是根據步驟 4 的完整備份

步驟 4 的完整備份，破壞原有備份計畫，步驟 5 的差異備份是根據步驟 4 完整備份，而非步驟 1 的完整備份。

備份計畫 Demo2

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak'                   -- 完整備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH COPY_ONLY    -- 完整備份並指定 COPY_ONLY
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Demo.bak' WITH differential -- 差異備份

從下圖可以觀察到全部的差異備份都是根據步驟 1 的完整備份，備份計畫沒有因為步驟 4 而有所改變

COP_ONLY 的還原 Demo

利用 AdventureWorks2012 進行 1 次完整備份和 5 次交易備份，第 3 次交易備份會進行正常和指定 COPY_ONLY，而還原時會略過第 3 次交易備份

還原 Demo1

第三次交易備份沒有指定 COPY_ONLY

-- 進行備份
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\FULL.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG1.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG2.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG3.bak' -- 沒有指定 COPY_ONLY
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG4.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG5.bak' WITH NORECOVERY

-- 進行還原
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'D:\FULL.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG1.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG2.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG4.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG5.bak' WITH RECOVERY

還原時略過第 3 次交易備份，直接跳至第 4 次交易備份，還原時就會失敗，錯誤訊息如下

這個備份組的記錄從 LSN 127000000026400001 開始，但距離目前太近，無法套用至資料庫。請改用較早期且含有 LSN 127000000024800001 的記錄備份進行還原。
RESTORE LOG 正在異常結束。

從下圖就可以看出還原時，一定要有第 3 次交易記錄，LSN 不會斷掉，才能還原成功。

還原 Demo2：

第 3 次交易備份指定 COPY_ONLY

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\FULL.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG1.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG2.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG3.bak' WITH COPY_ONLY -- 指定 COPY_ONLY
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG4.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\LOG5.bak' WITH NORECOVERY

-- 還原時沒有 LOG3
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'D:\FULL.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG1.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG2.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG4.bak' WITH NORECOVERY
RESTORE LOG [AdventureWorks2012] FROM DISK = N'D:\LOG5.bak' WITH RECOVERY

從下圖就可以看出第 3 次和第 4 次交易備份的 LSN 是一樣的，也因此略過第 3 次交易備份還是可以還原成功。