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


  1. 說明
    1. 時間點還原
  2. 小結
    1. 實驗 Script
  3. 相關連結
  4. 參考資料

本次筆記用以驗證完整備份的情況下是否能可以藉由交易紀錄達成 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 心中。