Sao lưu và phục hồi dữ liệu trong SQL Server
editor Trong SQL Server, sao lưu và phục hồi dữ liệu là quá trình quan trọng để bảo vệ dữ liệu của bạn khỏi mất mát hoặc hỏng hóc.
Bằng cách sao lưu và phục hồi dữ liệu, bạn có thể khôi phục lại dữ liệu trong trường hợp xảy ra sự cố như lỗi phần cứng, lỗi phần mềm, hoặc xóa dữ liệu một cách vô tình.Và để tìm hiểu rõ hơn, hãy cùng theo dõi bài viết dưới đây của R2S nhé!
Sao lưu và phục hồi dữ liệu trong SQL Server là gì?
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 thao tác sao lưu (Backup database) được thực hiện để lưu trữ 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 máy chủ gặp 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ố ý xóa.
Ngoài ra, thao tác phục hồi dữ liệu cũng được thực hiện để sao chép cơ sở dữ liệu từ máy chủ này sang máy chủ khác.
Lưu ý: Thao tác phục hồi dữ liệu chỉ nên được thực hiện khi cần thiết và tuân thủ các quy trình và quy định liên quan đến việc sao lưu và phục hồi dữ liệu trong SQL Server.
Các loại sao lưu dữ liệu
Dưới đây là những loại sao lưu dữ liệu mà bạn nên biết:
Loại 1: Full backup
Loại backup này sẽ sao lưu dữ liệu đầy đủ nhất, do đó máy chủ sẽ mất nhiều thời gian để thực hiện nếu cơ sở dữ liệu 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ụ như 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 sẽ như sau:
Chọn full backup, tên tập tin
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 như sau:
backup database Tên_Cơ_Sở_Dữ_Liệu to disk = 'Đường_dẫn\Tên_File.bak' with differentialVí dụ
backup database QuanLyBanHang to disk = 'D:\sql\QLBH_Diff.bak' with differentialThực hiện different backup bằng giao diện như sau
Loại 3: Log backup
Backup dữ liệu là loại sao lư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 như sau:
Sao lưu và phục hồi dữ liệu trong sql server – Kịch bản backup
Kịch bản sao lưu này được thực hiện trên cơ sở dữ liệu Quản Lý Bán Hàng. Thực hiện sao lưu toàn bộ vào 3h sáng Chủ Nhật (1 lần/ngày). Thực hiện sao lưu khác biệt vào 2h sáng thứ Tư (1 lần/ngày). Thực hiện sao lưu ghi nhật ký vào các thời điểm 2h10′, 2h20′, 2h40′ và 2h50′ (4 lần/ngày sau sao lưu khác biệt).
Giả sử máy chủ bị hỏng ổ cứng vào 2h55′ thứ Tư. 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 dưới đây.
Đầ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 Chủ Nhật bằng bản sao lưu toàn bộ. Tiếp theo, chúng ta sẽ khôi phục dữ liệu về thời điểm 2h sáng thứ Tư bằng bản sao lưu khác biệt.
Cuối cùng, sử dụng bản sao lưu ghi nhật ký vào thời điểm 2h50′ của thứ Tư để đư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 vào thời điểm 2h50′ thứ Tư và chúng ta sẽ mất dữ liệu từ 2h51′ trở đi.
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 ý:
- Để thực hiện việc khôi phục dữ liệu từ nhiều bản sao lưu, chúng ta cần sử dụng từ khóa With NoRecovery trong các câu lệnh phục hồi, trừ trường hợp cuối cùng.
- Ngoài ra, chúng ta cũng có thể sử dụng từ khóa With Standby thay cho With NoRecovery để xem dữ liệu đã được khôi phục sau mỗi lần thực thi câu lệnh phục hồi.
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 – Sao lưu và phục hồi dữ liệu trong sql server
Thực hiện bước full backup
backup database QuanLyBanHang to disk = 'd:\bk\QLBH.bak'Thêm dữ liệu vào bảng VATTU
Insert into VATTU values ('vt01', N'Máy lạnh Panasonic', N'Cái', 100)Thực hiện bước different backup
backup database QuanLyBanHang to disk = 'D:\bk\QLBH_Diff.bak' with differentialTiế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 bước 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ỏng ổ cứng, ta có thể xóa cơ sở dữ liệu QuanLyBanHang và sau đó khôi phục lại dữ liệu. Lưu ý rằng câu lệnh khôi phục cuối cùng cần có tham số “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'Bài tập thực hành – Sao lưu và phục hồi dữ liệu trong sql server
Bài thực hành số 1: Phân quyền và sao lưu
Câu 1: Tạo bảng 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 chi tiết
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_RoleCâ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 của QuanLyPhongMay
Lần 1: Thêm dữ liệu cho bảng MayTinh và thực hiện full backup như sau
| 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_RoleCâ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 sao lưu đầy đủ (full backup).
3.2 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện sao lưu khác nhau (different backup).
3.3 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện sao lưu log (lần 1).
3.4 Thêm dữ liệu cho bảng NhanVien sau đó thực hiện sao lưu log (lần 2, sử dụng lại tên file đã tạo ở lần 1).
Lưu ý: Thông tin này được trích dẫn từ các nguồn tìm kiếm và không cần liệt kê danh sách nguồn cuối bài.
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 lại dữ liệu với tỳ chọn With Standby thay cho With NoRecovery
Kết luận
Trên đây là những kiến thức hữu ích về sao lưu và phục hồi dữ liệu trong sql server. Hãy ghi lại những cú pháp và thực hiện nó thật chính xác trong những bài thực hành nhé!
Bài viết gốc được đăng tại: giasutinhoc.vn
