Một Ví Dụ Sao Lưu/Khôi Phục Dữ Liệu

Tiếp theo bài “Các Kiểu Backup Trong SQL Server“, trong bài này tôi sẽ giới thiệu script để thực hiện việc sao lưu/khôi phục sử dụng cả ba loại backup đã nêu.

Script 1. backup database

USE master
GO
IF DB_ID('TEST') IS NOT NULL DROP DATABASE TEST
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.Table1(c INT)
GO
INSERT dbo.Table1 VALUES(1)

-- thời điểm t1: full backup
BACKUP DATABASE TEST TO DISK = 'D:\Backup\Test_FULL.bak' WITH INIT

-- thêm một bản ghi mới
INSERT dbo.Table1 VALUES(2)

-- thời điểm t2: differential backup
BACKUP DATABASE TEST TO DISK = 'D:\Backup\Test_DIFF.bak'
WITH INIT, DIFFERENTIAL

-- thêm một bản ghi mới thứ ba
INSERT dbo.Table1 VALUES(3)

-- thời điểm t3: transaction log backup
BACKUP LOG TEST TO DISK = 'D:\Backup\Test_TRAN.trn'
WITH INIT

-- thêm một bản ghi mới thứ tư
INSERT dbo.Table1 VALUES(4)

-- thời điểm t4: transaction log backup lần nữa
BACKUP LOG TEST TO DISK = 'D:\Backup\Test_TRAN.trn'

Chú ý khi lệnh BACKUP khi có lựa chọn “WITH INIT” thì mỗi lần thực hiện sẽ ghi đè lên file hiện tại. Các lệnh full backup, differential backup, và transaction log backup đầu tiên dùng lựa chọn này. Riêng lệnh backup thứ tư (thời điểm t4) không có lựa chọn này do đó các transaction log backup nối đuôi (append) vào nhau – Đây là việc có chủ ý vì ta muốn duy trì tất cả các transaction log backup.

Giả sử sau đó xảy ra sự cố, ta mô phỏng sự việc này bằng cách xóa database:

USE master
GO
ALTER DATABASE TEST SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE TEST

Giờ bạn cần khôi phục lại database từ các bản backup:

Script 2. Khôi phục database

USE master
GO
-- bước 1: khôi phục từ bản full backup
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_FULL.bak' WITH NORECOVERY

-- bước 2: khôi phục từ bản differential backup
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_DIFF.bak' WITH NORECOVERY

-- bước 3: khôi phục từ các bản transaction log backup theo trình tự thời gian
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_TRAN.trn' WITH FILE = 1, NORECOVERY
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_TRAN.trn' WITH FILE = 2
GO
USE Test
GO
SELECT * FROM table1

c
----
1
2
3
4

Vậy là database đã được khôi phục trở lại trạng thái như trước khi có sự cố. Lưu ý ở trong ba lệnh RESTORE đầu có dùng lựa chọn “WITH NORCOVERY” để sau mỗi lệnh RESTORE sẽ đặt database ở chế độ chờ, tiếp nhận thêm các bản backup tiếp theo (lúc đó database chưa cho phép query). Lệnh RESTORE cuối cùng không dùng lựa chọn này để chỉ ra rằng việc khôi phục đã kết thúc và database giờ đã sẵn sàng hoạt động.

Một lưu ý nữa là ở lựa chọn “WITH FILE = x”, trước đó khi bạn backup transaction log không có “WITH INIT” thì các bản backup sẽ được nối tiếp vào nhau trong cùng một file “Test_TRAN.trn”, bản đầu tiên có ID = 1, bản thứ hai có ID = 2… Do đó khi restore theo thứ tự thời gian, bạn cần chỉ định WITH FILE = 1, 2… (Từ “FILE” có lẽ ra đời từ hồi backup ra băng từ là chủ yếu, và mỗi bản backup được gọi là 1 “file”, chứ không đồng nghĩa với “file” trong hệ điều hành thời bây giờ). Vậy làm thế nào để biết có bao nhiêu bản chứa trong file backup? Bạn có thể dùng lệnh sau:

RESTORE HEADERONLY
FROM DISK = 'D:\backup\Test_TRAN.trn'

backup history được lưu ở các bảng trong msdb database. Bạn tìm các bảng mà tên có chứa từ "backup". Ví dụ câu lệnh sau trả về thông tin các backup và thư mục chứa file backup từ hôm qua đến giờ:

SELECT

bs.database_name,

bs.backup_start_date,

bs.backup_finish_date,

bs.type, /*D=Database, L=Log*/

bs.backup_size,

bmf.physical_device_name

FROM msdb.dbo.backupset bs

JOIN msdb.dbo.backupmediafamily bmf

ON bmf.media_set_id = bs.media_set_id

WHERE DATEDIFF(d,bs.backup_start_date,GETDATE())<2

ORDER BY

bs.database_name,

bs.backup_start_date

Source: http://www.sqlviet.com/blog/mot-vi-du-sao-lu-khoi-phuc-du-lieu