SQL Server 備份與還原實驗筆記 3 - 完整備份與交易紀錄

2021-03-27

本次筆記用以驗證完整備份的情況下是否能可以藉由交易紀錄達成 Point-In-Time 的還原功能。

SQL Server Logo

說明

/*
0-1-2-3-4-5-6-7-8--
------------------T
                  0
---F---F-----F
   0   1     2
*/

連續使用完整備份，因為完整備份不會 Truncate 交易紀錄，因此連續完整備份後的交易紀錄備份，可以供第一筆(連續完整備份開端)完整備份做還原使用，並且支援時間點還原。

時間點還原

USE [master]
ALTER DATABASE [DB3] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DB3] FROM  DISK = N'C:\backup\f0.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE LOG [DB3] FROM  DISK = N'C:\backup\t0.trn' WITH  FILE = 1,  
RECOVERY, NOUNLOAD,  STATS = 5,  STOPAT = N'2021-03-30T06:37:18'
ALTER DATABASE [DB3] SET MULTI_USER
SELECT * FROM [DB3].[dbo].[T1]

驗證結合 F0 完整備份以及 T0 交易紀錄備份，結合使用時間點還原，可以回復到指定的時間。此外 F1、F2、F3 也可以使用 T0 交易紀錄備份還原到特定的時間點。

小結

  1. 只要保有連續完整備份的第一份完整備份，期間沒有任何的交易紀錄備份，則最後一份交易紀錄備份可以達成任意時間點的還原
  2. F0, F1, F2 都可以使用 T0 來進行 Point-In-Time 時間點還原
  3. 完整備份不會打斷 Log Chain

💡 待驗證資料點0、資料點1 是否可以還原。

無法，完整備份以前的時間點即使有交易紀錄也沒有辦法還原。

實驗 Script

USE MASTER;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DB3'
DROP DATABASE IF EXISTS DB3;
CREATE DATABASE DB3;

USE DB3;
CREATE TABLE [DB3].[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 [DB3].[dbo].[T1] ([Id]) Values(0)
WAITFOR DELAY '00:00:02'
Insert Into [DB3].[dbo].[T1] ([Id]) Values(1)
WAITFOR DELAY '00:00:02'

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

Insert Into [DB3].[dbo].[T1] ([Id]) Values(2)
WAITFOR DELAY '00:00:02'
Insert Into [DB3].[dbo].[T1] ([Id]) Values(3)
WAITFOR DELAY '00:00:02'

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

Insert Into [DB3].[dbo].[T1] ([Id]) Values(4)
WAITFOR DELAY '00:00:03'
Insert Into [DB3].[dbo].[T1] ([Id]) Values(5)
WAITFOR DELAY '00:00:03'
Insert Into [DB3].[dbo].[T1] ([Id]) Values(6)
WAITFOR DELAY '00:00:03'

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

Insert Into [DB3].[dbo].[T1] ([Id]) Values(7)
WAITFOR DELAY '00:00:03'
Insert Into [DB3].[dbo].[T1] ([Id]) Values(8)
WAITFOR DELAY '00:00:03'

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

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

USE DB3;
SELECT
s.name, s.database_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CAST(s.database_backup_lsn AS VARCHAR(50)) AS database_backup_lsn,
CAST(s.checkpoint_lsn AS VARCHAR(50)) AS checkpoint_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() 
GO

--- 還原資料庫
USE [master]
ALTER DATABASE [DB3] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DB3] FROM  DISK = N'C:\backup\f0.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE LOG [DB3] FROM  DISK = N'C:\backup\t0.trn' WITH  FILE = 1,  
RECOVERY, NOUNLOAD,  STATS = 5,  STOPAT = N'2021-03-30T06:37:18'
ALTER DATABASE [DB3] SET MULTI_USER
SELECT * FROM [DB3].[dbo].[T1]

相關連結

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

SQL Server 備份與還原實驗筆記

SQL Server 學習資源筆記

參考資料

What You Need To Know: Log Sequence Numbers and Log Chains

雖然上篇參考資料說不使用 Copy Only 的完整備份會影響後續的交易紀錄還原，但實際測試不論是該完整備份前的完整備份，或該完整備份後的完整備份，都可以使用之後的交易紀錄進行還原，而且也不會打斷 Log Chain。

How to create Copy-Only backups in SQL Server

這篇參考資料對於 Copy Only 有做完善的說明且也與實際驗證的結果相符。

Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups

這篇參考資料提出了四個問題，其中前兩個問題與本文核心非常相關：

1. 完整備份會不會打破交易紀錄 Log Chain(LSN chain)

不會。

2. 完整備份後的交易紀錄有包含完整備份的資料嗎

交易紀錄備份，備份的是交易紀錄而非資料，可以利用「早先的完整備份」搭配交易紀錄還原達到「後續完整備份」的資料狀態。

備份、還原常在每一個 DBA 心中。

#Database #SQLServer #SSMS