Bài 7: Quản trị CSDL - Giáo trình FPT

Bài 7:QUẢN TRỊ CSDL

Các nôi dung đã học trong bài trướcBảo mật trong SQL ServerLogin IDNgười dùng CSDL (Database User)Quyền và vai trò

Hệ thống bài cũ

Các nôi dung đã học trong bài trướcBảo mật trong SQL ServerLogin IDNgười dùng CSDL (Database User)Quyền và vai trò

Quản trị CSDL 2

Mục tiêu bài học

1. Sao lưu & Phục hồi CSDL

2. Đặt lịch sao lưu tự động

Quản trị CSDL 3

2. Đặt lịch sao lưu tự động

3. System Stored Procedure

Quản trị CSDL 4

Khi làm việc với CSDL, đặc biệt là những tác vụ dễ xảyra sự cố như

Thử nghiệm tính năng mớiSửa hoặc xóa dữ liệu quan trọng.

bạn nên tạo một bản sao CSDL để phục hồi khi có sự cố

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

Khi làm việc với CSDL, đặc biệt là những tác vụ dễ xảyra sự cố như

Thử nghiệm tính năng mớiSửa hoặc xóa dữ liệu quan trọng.

bạn nên tạo một bản sao CSDL để phục hồi khi có sự cố

Quản trị CSDL 5

Các kiểu sao lưu CSDLFull backup:

Sao lưu toàn bộ dữ liệu (kể cả các stored procedure, view,hàm người dùng định nghĩa, transaction log…)File tạo ra có phần mở rộng .bak

Differential backup:Sao lưu các dữ liệu mới được cập nhật kể từ lần full backuptrước đóFile tạo ra có phần mở rộng .bak

Transaction log backup:Sao lưu các bản ghi transaction log (các thao tác xảy ra trênCSDL, không sao lưu dữ liệu)File tạo ra có phần mở rộng .trn

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

Các kiểu sao lưu CSDLFull backup:

Sao lưu toàn bộ dữ liệu (kể cả các stored procedure, view,hàm người dùng định nghĩa, transaction log…)File tạo ra có phần mở rộng .bak

Differential backup:Sao lưu các dữ liệu mới được cập nhật kể từ lần full backuptrước đóFile tạo ra có phần mở rộng .bak

Transaction log backup:Sao lưu các bản ghi transaction log (các thao tác xảy ra trênCSDL, không sao lưu dữ liệu)File tạo ra có phần mở rộng .trn

Quản trị CSDL 6

Sao lưu CSDL

Chọn kiểu sao lưu

Quản trị CSDL 7

Chọn kiểu sao lưu

Sao lưu CSDL

- Click vào nút Remove để hủy vịtrí lưu File cũ.- Click vào nút Add để chỉ định vịtrí lưu File mới

- Chọn đường dẫn lưuFile- Nhập tên File. Chú ýphải nhập đầy đủ phầnmở rộng .bak hoặc .trn

Quản trị CSDL 8

- Click vào nút Remove để hủy vịtrí lưu File cũ.- Click vào nút Add để chỉ định vịtrí lưu File mới

- Chọn đường dẫn lưuFile- Nhập tên File. Chú ýphải nhập đầy đủ phầnmở rộng .bak hoặc .trn

Sao lưu CSDL

- Chế độ “Append to the existingbackup set” cho phép lưu nhiềubản sao CSDL từ các lần sao lưukhác nhau vào cùng một file(tên file được chỉ định ở bước 2)- Chế độ “Overwrite all existingbackup sets” chỉ giữ lại một bảnsao duy nhất của lần sao lưucuối cùng.

Quản trị CSDL 9

- Chế độ “Append to the existingbackup set” cho phép lưu nhiềubản sao CSDL từ các lần sao lưukhác nhau vào cùng một file(tên file được chỉ định ở bước 2)- Chế độ “Overwrite all existingbackup sets” chỉ giữ lại một bảnsao duy nhất của lần sao lưucuối cùng.

Phục hồi CSDL

Quản trị CSDL 10

Phục hồi CSDL

-Chọn radiobutton để chọnfile CSDL trên ổđĩa

-Click vào đâyđể chỉ định vịtrí chứa fileCSDL


Phục hồi CSDL


-Chọn phiên bản của bản saolưu CSDL, để khôi phục CSDL ởđây

Phục hồi CSDL

-Chọn radio button này khithực hiện phục hồi CSDL từmột bản sao full bakup vànhiều bản sao differentialbackup


-Chọn radio button này khithực hiện phục hồi CSDL từmột bản sao full bakup vànhiều bản sao differentialbackup

Càng thực hiện sao lưu nhiều càng giảm rủi ro khi có sựcố.Với các CSDL quan trọng, thực hiện nhiều thay đổi trongngày. Nên thực hiện nhiều lần sao lưu trong một ngàyFull Backup là phương pháp an toàn nhất, nhưng thựchiện Full Backup nhiều sẽ tốn dung lượng bộ nhớ.Nên:

Thực hiện sao lưu Full Backup một lần vào lúc bắt đầu mộtngày (chuẩn bị làm việc với CSDL)Thực hiện nhiều sao lưu Differential backup trong ngày(định kì khoảng một vài tiếng một lần)Thực hiện nhiều sao lưu Transaction log backup trongngày

Gợi ý khi sao lưu/phục hồi CSDL

Càng thực hiện sao lưu nhiều càng giảm rủi ro khi có sựcố.Với các CSDL quan trọng, thực hiện nhiều thay đổi trongngày. Nên thực hiện nhiều lần sao lưu trong một ngàyFull Backup là phương pháp an toàn nhất, nhưng thựchiện Full Backup nhiều sẽ tốn dung lượng bộ nhớ.Nên:

Thực hiện sao lưu Full Backup một lần vào lúc bắt đầu mộtngày (chuẩn bị làm việc với CSDL)Thực hiện nhiều sao lưu Differential backup trong ngày(định kì khoảng một vài tiếng một lần)Thực hiện nhiều sao lưu Transaction log backup trongngày


Khi có sự cố, tiến hành phục hồi như sau:Phục hồi CSDL sử dụng bản sao lưu Full BackupPhục hồi CSDL sử dụng bản sao lưu Differential backup ởtrước và gần thời điểm xảy ra sự cốPhục hồi CSDL sử dụng bản sao lưu Transaction logbackup ở trước và gần thời điểm xảy ra sự cố

Gợi ý khi sao lưu/phục hồi CSDL

Khi có sự cố, tiến hành phục hồi như sau:Phục hồi CSDL sử dụng bản sao lưu Full BackupPhục hồi CSDL sử dụng bản sao lưu Differential backup ởtrước và gần thời điểm xảy ra sự cốPhục hồi CSDL sử dụng bản sao lưu Transaction logbackup ở trước và gần thời điểm xảy ra sự cố


Nội dung demoTạo một bản sao lưu Full Backup (tên file APFull.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới “Test”)Phục hồi CSDL sử dụng bản Full Backup (APFull.bak)

Kiểm tra CSDL được phục hồi về trạng thái trước khi bảng“Test” được thêm vào

Demo sao lưu/phục hồiCSDL sử dụng Full backup

Nội dung demoTạo một bản sao lưu Full Backup (tên file APFull.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới “Test”)Phục hồi CSDL sử dụng bản Full Backup (APFull.bak)

Kiểm tra CSDL được phục hồi về trạng thái trước khi bảng“Test” được thêm vào


Nội dung demoTạo một bản sao lưu Full Backup mới (APFull1.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới Test1)Tạo một bản sao lưu Differential backup (APDiff1.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới Test2)Tạo một bản sao lưu Differential bakup (APDiff2.bak)

Demo sao lưu CSDL sử dụngFull backup & Differential backup

Nội dung demoTạo một bản sao lưu Full Backup mới (APFull1.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới Test1)Tạo một bản sao lưu Differential backup (APDiff1.bak)Thay đổi dữ liệu trên CSDL (thêm một bảng mới Test2)Tạo một bản sao lưu Differential bakup (APDiff2.bak)


Nội dung demoPhục hồi CSDL sử dụng bản Full Backup (APFull1.bak) &Differential backup (APDiff1.bak)

Phục hồi CSDL sử dụng bản Full Backup APFull1.bak (chú ýbước thứ 4 khi phục hồi CSDL (slide 13) chọn radio buttonthứ 2)Phục hồi CSDL sử dụng bản Differential backup APDiff1.bak(chú ý bước thứ 4 khi phục hồi CSDL (slide 13) chọn radiobutton thứ 1)Kiểm tra CSDL đã được phục hồi về trạng thái sau khi thêmbảng Test1 và trước khi thêm Test2

Demo phục hồi CSDL sử dụngFull backup & Differential backup


Phục hồi CSDL sử dụng bản Full Backup APFull1.bak (chú ýbước thứ 4 khi phục hồi CSDL (slide 13) chọn radio buttonthứ 2)Phục hồi CSDL sử dụng bản Differential backup APDiff1.bak(chú ý bước thứ 4 khi phục hồi CSDL (slide 13) chọn radiobutton thứ 1)Kiểm tra CSDL đã được phục hồi về trạng thái sau khi thêmbảng Test1 và trước khi thêm Test2



Phục hồi CSDL sử dụng bản Full Backup APFull1.bak (chú ýbước thứ 4 khi phục hồi CSDL (slide 13) chọn radio buttonthứ 2)Phục hồi CSDL sử dụng bản Differential backup APDiff2.bak(chú ý bước thứ 4 khi phục hồi CSDL (slide 13) chọn radiobutton thứ 1)Kiểm tra CSDL đã được phục hồi về trạng thái sau khi thêmbảng Test1 và Test2

Demo phục hồi CSDL sử dụngFull backup & Differential backup


Phục hồi CSDL sử dụng bản Full Backup APFull1.bak (chú ýbước thứ 4 khi phục hồi CSDL (slide 13) chọn radio buttonthứ 2)Phục hồi CSDL sử dụng bản Differential backup APDiff2.bak(chú ý bước thứ 4 khi phục hồi CSDL (slide 13) chọn radiobutton thứ 1)Kiểm tra CSDL đã được phục hồi về trạng thái sau khi thêmbảng Test1 và Test2


Đặt lịch sao lưu tự động là:Tạo một kế hoạch cho SQL Server để SQL Server tự độngthực hiện sao lưu CSDL tại một thời điểm được ấn địnhsẵn.

Để tạo lịch sao lưu tự động người dùng phải đăng nhậpbằng Login ID có vai trò “sysadmin”

Đặt lịch sao lưu tự động

Đặt lịch sao lưu tự động là:Tạo một kế hoạch cho SQL Server để SQL Server tự độngthực hiện sao lưu CSDL tại một thời điểm được ấn địnhsẵn.

Để tạo lịch sao lưu tự động người dùng phải đăng nhậpbằng Login ID có vai trò “sysadmin”


Nhấn chuột phải vào Maintenance Plans. Chọn NewMaintenance Plan



Đặt tên cho Maintenance Plan



Trang cấu hình lịch sao lưu tự động


Cấu hình thời gian thực hiện saolưu tự đọng


Cấu hình thời gian thực hiện saolưu tự đọng

Các nhiệm vụChú ý: backup database task &

maintenance cleanup task

Ấn định thời gian thực hiện sao lưu CSDL



Nhấn chuột vàođây

Đặt thời gian là giờmà CSDL ít được dùng

nhất

Thêm Back Up Database TaskBack Up Database Task: thực hiện sao lưu CSDL theo thờigian đã ấn định



Kéo Back Up DatabaseTask sang khung bên

phải

Cấu hình Back Up Database TaskTrong bước 4: nhấn đúp chuột vào Back Up Database Task


-Chọn Backup Type là “Full”-Chọn các CSDL muốn thực hiện saolưu trong mục Database(s)


-Chọn Backup Type là “Full”-Chọn các CSDL muốn thực hiện saolưu trong mục Database(s)

-Chọn đường dẫn lưu bản sao CSDL-Nhập bak vào mục Backup Fileextension

Thêm Maintenance Cleanup Task vì sao lưu được thựchiện thường xuyên, dẫn đến đầy bộ nhớ server

Maintenance Cleanup Task: Xóa các bản sao, mà có thờigian sao lưu cũ hơn một khoảng thời gian cho trước.



Kéo Maintenance Cleanup Task sangkhung bên phahir

Cấu hình cho Maintenance Cleanup TaskTrong bước 6: Nhấn đúp chuột vào Maintenance Cleanup Task



- Chỉ định vị trí lưu File bản saoCSDL và phần mở rộng của bản sao.

- Chỉ định khoảng thời gian, mà các bảnsao được sao lưu trong khoảng thời giannày sẽ được giữ lại.- Ví dụ trong hình sẽ giữ lại các bản saođược sao lưu trong vòng 4 tuần và xóa cácbản sao cũ hơn

Chú ý: SQL Server chỉ thực hiện sao lưu tự động thành công khiSQL Server Agent đang chạyNếu SQL Server Agent bị tắt. Có thể khởi động lại trong

SQL Server Management StudioSQL Server Configuration Management


Chú ý: SQL Server chỉ thực hiện sao lưu tự động thành công khiSQL Server Agent đang chạyNếu SQL Server Agent bị tắt. Có thể khởi động lại trong

SQL Server Management StudioSQL Server Configuration Management


Tạo lịch sao lưu tự động cho CSLD APKiểm tra, đảm bảo lịch sao lưu tự động hoạt động chínhxác

Thực thi lịch sao lưu bằng tayKiểm tra bản sao được tự động tạo ra khi đến thời điểmđược ấn định trên lịch.

Demo

Tạo lịch sao lưu tự động cho CSLD APKiểm tra, đảm bảo lịch sao lưu tự động hoạt động chínhxác

Thực thi lịch sao lưu bằng tayKiểm tra bản sao được tự động tạo ra khi đến thời điểmđược ấn định trên lịch.


System Stored ProcedureLà những Stored Procedure được cungcấp sẵn khi cài đặt SQL ServerRất hữu ích khi thực hiện các hànhđộng quản trị và xem thông tin các đốitượng trong SQL Server

System Stored Procedure

System Stored ProcedureLà những Stored Procedure được cungcấp sẵn khi cài đặt SQL ServerRất hữu ích khi thực hiện các hànhđộng quản trị và xem thông tin các đốitượng trong SQL Server


Tham khảo System Stored Procedure tại:http://msdn.microsoft.com/en-us/library/ms187961.aspx

Trên trang này các System Stored Procedure được phântheo nhómHai nhóm quan trọng chứa các System Stored Procedurehay sử dụng

Database Engine Stored Procedures: chứa nhiều SystemSP để xem thông tin các đối tượng trong SQL ServerSecurity Stored Procedures: chứa các System SP dùng chomục đích quản trị bảo mật hệ thống

System Stored Procedure

Tham khảo System Stored Procedure tại:http://msdn.microsoft.com/en-us/library/ms187961.aspx

Trên trang này các System Stored Procedure được phântheo nhómHai nhóm quan trọng chứa các System Stored Procedurehay sử dụng

Database Engine Stored Procedures: chứa nhiều SystemSP để xem thông tin các đối tượng trong SQL ServerSecurity Stored Procedures: chứa các System SP dùng chomục đích quản trị bảo mật hệ thống


http://msdn.microsoft.com/en-us/library/ms187961.aspx


Thủ tục Mô tả

sp_Help [<tên đối tượngCSDL>]

Trả về thông tin đối tượng CSDL. (bảng, view, storedprocedure…) Trả về tổng hợp tất cả đối tượng trong cơ sởdữ liệu hiện thời, nếu không có tham số.

sp_HelpText <tên> Trả về văn bản của stored procedure, hàm người dùngđịnh nghĩa, trigger, hay view không mã hóa.

sp_HelpDb [<tên CSDL>] Trả về thông tin CSDL, hoặc toàn bộ cơ sở dữ liệu, nếukhông chỉ định tham số.

Giới thiệu một sốSystem Stored Procedure hữu ích

Trả về văn bản của stored procedure, hàm người dùngđịnh nghĩa, trigger, hay view không mã hóa.

sp_HelpDb [<tên CSDL>] Trả về thông tin CSDL, hoặc toàn bộ cơ sở dữ liệu, nếukhông chỉ định tham số.

sp_Helpfile Xem tên database vật lý và thuộc tính của các tập tin liênquan đến database hiện tại. Dùng stored procedure nàyđể xác định tên vật lý của database cần detach hoặcattach.




sp_Who [<ID đăngnhập>]

Trả về thông tin người đang đăng nhập và các tiến trìnhđang chạy. Trả về thông tin của toàn bộ người dùng đanghoạt động, nếu không chỉ định tham số.

sp_Columns <tên> Trả về thông tin cột được định nghĩa trong bảng hoặcview xác định.

sp_tables Xem danh sách các đối tượng có thể truy vấn trongdatabase hiện tại. Tất cả các đối tượng trong mệnh đềFROM.


Trả về thông tin cột được định nghĩa trong bảng hoặcview xác định.

sp_tables Xem danh sách các đối tượng có thể truy vấn trongdatabase hiện tại. Tất cả các đối tượng trong mệnh đềFROM.

sp_columns <tên> Xem thông tin các cột trong 1 table hoặc view

sp_depends <tên> Xem danh sách các Stored Procedure, View phụ thuộc vào(tham chiếu đến ) bảng hoặc view trong CSDL



sp_helpsrvrole '<TênServer Role>'

-Trả về danh sách tất cả Server Role nếu không đượctruyền tham số

- Trả về thông tin mô tả cho Server Role được chỉ địnhtrong câu lệnh

sp_password '<Passwordcũ>, '<password mới>','<Login ID>'

Thay đổi password cho một Login ID


sp_password '<Passwordcũ>, '<password mới>','<Login ID>'sp_helplogins Cung cấp thông tin về LoginID cùng với thông tin các

Database User liên kết với Login ID nàysp_addlogin Thêm một login ID mới

Sp_adduser Thêm một Database User cho một Login ID

Sinh viên có thể tham khảo các system stored procedurekhác tại trang


Sau đó sử dụng hai stored procedure sp_help hoặcsp_helptext để xem định nghĩa và danh sách tham sốcủa một system stored procedure, để biết cách sử dụngcác stored procedure này.


Sinh viên có thể tham khảo các system stored procedurekhác tại trang


Sau đó sử dụng hai stored procedure sp_help hoặcsp_helptext để xem định nghĩa và danh sách tham sốcủa một system stored procedure, để biết cách sử dụngcác stored procedure này.




Hàm sp_help

Demosử dụng System Stored Procedure

Hàm sp_helptext


Thiết lập phím tắt cho các Stored Procedure hệ thốngSử dụng menu Tools > Option

Thiết lập phím tắtcho các Stored Procedure hệ thống


Các nội dung đã học trong bàiSao lưu và phục hồi CSDL

Sao lưu CSDL là quá trình tạo một bản sao CSDL để phụchồi CSDL khi xảy ra sự cốCác kiểu sao lưu CSDL

Full backup: Sao lưu toàn bộ dữ liệu (kể cả các storedprocedure, view, hàm người dùng định nghĩa, transactionlog…)Differential backup: Sao lưu các dữ liệu mới được cập nhậtkể từ lần full backup trước đóTransaction log backup: Sao lưu các bản ghi transaction log(các thao tác xảy ra trên CSDL, không sao lưu dữ liệu)

Tổng kết

Các nội dung đã học trong bàiSao lưu và phục hồi CSDL

Sao lưu CSDL là quá trình tạo một bản sao CSDL để phụchồi CSDL khi xảy ra sự cốCác kiểu sao lưu CSDL

Full backup: Sao lưu toàn bộ dữ liệu (kể cả các storedprocedure, view, hàm người dùng định nghĩa, transactionlog…)Differential backup: Sao lưu các dữ liệu mới được cập nhậtkể từ lần full backup trước đóTransaction log backup: Sao lưu các bản ghi transaction log(các thao tác xảy ra trên CSDL, không sao lưu dữ liệu)


Đặt lịch sao lưu tự độngTạo một kế hoạch cho SQL Server để SQL Server tự độngthực hiện sao lưu CSDL tại một thời điểm được ấn địnhsẵn.Để tạo lịch sao lưu tự động người dùng phải đăng nhậpbằng Login ID có vai trò “sysadmin”Ba bước để tạo lịch sao lưu tự động

Ấn định thời gian SQL Server thực hiện sao lưuThêm và cấu hình Back Up Database TaskThêm và cấu hình Maintenance Cleanup Task

Chú ý: Để SQL Server thực hiện sao lưu tự động thànhcông phải cấu hình SQL Server Agent luôn chạy

Tổng kết

Đặt lịch sao lưu tự độngTạo một kế hoạch cho SQL Server để SQL Server tự độngthực hiện sao lưu CSDL tại một thời điểm được ấn địnhsẵn.Để tạo lịch sao lưu tự động người dùng phải đăng nhậpbằng Login ID có vai trò “sysadmin”Ba bước để tạo lịch sao lưu tự động

Ấn định thời gian SQL Server thực hiện sao lưuThêm và cấu hình Back Up Database TaskThêm và cấu hình Maintenance Cleanup Task

Chú ý: Để SQL Server thực hiện sao lưu tự động thànhcông phải cấu hình SQL Server Agent luôn chạy


SQL Server cung cấp các System Stored Procedure rấthữu ích khi thực hiện các hành động quản trị và xemthông tin các đối tượng trong SQL ServerMột số System Stored Procedure thường dùng:

Sp_helpSp_helptextSp_dependsSp_tables….

Tham khảo thêm tại:http://msdn.microsoft.com/en-us/library/ms187961.aspx

Tổng kết

SQL Server cung cấp các System Stored Procedure rấthữu ích khi thực hiện các hành động quản trị và xemthông tin các đối tượng trong SQL ServerMột số System Stored Procedure thường dùng:

Sp_helpSp_helptextSp_dependsSp_tables….

Tham khảo thêm tại:http://msdn.microsoft.com/en-us/library/ms187961.aspx




Bài 7: Quản trị CSDL - Giáo trình FPT

Documents

Transcript of Bài 7: Quản trị CSDL - Giáo trình FPT