Backup & Recovery

Recovery Models

SQL Server cung cấp 3 recovery model, mỗi loại ảnh hưởng đến cách transaction log được quản lý và loại backup có thể thực hiện.

So sánh Recovery Models

SIMPLE Recovery Model

• Transaction log được truncate tự động sau mỗi checkpoint
• Không thể thực hiện transaction log backup
• Thích hợp: development, non-critical databases, data warehouses (ETL chạy từ đầu được)
• Rủi ro: mất dữ liệu từ lần full/differential backup cuối cùng

-- Xem recovery model hiện tại
SELECT name, recovery_model_desc 
FROM sys.databases 
WHERE name = 'AdventureWorks';

-- Chuyển sang SIMPLE
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;

FULL Recovery Model

• Tất cả transactions được ghi đầy đủ vào log
• Transaction log KHÔNG tự truncate → bắt buộc phải backup log thường xuyên
• Cho phép point-in-time recovery
• Thích hợp: production OLTP systems, khi RPO thấp (mất ít dữ liệu nhất)

ALTER DATABASE AdventureWorks SET RECOVERY FULL;

BULK-LOGGED Recovery Model

• Minimally log các bulk operations: BULK INSERT, SELECT INTO, index rebuild, BCP
• Khi có bulk operation: point-in-time restore không khả dụng trong khoảng đó
• Log backup vẫn cần thiết
• Thích hợp: temporary switch trong ETL loads để giảm log space

-- Switch tạm để load bulk data
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
-- ... thực hiện bulk operations ...
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
BACKUP LOG AdventureWorks TO DISK = 'log_after_bulk.bak';

Các loại Backup

1. Full Backup

Backup toàn bộ database tại một thời điểm. Là nền tảng của mọi backup strategy.

-- Full backup ra file local
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Full_20260401.bak'
WITH 
    NAME = 'Full Backup - AdventureWorks',
    DESCRIPTION = 'Weekly full backup',
    COMPRESSION,
    CHECKSUM,
    STATS = 10;  -- Báo tiến độ mỗi 10%

-- Full backup ra nhiều file (striped) để tăng tốc
BACKUP DATABASE AdventureWorks
TO 
    DISK = 'D:\Backups\AW_1of2.bak',
    DISK = 'D:\Backups\AW_2of2.bak'
WITH COMPRESSION, STATS = 10;

2. Differential Backup

Backup tất cả các trang đã thay đổi kể từ lần full backup cuối cùng.

• Nhanh hơn full backup, nhỏ hơn
• Khi restore: cần full backup + differential backup mới nhất
• Không dựa trên differential backup trước

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Diff_20260401.bak'
WITH DIFFERENTIAL,
    NAME = 'Differential Backup - AdventureWorks',
    COMPRESSION,
    CHECKSUM;

3. Transaction Log Backup

Backup phần transaction log chưa được backup, cho phép point-in-time recovery.

• Yêu cầu: Recovery model = FULL hoặc BULK-LOGGED
• Sau khi backup: log được truncate (đánh dấu có thể tái sử dụng)
• Phải có ít nhất 1 full backup trước khi backup log lần đầu

-- Log backup
BACKUP LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Log_20260401_1400.trn'
WITH 
    NAME = 'Log Backup 14:00',
    COMPRESSION,
    CHECKSUM;

-- Backup tất cả log files (để clear log space khẩn cấp)
BACKUP LOG AdventureWorks TO DISK = 'NUL';  -- KHÔNG dùng trong production thực tế!

4. File/Filegroup Backup

Backup từng file hoặc filegroup riêng lẻ — hữu ích cho các database rất lớn (VLDB).

-- Backup một filegroup cụ thể
BACKUP DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\Backups\AW_PRIMARY_FG.bak'
WITH COMPRESSION;

-- Backup một file cụ thể
BACKUP DATABASE AdventureWorks
FILE = 'AdventureWorks_Data'
TO DISK = 'D:\Backups\AW_DataFile.bak'
WITH COMPRESSION;

5. Copy-Only Backup

Backup không ảnh hưởng đến backup chain — không reset differential base.

-- Copy-only full backup (không làm hỏng backup chain)
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION;

-- Copy-only log backup (không truncate log)
BACKUP LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Log_CopyOnly.trn'
WITH COPY_ONLY;

Backup Destinations

Backup ra Disk (Local / Network Share)

-- Local disk
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks.bak'
WITH COMPRESSION;

-- Network share (UNC path)
BACKUP DATABASE AdventureWorks
TO DISK = '\\BackupServer\SQLBackups\AdventureWorks.bak'
WITH COMPRESSION;

Backup lên Azure Blob Storage (URL)

-- Tạo credential trước
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2020-08...&sig=xxxxx';

-- Backup lên Azure
BACKUP DATABASE AdventureWorks
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/AdventureWorks.bak'
WITH CREDENTIAL = 'https://mystorageaccount.blob.core.windows.net/backups',
    COMPRESSION, ENCRYPTION
    (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert);

Restore Operations

RESTORE với RECOVERY vs NORECOVERY

-- Restore full backup, chưa xong
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY,
    MOVE 'AdventureWorks_Data' TO 'D:\SQL\AW_Data.mdf',
    MOVE 'AdventureWorks_Log' TO 'D:\SQL\AW_Log.ldf',
    STATS = 10;

-- Apply differential backup, chưa xong
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Diff.bak'
WITH NORECOVERY;

-- Apply log backup, chưa xong
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log_1.trn'
WITH NORECOVERY;

-- Apply log backup cuối, bring database ONLINE
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log_2.trn'
WITH RECOVERY;

Point-in-Time Recovery

-- Restore đến một thời điểm cụ thể (STOPAT)
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY,
    STOPAT = '2026-04-01T14:35:00';

-- Restore đến một LSN (Log Sequence Number) cụ thể
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY,
    STOPATMARK = 'lsn:0x00000028:00000210:0001';

-- Restore đến một named transaction mark
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY,
    STOPATMARK = 'MyTransactionMark';

Tail-Log Backup (quan trọng trước restore!)

Backup phần log chưa được backup trước khi restore — để không mất dữ liệu từ lần log backup cuối.

-- Tail-log backup khi database còn accessible
BACKUP LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_TailLog.trn'
WITH NORECOVERY,  -- Database chuyển sang RESTORING ngay sau đó
    NO_TRUNCATE;  -- Nếu data files bị hỏng

-- Tail-log backup khi data files bị hỏng (log còn intact)
BACKUP LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_TailLog.trn'
WITH NO_TRUNCATE, NORECOVERY;

Page-Level Restore

Restore từng trang bị hỏng mà không cần offline toàn bộ database (chỉ FULL recovery model).

-- Xem các trang bị hỏng
SELECT * FROM msdb.dbo.suspect_pages;

-- Restore từng trang (database vẫn ONLINE, các trang khác accessible)
RESTORE DATABASE AdventureWorks
PAGE = '1:57, 1:202, 1:916'
FROM DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;

-- Apply log sau đó để recover
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY;

RPO và RTO

Recovery Point Objective (RPO)

Mất tối đa bao nhiêu dữ liệu? — đo bằng thời gian

• RPO = 1 giờ → có thể mất tối đa 1 giờ dữ liệu
• Đạt được bằng cách: backup log thường xuyên, Always On AG synchronous

Recovery Time Objective (RTO)

Phục hồi trong bao lâu? — đo bằng thời gian downtime

• RTO = 30 phút → database phải online lại trong 30 phút
• Đạt được bằng cách: Always On AG (auto failover ~30s), pre-staged restore

Tradeoff RPO vs RTO

Backup Strategy điển hình

Strategy cho OLTP Production

Thứ Hai 00:00  → Full Backup
Thứ Ba đến CN 00:00 → Differential Backup
Mỗi giờ (00:00 - 23:00) → Transaction Log Backup

-- Job: Weekly Full Backup (chạy Chủ Nhật 00:00)
BACKUP DATABASE AdventureWorks
TO DISK = N'D:\Backups\AW_Full_' + 
    CONVERT(VARCHAR, GETDATE(), 112) + '.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Job: Nightly Differential (Thứ Hai - Thứ Bảy 00:00)
BACKUP DATABASE AdventureWorks
TO DISK = N'D:\Backups\AW_Diff_' + 
    CONVERT(VARCHAR, GETDATE(), 112) + '_' +
    REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + '.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Job: Hourly Log Backup
BACKUP LOG AdventureWorks
TO DISK = N'D:\Backups\AW_Log_' +
    CONVERT(VARCHAR, GETDATE(), 112) + '_' +
    REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + '.trn'
WITH COMPRESSION, CHECKSUM;

Xác minh và Kiểm tra Backup

RESTORE VERIFYONLY

Kiểm tra backup có readable và không bị corrupt — không thực sự restore.

RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH CHECKSUM;  -- Xác minh checksum nếu backup có checksum

DBCC CHECKDB

Kiểm tra tính integrity của database sau khi restore.

-- Kiểm tra toàn bộ database
DBCC CHECKDB ('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Kiểm tra một table cụ thể
DBCC CHECKTABLE ('Sales.Orders') WITH NO_INFOMSGS;

-- Xem thông tin backup history
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,  -- D=Full, I=Differential, L=Log
    CAST(bs.backup_size / 1024.0 / 1024 AS DECIMAL(10,2)) AS backup_size_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'AdventureWorks'
ORDER BY bs.backup_start_date DESC;

Backup Compression và Encryption

Backup Compression

-- Enable compression mặc định ở server level
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- Compression cho từng backup
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Compressed.bak'
WITH COMPRESSION;

-- Xem compression ratio
SELECT 
    backup_size,
    compressed_backup_size,
    CAST(100 * (1 - compressed_backup_size * 1.0 / backup_size) AS DECIMAL(5,2)) 
        AS compression_savings_pct
FROM msdb.dbo.backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date DESC;

Backup Encryption (SQL Server 2014+)

-- Bước 1: Tạo master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssword123!';

-- Bước 2: Tạo certificate
CREATE CERTIFICATE BackupEncryptCert
WITH SUBJECT = 'SQL Server Backup Encryption Certificate';

-- Bước 3: Backup certificate (RẤT QUAN TRỌNG - mất cert = mất backup!)
BACKUP CERTIFICATE BackupEncryptCert
TO FILE = 'D:\Certs\BackupEncryptCert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Certs\BackupEncryptCert.pvk',
    ENCRYPTION BY PASSWORD = 'CertP@ssword123!'
);

-- Bước 4: Backup với encryption
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Encrypted.bak'
WITH 
    COMPRESSION,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupEncryptCert
    );

Database Snapshots

Database snapshot là read-only, point-in-time copy của database.

• Cơ chế: copy-on-write — chỉ copy trang khi trang gốc bị thay đổi
• Không thay thế backup — snapshot phụ thuộc vào source database
• Dùng để: nhanh chóng revert về trạng thái trước, reporting, testing

-- Tạo database snapshot
CREATE DATABASE AdventureWorks_Snapshot_20260401
ON (
    NAME = 'AdventureWorks_Data',
    FILENAME = 'D:\Snapshots\AW_Snap_20260401.ss'
)
AS SNAPSHOT OF AdventureWorks;

-- Query từ snapshot
SELECT TOP 10 * 
FROM AdventureWorks_Snapshot_20260401.Sales.Orders;

-- Revert database về snapshot (database offline với users trong quá trình này)
RESTORE DATABASE AdventureWorks
FROM DATABASE_SNAPSHOT = 'AdventureWorks_Snapshot_20260401';

-- Xóa snapshot
DROP DATABASE AdventureWorks_Snapshot_20260401;

-- Xem tất cả snapshots
SELECT name, source_database_id, create_date
FROM sys.databases
WHERE source_database_id IS NOT NULL;

SQL Server Agent Backup Jobs

-- Xem các backup jobs trong SQL Agent
SELECT 
    j.name AS job_name,
    j.enabled,
    js.step_name,
    js.command,
    jsch.freq_type,
    jsch.freq_interval
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
JOIN msdb.dbo.sysjobschedules jsch ON j.job_id = jsch.job_id
WHERE js.command LIKE '%BACKUP%'
ORDER BY j.name;

-- Lịch sử các backup jobs gần nhất
SELECT TOP 20
    j.name AS job_name,
    jh.run_date,
    jh.run_time,
    CASE jh.run_status 
        WHEN 0 THEN 'Failed' 
        WHEN 1 THEN 'Succeeded' 
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled' 
    END AS run_status,
    jh.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE j.name LIKE '%Backup%'
ORDER BY jh.run_date DESC, jh.run_time DESC;

Ola Hallengren Backup Solution

Script backup phổ biến nhất trong cộng đồng SQL Server (thay thế cho custom scripts):

• Download: https://ola.hallengren.com/
• Hỗ trợ: full, differential, log backup; cleanup; verify; compression; encryption
• Tự động skip databases đang OFFLINE hoặc RESTORING

-- Ví dụ sử dụng Ola Hallengren stored procedures
EXEC dbo.DatabaseBackup
    @Databases = 'AdventureWorks',
    @Directory = 'D:\Backups',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @Verify = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 168;  -- Xóa backups cũ hơn 168 giờ (7 ngày)

EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\Backups',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CleanupTime = 48;  -- Giữ log backups 48 giờ

Q&A - Phỏng vấn Backup & Recovery

Junior Level

Q1: Recovery model là gì và có bao nhiêu loại?

Recovery model quyết định cách SQL Server quản lý transaction log và loại backup nào được hỗ trợ. Có 3 loại:

• SIMPLE: log tự truncate, không backup log được, RPO = thời gian giữa 2 full backup
• FULL: log phải backup thủ công, hỗ trợ point-in-time recovery
• BULK-LOGGED: giảm log cho bulk ops (BULK INSERT, index rebuild), vẫn cần backup log

Q2: Sự khác biệt giữa Full, Differential và Log backup?

• Full: backup toàn bộ database — basis cho mọi restore chain
• Differential: backup những gì thay đổi kể từ full backup cuối — nhỏ hơn full, restore nhanh hơn chuỗi log dài
• Log: backup transaction log — cho phép point-in-time recovery, cần backup thường xuyên nhất

Q3: Khi nào dùng WITH NORECOVERY khi restore?

Khi còn cần apply thêm backup (differential hoặc log) sau đó. Database sẽ ở trạng thái RESTORING và không nhận connections. Backup cuối cùng trong chuỗi restore dùng WITH RECOVERY để bring database online.

Q4: Copy-only backup khác gì với full backup thông thường?

Copy-only backup không ảnh hưởng đến backup chain:

• Full copy-only không reset differential base → differential backup sau vẫn dựa trên full backup trước đó
• Log copy-only không truncate transaction log
• Dùng khi cần backup ad-hoc mà không muốn can thiệp vào backup schedule chính

Mid Level

Q5: Giải thích quy trình restore point-in-time. Các bước thực hiện?

1. Tail-log backup: backup phần log chưa backup (dùng WITH NORECOVERY để database vào RESTORING)
2. Restore full backup: RESTORE DATABASE ... FROM DISK ... WITH NORECOVERY
3. Restore differential backup (nếu có): RESTORE DATABASE ... WITH NORECOVERY
4. Restore log backups tuần tự: mỗi file log WITH NORECOVERY, log cuối cùng WITH RECOVERY, STOPAT = 'datetime'

Q6: RPO và RTO là gì? Làm thế nào để đạt RPO = 15 phút?

• RPO (Recovery Point Objective): tối đa mất bao nhiêu dữ liệu (đo bằng thời gian)
• RTO (Recovery Time Objective): phục hồi trong bao lâu (downtime tối đa)

Để đạt RPO = 15 phút: chạy log backup mỗi 15 phút trên database ở FULL recovery model. Trong trường hợp disaster, mất tối đa 15 phút dữ liệu.

Q7: Tail-log backup là gì và khi nào cần thiết?

Tail-log backup là log backup của phần transaction log chưa được backup — thực hiện ngay trước khi restore để không mất dữ liệu từ lần log backup cuối đến thời điểm sự cố.

Cần thiết khi:

• Database bị corrupt hoặc mất do hardware failure nhưng log file còn intact
• Dùng WITH NO_TRUNCATE nếu data files bị hỏng

Q8: Database snapshot hoạt động như thế nào? Có thể dùng thay backup không?

Snapshot dùng cơ chế copy-on-write: khi trang gốc lần đầu bị sửa đổi sau khi tạo snapshot, trang gốc được copy vào file snapshot trước khi ghi dữ liệu mới.

Không thể thay thế backup vì:

• Snapshot lưu trên cùng server, cùng disk → không protect khỏi hardware failure
• Snapshot phụ thuộc hoàn toàn vào source database — source bị hỏng, snapshot cũng mất
• Snapshot không cover server outage, disk failure

Senior Level

Q9: Thiết kế backup strategy cho OLTP database 2TB, RPO = 30 phút, RTO = 2 giờ, ngân sách backup storage hạn chế?

Phân tích: 2TB full backup sẽ lớn và tốn thời gian/storage. Cần tối ưu:

Strategy:

• Full backup: 1 lần/tuần (Chủ Nhật 00:00) — dùng WITH COMPRESSION (typically giảm 50-70%)
• Differential backup: hàng đêm — tăng trưởng theo tỷ lệ thay đổi, không theo kích thước database
• Log backup: mỗi 30 phút — đảm bảo RPO
• Retention: Full: 4 tuần, Differential: 2 tuần, Log: 72 giờ
• Backup destination: local disk + cloud (Azure Blob) với lifecycle policy

RTO 2 giờ: với 2TB, restore từ full + differential + logs có thể mất hơn 2 giờ. Cân nhắc:

• Filegroup backup strategy nếu chỉ một phần crash
• Always On AG với async secondary để failover nhanh hơn

Q10: Giải thích backup chain và khi nào chain bị broken?

Backup chain là chuỗi liên tục từ full backup → (optional differential) → sequential log backups. Chain đảm bảo restore được đến bất kỳ thời điểm nào trong khoảng covered.

Chain bị broken khi:

1. Switch từ FULL sang SIMPLE rồi back sang FULL → chain reset, phải lấy new full backup
2. Log backup bị thiếu/miss → không restore qua khoảng đó được
3. Database detach/attach không đúng cách
4. TRUNCATE_ONLY (SQL 2008 trở về trước, đã deprecated)

Cách kiểm tra: query msdb.dbo.backupset và verify không có gap trong sequence của log backups.

Q11: Khi nào dùng page-level restore? Ưu điểm so với full restore?

Page-level restore khi:

• Chỉ một vài trang (pages) bị corrupt, phần còn lại database hoạt động tốt
• Muốn giảm thiểu downtime — database vẫn ONLINE, chỉ các trang bị corrupt không accessible
• Phát hiện qua DBCC CHECKDB hoặc msdb.dbo.suspect_pages

Ưu điểm: không cần offline cả database → RTO gần như zero cho phần còn lại. Sau khi restore trang và apply log, database hoàn toàn normal.

Điều kiện: FULL recovery model, Enterprise Edition (hoặc Developer)