Sao lưu và phục hồi dữ liệu trong sql server (Bài 4)

Sao lưu và phục hồi dữ liệu trong sql server là một trong những thao tác quan trọng mà người quản trị cơ sở dữ liệu phải thực hiện. Nếu như thao tác sao lưu (Backup database) được thực hiện để lưu dữ liệu và được thực hiện thường xuyên thì thao tác phục hồi dữ liệu (Restore database) chỉ được thực hiện khi nào máy chủ bị sự cố như hư ổ cứng hoặc dữ liệu bị mất do người dùng vô tình hoặc cố ý xoá,… Ngoài ra thao tác phục hổi dữ liệu cũng được thực hiện để sao chép database từ máy chủ này sang máy chủ khác.

sao luu va phuc hoi du lieu trong sql server 1

Sao lưu và phục hồi dữ liệu trong sql server – Các loại sao lưu dữ liệu

Loại 1: Full backup

Loại backup này sẽ backup dữ liệu đầy đủ nhất, vì vậy máy chủ sẽ mất nhiều thời gian để thực hiện nếu database lớn

Cú pháp

backup database Tên_Cơ_Sở_Dữ_Liệu to disk = 'Đường_dẫn\Tên_file.bak'

Ví dụ câu lệnh full backup cơ sở dữ liệu QuanLyBanHang và lưu vào “d:\bk” (Lưu ý chúng ta phải tạo thư mục bk trong ổ đĩa D trước khi thực thi câu lệnh sau)

backup database QuanLyBanHang to disk = 'd:\bk\QLBH.bak'

Thực hiện full backup bằng giao diện

sao luu va phuc hoi du lieu trong sql server 2

Chọn full backup, tên tập tin

sao luu va phuc hoi du lieu trong sql server 3

Loại 2: Different backup

Backup những dữ liệu phát sinh tính từ lần backup full gần nhất. Như vậy trước khi chúng ta thực hiện different backup thì full backup phải được thực hiện trước. Chính vì vậy, khi chúng ta sử dụng loại backup này sẽ tiết kiệm được thời gian backup dữ liệu.

Cú pháp

backup database Tên_Cơ_Sở_Dữ_Liệu to disk = 'Đường_dẫn\Tên_File.bak' with differential

Ví dụ

backup database QuanLyBanHang to disk = 'D:\sql\QLBH_Diff.bak' with differential

Thực hiện different backup bằng giao diện

sao luu va phuc hoi du lieu trong sql server 4

Loại 3: Log backup

Backup dữ liệu sử dụng tập tin log và log backup được sử dụng để backup dữ liệu tại thời điểm cụ thể ví dụ như mỗi giờ trong ngày.

Cú pháp

backup log Tên_Cơ_Sở_Dữ_Liệu to disk = 'Đường_dẫn\Tên_File.trn'

Ví dụ

backup log QuanLyBanHang to disk = 'D:\sql\QLBH.trn'

Thực hiện log backup bằng giao diện

sao luu va phuc hoi du lieu trong sql server 5

Sao lưu và phục hồi dữ liệu trong sql server – Kịch bản backup

Kịch bản backup này được thực hiên trên cơ sở dữ liệu QuanLyBanHang

  • Thực hiện full backup vào lúc 3h sáng ngày chủ nhật (1 lần trong ngày)
  • Thực hiện different backup vào lúc 2h sáng ngày thứ 4 (1 lần trong ngày)
  • Thực hiện log backup vào các thời điểm như 2h10′, 2h20′, 2h40′ và 2h50′ (4 lần trong một ngày sau thời điểm different backup)

Giả sử máy chủ bị hư ổ cứng vào lúc 2h55′ ngày thứ 4. Hỏi kịch bản phục hồi và tình trạng dữ liệu sau khi khôi phục? Câu trả lời ở ngay bên dưới

Đầu tiên chúng ta sẽ thực hiện khôi phục dữ liệu về thời điểm 3h sáng ngày chủ nhật bằng cách sử dụng bản full backup. Tiếp theo chúng ta sẽ khôi phục dữ liệu về thời điểm 2h sáng thứ 4 sử dụng bản different backup. Cuối cùng sử dụng bản log backup ở thời điểm 2h50′ của ngày thứ 4 để đưa dữ liệu về thời điểm 2h50′. Như vậy dữ liệu chỉ được khôi phục trở lại ở thời điểm 2h50′ ngày thứ 4 và chúng ta sẽ bị mất dữ liệu từ 2h51′ trở về sau.

Sao lưu và phục hồi dữ liệu trong sql server – Phục hồi dữ liệu

Phục hồi dữ liệu từ bản full và different backup

restore database Tên_Cơ_Sở_Dữ_Liệu from disk = 'Đường dẫn\Tên_File.bak' [With NoRecovery]

Phục hồi dữ liệu từ bản log backup

restore log Tên_Cơ_Sở_Dữ_Liệu from disk = 'Đường dẫn\Tên_File.trn' [With NoRecovery]

Lưu ý: Để có thể thực hiện khôi phục dữ liệu từ nhiều bản backup, chúng ta phải chỉ định With NoRecovery ở các câu lệnh restore ngoài trừ câu lệnh restore cuối cùng. Ngoài ra chúng ta có thể xem dữ liệu được khôi phục sau mỗi lần thực thi câu lệnh restore bằng cách sử dụng With Standby thay cho With NoRecovery.

Sao lưu và phục hồi dữ liệu trong sql server – Ví dụ mẫu

Sao lưu dữ liệu của cơ sở dữ liệu QuanLyBanHang

Thực hiện full backup

backup database QuanLyBanHang to disk = 'd:\bk\QLBH.bak'

Thêm dữ liệu cho bảng VATTU

Insert into VATTU values ('vt01', N'Máy lạnh Panasonic', N'Cái', 100)

Thực hiện different backup

backup database QuanLyBanHang to disk = 'D:\bk\QLBH_Diff.bak' with differential

Tiếp tục thêm dữ liệu cho bảng VATTU

Insert into VATTU values ('vt02', N'Tủ lạnh Panasonic', N'Cái', 100)

Thực hiện log backup

backup log QuanLyBanHang to disk = 'D:\bk\QLBH.trn'

Phục hồi dữ liệu cho cơ sở dữ liệu QuanLyBanHang

Giả lập máy chủ bị hư ổ cứng bằng cách xoá cơ sở dữ liệu QuanLyBanHang, sau đó tiến hành khôi phục dữ liệu. Chú ý câu lệnh restore cuối cùng không có With NoRecovery

--Khôi dữ liệu từ full backup
restore database QuanLyBanHang from disk = 'D:\bk\QLBH.bak' With NoRecovery
--Khôi phục dữ liệu từ different backup
restore database QuanLyBanHang from disk = 'D:\bk\QLBH_Diff.bak' With NoRecovery
--Khôi phục dữ liệu từ log backup
restore log QuanLyBanHang from disk = 'D:\bk\QLBH.trn'

Sao lưu và phục hồi dữ liệu trong sql server – Tổng kết

  • Tổng quan về sao lưu và phục hồi dữ liệu trong sql server
  • Các loại sao lưu và phục hồi dữ liệu trong sql server

Sao lưu và phục hồi dữ liệu trong sql server – Bài tập thực hành

Bài thực hành số 1: Phân quyền và sao lưu

Câu 1: Tạo CSDL QuanLyPhongMay

File type Size Max size File growth
Data 1 10 MB 1000 MB 10 MB
Data 2 10 MB 1000 MB 10 MB
Log 1 30 MB 2 GB 30 MB
Log 2 30 MB 2000 MB 30 MB

Câu 2: Tạo các bảng thuộc CSDL QuanLyPhongMay

Bảng PhongMay

Tên cột Kiểu dữ liệu Ghi chú
MaPhong varchar(20) Khóa chính
GhiChu nvarchar(100)

Bảng MayTinh

Tên cột Kiểu dữ liệu Ghi chú
MaMay varchar(20) Khóa chính
GhiChu nvarchar(100)
MaPhong varchar(20) Khóa ngoại

Bảng MonHoc

Tên cột Kiểu dữ liệu Ghi chú
MaMon varchar(20) Khóa chính
TenMon nvarchar(100)
SoGio int

Bảng DangKy

Tên cột Kiểu dữ liệu Ghi chú
MaMon varchar(20) Khóa chính
MaPhong varchar(20)
NgayDK datetime

Câu 3: Cấp quyền

3.1 Tạo login và user

Tên login Tên user Tên role
TruongLV TruongLV PhongKT
AnLV AnLV

3.2 Phân quyền

Tên user/Tên nhóm Quyền
PhongKT Tạo database, tạo bảng
AnLV Xem toàn bộ cơ sở dữ liệu, chỉ thêm dữ liệu trên bảng PhongMay và cho phép cấp lại quyền

Gợi ý

--Câu lệnh cấp quyền tạo cơ sở dữ liệu
grant alter any database to Tên_Login/Tên_Role
--Câu lệnh cấp quyền tạo bảng
grant create table to Tên_User/Tên_Role

Câu 4: Sao lưu và phục hồi cơ sở dữ liệu

4.1 Thực hiện full, differential và log backup cho cơ sở dữ liệu QuanLyPhongMay

Lần 1: Thêm dữ liệu cho bảng MayTinh và thực hiện full backup

MaMay GhiChu MaPhong
m01 TH 601

Lần 2: Thêm dữ liệu cho bảng MayTinh và thực hiện different backup

MaMay GhiChu MaPhong
m02 TH 602

Lần 3: Thêm dữ liệu cho bảng MayTinh và thực hiện log backup

MaMay GhiChu MaPhong
m03 TH 603

4.2 Giả lập máy chủ bị hư ổ cứng bằng cách xoá cơ sở dữ liệu QuanLyPhongMay, sau đó tiến hành khôi phục dữ liệu.

Bài thực hành số 2: Phân quyền và sao lưu

Câu 1: Cho phép tài khoản bk được phép thực hiện các công việc sau

STT Công việc được phép
1 Tạo CSDL
2 Tạo bảng
3 Backup và restore

Gợi ý câu lệnh cấp quyền backup và restore

grant backup database to Tên_User/Tên_Role

Câu 2: Đăng nhập vào quyền của bk và thực hiện các công việc bên dưới

2.1 Tạo cơ sở dữ liệu tên QuanLyNhanVien

File type Size Max size File growth Path
Data 1 10 MB Không giới hạn 10 MB C:\SQL
Data 2 10 MB 1GB 10 MB D:\SQL
Log 30 MB Không giới hạn 30 MB C:\SQL

2.2 Tạo bảng

Bảng PhongBan

Tên cột Kiểu dữ liệu Số kí tự Ghi chú
MaPhong varchar 20 Khóa chính
TenPhong nvarchar 100
DienThoai int

Bảng NhanVien

Tên cột Kiểu dữ liệu Số kí tự Ghi chú
MaNv varchar 20 Khóa chính
TenNv nvarchar 100
GioiTinh nvarchar 3
NgaySinh date
DiaChi nvarchar 200
MaPhong varchar 20 Khóa ngoại

2.2 Thiết lập ràng buộc cho các bảng

2.3 Tạo database diagram

Câu 3: Thêm dữ liệu và backup

3.1 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện full backup

3.2 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện different backup

3.3 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện log backup (lần 1)

3.4 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện log backup (lần 2, sử dụng lại tên file đã tạo ở lần 1)

Câu 4: Khôi phục dữ liệu

4.1 Xoá cơ sở dữ liệu QuanLyNhanVien

4.2 Khôi phục dữ liệu với tỳ chọn With Standby thay cho With NoRecovery

Nội dung bình luận cho “Sao lưu và phục hồi dữ liệu trong sql server (Bài 4)

Trả lời