Sao lưu và khôi phục cơ sở dữ liệu trong SQL Server

(Backup and Restore Database in SQL Server)

 Sao lưu và khôi phục dữ liệu là vấn đề rất quan trọng khi quản trị CSDL, đảm bảo cho dữ liệu không bị mất mát và nếu có sự cố xảy ra thì có thể khôi phục lại trạng thái của CSDL trước khi xảy ra sự cố. Công việc này phải dựa vào cơ chế Backup và Restore của các hệ QTCSDL, và để thực hiện tốt công việc này thì người quản trị dữ liệu phải có kịch bản sao lưu và khôi phục CSDL cho hệ thống một cách cẩn thận và chi tiết.

SQL Server cung cấp ba loại backup như sau:

Full Backup: Backup toàn bộ dữ liệu tại thời điểm thực hiện (thường dùng nhất).
Differential Backup: Backup các dữ liệu mới được cập nhật kể từ lần full backup trước đó.
Transaction Log Backup: Backup các log record hiện có trong log file. Nghĩa là sao lưu các hành động (các thao tác xảy ra đối với database) chứ không sao lưu dữ liệu. Đồng thời nó cũng cắt bỏ (truncate) log file, loại bỏ các log record vừa được backup ra khỏi log file. Vì thế khi thấy log file tăng quá lớn, có nhiều khả năng là bạn chưa từng backup transaction log bao giờ.

Một nguyên tắc chung để giảm thiểu mất mát dữ liệu khi có sự cố là tăng tần suất backup. Tuy nhiên với một database có dung lượng lớn và được cập nhật liên tục, thì việc thực hiện full backup với tần suất cao là không khả thi, vì nó dùng rất nhiều tài nguyên (CPU, I/O). Nhờ có differential backup và transaction log backup, bạn có thể tạo lập các phương án sao lưu thích hợp, đảm bảo dữ liệu được backup thường xuyên hơn mà không chiếm nhiều tài nguyên của hệ thống.

Chẳng hạn, một kịch bản sao lưu và khôi phục dữ liệu tham khảo như sau:

Full backup: Một lần mỗi ngày vào 2h sáng.

Differential backup: vào các thời điểm 6h, 10h, 14h, 18h, 22h (5 lần/ngày).

Transaction log backup: 15 phút một lần vào các thời điểm 5′, 20′, 35′, và 50′ của mỗi giờ (4 lần/giờ).

Lưu ý  Differential backup luôn sao lưu các dữ liệu đã thay đổi kể từ lần full backup trước (trong ví dụ trên là các dữ liệu đã  thay đổi kể từ 2h), chứ không phải từ lần differential backup trước đó. Vì thế bản backup lúc 10h sẽ bao gồm các dữ liệu đã được sao lưu trong bản backup lúc 6h, bản backup lúc 14h gồm các dữ liệu đã có trong bản 10h…

Transaction log backup chỉ sao lưu các log record kể từ lần transaction log backup trước đó.

Giả sử Database bị hỏng vào thời điểm 10h55′, bạn cần khôi phục lại database theo trình tự sau:

Bước 1. Khôi phục từ bản full backup gần với thời điểm có sự cố nhất (bản full backup lúc 2h).

Bước 2. Khôi phục từ bản differential backup gần với thời điểm có sự cố nhất (bản lúc 10h).

Bước 3. Khôi phục tất cả các transaction log backup kể từ sau lần diferential backup gần nhất, lần lượt theo trình tự thời gian. Đó là các bản tại các thời điểm 10h5′, 10h20′, 10h35′, và 10h50′.

Bước 1 và 2 đưa database trở lại trạng thái giống như lúc 10h. Ở bước 3, với mỗi lần khôi phục transaction log thì các thao tác chứa trong đó được đem ra thực hiện lại trên database (gọi là log forwarding) và do đó đưa nó về trạng thái gần hơn thời điểm xảy ra sự cố. Như vậy sau khi hoàn tất khôi phục bốn bản transaction log backup thì database sẽ ở vào trạng thái giống như lúc 10h50′. Tuy nhiên các thay đổi diễn ra trong 5 phút sau đó (từ 10h50′ đến 10h55′) đã bị mất.

Trong trường hợp may mắn hơn, khi sự cố xảy ra mà log file vẫn còn nguyên vẹn, bạn sẽ có cơ hội đưa database trở lại trạng thái ngay trước khi có sự cố, và do đó không có mất mát dữ liệu. Việc đầu tiên bạn cần làm là thực hiện ngay transaction log backup (nên nhớ, không được vội vàng khôi phục từ bản full backup). Sau đó các bước tiếp theo sẽ tương tự như trên:

Bước 0. Sao lưu transaction log.

Bước 1. Khôi phục từ full backup file lúc 2h sáng hôm đó.

Bước 2. Khôi phục từ differential backup file lúc 10h.

Script mô tả Backup và Restore Database trong SQL Server

Script 1 (Backup Database)

CREATE DATABASE ATM

GO

USE ATM

GO

CREATE TABLE ACCOUNTS(ACCNONO CHAR(5) NOT NULL PRIMARY KEY,CNO CHAR(4)NOT NULL, DATE DATETIME, PIN CHAR(4) NOT NULL,BALANCE INT)

GO

INSERT INTO ACCOUNTS

VALUES('TK001','C001', '2012-04-29','2345',700)

-- thời điểm t1:full backup, tham số WITH INIT cho phép ghi đè lên file hiện tại

BACKUP DATABASE ATM TO DISK = 'D:\BACKUP\ATM_FULL.bak' WITH INIT

-- thêm một bản ghi mới 

INSERT INTO ACCOUNTS

VALUES('TK002','C002', '2012-03-22','3456',500) 

-- thời điểm t2: differential backup

BACKUP DATABASE ATM TO DISK = 'D:\BACKUP\ATM_DIFF.bak'

WITH INIT, DIFFERENTIAL

-- thêm một bản ghi mới thứ ba

INSERT INTO ACCOUNTS

VALUES('TK003','C002', '2012-01-27','3456',900) 

-- thời điểm t3: transaction log backup

BACKUP LOG ATM TO DISK = 'D:\BACKUP\ATM_LOG.trn' WITH INIT

-- thêm một bản ghi mới thứ tư

INSERT INTO ACCOUNTS

VALUES('TK004','C004', '2012-07-16','1256',1000) 

-- thời điểm t4: transaction log backup lần nữa.
-- không có tham số WITH INIT để bổng sung vào bản log trước đó (không ghi đè)

BACKUP LOG ATM TO DISK = 'D:\BACKUP\ATM_LOG.trn'

Giả sử sau đó xảy ra sự cố (Giả sử database ATM bị xóa):

USE MASTER

GO

DROP DATABASE ATM

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

Script 2 (Restore database)

USE MASTER

GO

-- bước 1: khôi phục từ bản full backup.tham số “WITH NORECOVERY” để 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 đã xong

RESTORE DATABASE ATM FROM DISK = 'D:\BACKUP\ATM_FULL.BAK' WITH NORECOVERY

-- bước 2:khôi phục từ bản differential backup

RESTORE DATABASE ATM FROM DISK = 'D:\BACKUP\ATM_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 ATM FROM DISK = 'D:\BACKUP\ATM_LOG.trn' WITH FILE =1,NORECOVERY

RESTORE DATABASE ATM FROM DISK = 'D:\BACKUP\ATM_LOG.trn' WITH FILE =2

-- Kiểm tra lại sau khi đã Restore

Vậy là database ATM đã được khôi phục trở lại trạng thái như trước khi bị xóa.

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 =  n”, 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 “ATM_LOG.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…

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\ATM_LOG.trn'

 

Sử dụng Jobs trong SQL Server Agent để lập lịch Backup dữ liệu

Tiện ích Jobs trong SQL Server Agent giúp cho việc tạo lịch backup dữ liệu được thực hiện tự động một cách dễ dàng . SQL Server Agent là module trong SQL Server dùng để tự động thực hiện các tác vụ về bảo trì và giám sát hệ thống.

Để sử dụng công cụ Jobs trong SQL Server Agent, trước tiên bạn phải khởi động (start) dịch vụ này.

 

Trong trình đơn Jobs trong SQL Server Agent, chọn New Job

Hộp thoại New Job:

Name: nhập tên backup

Owner: Người có quyền quản trị CSDL

Tiếp theo, chọn Steps trong menu ở panel bên trái, bấm New

Trong mục Command nhập câu lệnh:

BACKUP DATABASE ATM TO DISK = 'D:"BACKUP"ATM.bak' WITH INIT

Chú ý: Tham số WITH INIT cho phép ghi đè lên file backup hiện tại, nếu không các bản backup hằng ngày sẽ được ghi nối tiếp nhau trong cùng một file do đó sẽ làm cho dung lượng file backup tăng lên rất lớn.

Bấm OK, trong menu ở panel bên trái, chọn Schedules, bấm New để tạo một lịch làm việc cho Jobs.

Thiết lập các tham số cho Schedules, ở đây thiết lập Jobs sẽ thực hiện backup mỗi ngày 1 lần vào lúc 2h sáng.

Bấm OK 2 lần để kết thúc tạo Jobs Backup. Như vậy, với Job đã tạo như trên, cơ sở dữ liệu ATM sẽ được tự động backup mỗi ngày 1 lần vào lúc 2h sáng. Backup file (ATM.bak) lưu tại thư mục D:\Backup. Nếu có sự cố xảy ra, có thể phục hồi lại CSDL từ backup file này.