Database

SQL Server 備份與還原實驗筆記 2 - Copy Only 的使用時機

  1. 說明
    1. 任意時間點的復原需求
    2. 復原測試
    3. FLSN & LLSN
  2. 小結
    1. 完整的實驗 Script
  3. 相關連結

筆記 SQL Server 完整備份資料庫時，使用 Copy Only 的情境以及時機。

SQL Server Logo

說明

/*
0--123---45---67----8-------9
-------T---------A------T
       1                2
F-----------F
0           1
*/

上圖是資料庫的備份歷程，包含 F0、F1、T1、T2，其中 F0 為第一次的完整備份、F1為完整備份、T1、T2 為交易紀錄備份。時間軸 0 到 9 表示輸入進資料庫的資料，A 點為想要探究是否可以還原的一點，藉由上述的時間軸結合 SQL Script 作為本次的驗證情境。

本次特別將所有的備份檔以單獨(.bak for fullbackup, .trn for trasactional log)檔案的方式儲存，而不以媒體集累積的方式來實驗。

任意時間點的復原需求

如果使用 GUI，系統僅會提示使用 F1 以及 T2 進行資料庫復原，並且支援 Point-In-Time 的復原方式，但在做復原之前，如果沒有做 log tail 備份的話，則時間軸的 9 就無法復原會有資料遺失的風險。

如果要復原到 1、2、3 必須要使用 F0 以及 T1 來做復原。

4、5 只能使用 F1 來復原，且無法指定 Point-In-Time，只能還原到時間軸 5 已經完成之處，無法指定在時間軸 5 之前 4 之後，這也表示在完整備份之前，應該再做一次交易紀錄備份，才能保持完整彈性的 Point-In-Time 復原。

如果要復原 6、7、A、8 必須要使用 F1 以及 T2 來進行復原，並且可以使用 Point-In-Time 還原到特定的時間點。

如果要復原到 9 則一定要做 log tail 備份，並使用 F1、T2 以及 Log tail 備份來復原。

復原測試

RESTORE DATABASE [DB1] FROM  DISK = N'C:\backup\f0.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE LOG [DB1] FROM  DISK = N'C:\backup\t1.trn' WITH  FILE = 1,
NORECOVERY, NOUNLOAD,  STATS = 5

RESTORE LOG [DB1] FROM  DISK = N'C:\backup\t2.trn' WITH  FILE = 1,
RECOVERY, NOUNLOAD,  STATS = 5

上述的復原會在復原可以成功，因為完整備份(Full Backup)不會中斷交易紀錄的 LSN Chain。

FLSN & LLSN

RESTORE HEADERONLY FROM  DISK = N'C:\backup\f0.bak'
FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN
37000000034400001, 37000000036800001, 37000000034400001, 0
RESTORE HEADERONLY FROM  DISK = N'C:\backup\t1.trn'
FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN
37000000034400001, 37000000041600001, 37000000034400001, 37000000034400001
RESTORE HEADERONLY FROM  DISK = N'C:\backup\f1.bak'
FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN
37000000054400001, 37000000056800001, 37000000054400001, 37000000034400001
RESTORE HEADERONLY FROM  DISK = N'C:\backup\t2.trn'
FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN
37000000041600001, 37000000061600001, 37000000054400001, 37000000054400001

藉由備份檔 FLSN 以及 LLSN 可以發現，完整備份的 LLSN 會與對應的交易備份檔的 FLSN 相連，而中途加入的新的完整備份，不會改變新交易紀錄 FLSN 對應前一次交易紀錄的 LLSN。

同時也可以發現新的完整備份出現，其 CheckpointLSN 會成為新交易紀錄備份的 CheckpointLSN, DatabaseBackupLSN 值。

小結

  1. 只透過完整備份，儘管有使用完整復原模式，仍然無法進行 Point-In-Time 的時間點復原
  2. 要使用 Point-In-Time 的時間點復原，必須搭配交易紀錄檔的備份，才能夠進行
  3. 差異備份必須與最近一次的完整紀錄做搭配，換言之中途插入的完整備份紀錄，會影響後續的差異備份的還原使用
  4. 如果要插入完整備份，可以使用 Copy-Only 的方式完整備份資料庫
  5. 插入的完整備份不會破壞交易備份的 Log Chain
  6. 交易紀錄也可以做 Copy-Only 為的是避免遺失該交易紀錄備份造成還原使用的交易紀錄缺乏
  7. 備份還原盡量使用 Script 的方式明確告知資料庫，不要仰賴 GUI

SSMS 指定只複製備份

完整的實驗 Script

/*

0--123---45---67----8-------9
-------T---------A------T
       1                2
F-----------F
0           1

0	2021-03-14 16:15:32.887
1	2021-03-14 16:15:36.703
2	2021-03-14 16:15:36.703
3	2021-03-14 16:15:36.703
4	2021-03-14 16:15:41.003
5	2021-03-14 16:15:41.043
6	2021-03-14 16:16:47.310
7	2021-03-14 16:16:47.347
8	2021-03-14 16:17:38.193
9	2021-03-14 16:18:28.363
*/

USE MASTER;
DROP DATABASE IF EXISTS DB1;
CREATE DATABASE DB1;

USE DB1
CREATE TABLE [DB1].[dbo].[T1]
	(
	Id int NOT NULL,
	Date datetime NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.T1 ADD CONSTRAINT
	DF_T1 DEFAULT getdate() FOR Date
GO

Insert Into [DB1].[dbo].[T1] ([Id]) Values(0)

BACKUP DATABASE [DB1] TO  DISK = N'C:\backup\f0.bak' WITH FORMAT, INIT,  
NAME = N'完整資料庫備份 - F0', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Insert Into [DB1].[dbo].[T1] ([Id]) Values(1)
Insert Into [DB1].[dbo].[T1] ([Id]) Values(2)
Insert Into [DB1].[dbo].[T1] ([Id]) Values(3)

BACKUP LOG [DB1] TO  DISK = N'C:\backup\t1.trn' 
WITH FORMAT, INIT,  NAME = N'交易紀錄備份 - T1', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Insert Into [DB1].[dbo].[T1] ([Id]) Values(4)
Insert Into [DB1].[dbo].[T1] ([Id]) Values(5)

BACKUP DATABASE [DB1] TO  DISK = N'C:\backup\f1.bak' WITH FORMAT, INIT,  
NAME = N'完整資料庫備份 - F1', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Insert Into [DB1].[dbo].[T1] ([Id]) Values(6)
Insert Into [DB1].[dbo].[T1] ([Id]) Values(7)

-- Wait 10 secs

Insert Into [DB1].[dbo].[T1] ([Id]) Values(8)

BACKUP LOG [DB1] TO  DISK = N'C:\backup\t2.trn' 
WITH FORMAT, INIT,  NAME = N'交易紀錄備份 - T2', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Insert Into [DB1].[dbo].[T1] ([Id]) Values(9)


----

USE [master]
ALTER DATABASE [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DB1] FROM  DISK = N'C:\backup\f0.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE LOG [DB1] FROM  DISK = N'C:\backup\t1.trn' WITH  FILE = 1,
RECOVERY, NOUNLOAD,  STATS = 5

RESTORE LOG [DB1] FROM  DISK = N'C:\backup\t2.trn' WITH  FILE = 1,
RECOVERY, NOUNLOAD,  STATS = 5

-- SELECT FROM DB1.dbo.T1

SELECT * FROM [DB1].[dbo].[T1]

--

RESTORE FILELISTONLY FROM  DISK = N'C:\backup\f0.bak'
RESTORE FILELISTONLY FROM  DISK = N'C:\backup\f1.bak'

RESTORE HEADERONLY FROM  DISK = N'C:\backup\f0.bak'
-- FLSN LLSN
--37000000037600001 37000000035200001

RESTORE HEADERONLY FROM  DISK = N'C:\backup\f1.bak'
-- FLSN LLSN
--37000000055200001 37000000057600001

RESTORE HEADERONLY FROM  DISK = N'C:\backup\t1.trn'
-- FLSN LLSN
-- 37000000035200001 370000000424000014

RESTORE HEADERONLY FROM  DISK = N'C:\backup\t2.trn'
-- FLSN LLSN
-- 37000000057600001 37000000075200001

相關連結

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記