Các Kiểu Backup Trong SQL Server

Vũ Huy Tâm

Khái niệm backup (sao lưu) và restore (khôi phục) chắc hẳn đã quen thuộc đối với đa số chúng ta: bạn thường xuyên backup, ví dụ copy toàn bộ thư mục sang một thiết bị lưu trữ khác, để phòng khi gặp sự cố gây mất mát dữ liệu thì có thể copy ngược trở lại. Với database thì việc backup diễn ra có khác, khi hệ thống đang vận hành thì bạn không thể đơn giản copy các data file và log file vì chúng bị khóa hoàn toàn. Bạn phải dựa vào cơ chế backup của hệ QTCSDL. 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 đó. Đây có lẽ là loại được dùng thường xuyên nhất.
· Differential backup: backup các trang 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à nó 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 bớt lượng dữ liệu mất mát 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 CPU và 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. Ví dụ, bạn có thể thực hiện:

· 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ờ). Chọn thời điểm như vậy để đảm bảo nó xảy ra sau differential backup.

Lưu ý là differential backup luôn sao lưu các trang đã thay đổi kể từ lần full backup trước (trong ví dụ trên là các trang đã 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 trang lưu trong bản lúc 6h, bản 14h gồm các trang đã có trong bản 10h… Transaction log backup thì ngược lại, 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 đây 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′, 1oh20′, 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′) đã vĩnh viễn 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 của sáng sớm hôm đó.

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

Bước 3. Khôi phục các transaction log backup file kể từ sau 10h, lần lượt theo trình tự thời gian: các bản backup vào lúc 10h5′, 1oh20′, 10h35′, 10h50′, và cuối cùng bản lúc 10h55′ (vừa thực hiện ở bước 0).

Trong bài sau tôi sẽ giới thiệu script thực hiện từng bước qua một ví dụ cụ thể.




Tags: , , ,

23 Comments
Posted on 8/9/2010 | Categories: Database Administration

Các bài viết tương tự

Comments
  • Hồng Phúc (04/10/2010 7:12 pm)

    Bạn ơi!! mình muốn hỏi mình lỡ cập nhật hàng loạt trên cơ sở dữ liệu!! bây giờ các bước mình cần thực hiện là gì để có thể khôi phục lại cơ sở dữ liệu như ban đầu!! thì phải làm sao???? Mong sớm nhận được hồi âm!! Thanks alot!!!!

  • Vũ Huy Tâm (05/10/2010 5:09 am)

    Nếu bạn có bản full backup TRƯỚC khi lỡ cập nhật thì cần làm thế này:

    - ghi lại thời điểm ngay trước khi xảy ra cập nhật nhầm, giả sử thời điểm đó là '2010-10-02 15:10:00' (3 giờ 10 phút chiều ngày 2/10).

    - backup log cho database

    - restore theo thứ tự full backup rồi đến transaction log backup:

    RESTORE DATABASE Your_DB FROM DISK = 'D:ackupYour_DB_FULL.bak' WITH NORECOVERY

    RESTORE DATABASE Your_DB FROM DISK = 'D:ackupYour_DB_TRAN.trn' WITH FILE = 1, STOPAT = '2010-10-02 15:10:00'

    Lưu ý mệnh đề 'STOPAT =' ở lệnh RESTORE thứ hai, database sẽ được restore nhưng dừng lại tại trước thời điểm có sự cố, do đó database trở lại tình trạng như trước khi có lỗi cập nhật.

    Tốt nhất bạn nên restore sang một database mới để thử trước.

    Nếu bạn chưa có bản full backup hoặc đã backup full SAU khi có sự cố thì bó tay không cứu được nữa.

  • Nguyễn trần Vũ (23/07/2011 12:05 am)

    em xin hỏi, nếu muốn dùng câu lệnh để tìm ra các bản backup tồn tại trên server thì làm sao. và nếu muốn xóa 1 bản backup thì làm sao.thank

  • Omni Nguyen (05/02/2013 6:13 am)

    Anh cho em hỏi chút là em backup như anh nói là backup online luôn được ah anh hay là phải stop DB ạ?
    Em chủ yếu làm Oracle, giờ e đang phải tìm hiểu thêm về ms sql nên có nhiều cái đang lơ mơ so sánh với Oracle ạ
    EM cảm ơn anh

  • vu tran (23/03/2013 5:08 am)

    Anh Tâm cho em hỏi tí :
    Với ví dụ trên thì khi em backup Transaction log backup trong khoảng từ 06h00.AM đến 10h00.AM thì có đến 14 “FILE” (4file/giờ) đúng không ạ ?
    Và nếu Database bị hư lúc 9h10.AM thì mình restore theo thứ tự như bên dưới có đúng không ?

    RESTORE DATABASE TEST FROM DISK = ‘D:\FRT ECOMMERCE\SQLServer\Test_FULL.bak’ WITH NORECOVERY

    RESTORE DATABASE TEST FROM DISK = ‘D:\FRT ECOMMERCE\SQLServer\Test_DIFF.bak’ WITH NORECOVERY

    RESTORE DATABASE TEST FROM DISK = ‘D:\FRT ECOMMERCE\SQLServer\Test_TRAN.trn’ WITH FILE = 1, NORECOVERY

    RESTORE DATABASE TEST FROM DISK = ‘D:\FRT ECOMMERCE\SQLServer\Test_TRAN.trn’ WITH FILE = 2, NORECOVERY –Cho đến WITH FILE = 8

    RESTORE DATABASE TEST FROM DISK = ‘D:\FRT ECOMMERCE\SQLServer\Test_TRAN.trn’ WITH FILE = 9

  • Lam Tu Thanh (21/05/2013 10:30 am)

    Anh Tâm cho em hỏi 2 cau hoi nha :

    1. Em muốn tạo job restore các file backup transaction log đang chứa trong 1 folder cố định thì phải viết như thế nào anh ?

    Em tìm cũng nhiều mà chưa có câu trả lời

    2. Em dang lam log shipping tren sever A (primary) va B (Secondary)
    nhung moi ngay tren sever A, luc 4h sang lai co job maintenance la backup full, sau do backup transaction log nua.
    Nen sau khi log shipping chay toi 4h thi fail, do database bi thay doi log roi.

    Co cach nao khac phuc khong anh ?

  • Vũ Huy Tâm (24/05/2013 12:59 pm)

    1. Bạn cần viết code đọc tuần tự các file trong folder theo thứ tự ngày được tạo, tại mỗi file thì restore vào database.
    2. Để giữ được log shipping thì maintenance plan phải không được backup trans log (full và differential backup thì được). Lý do là vì việc backup trans log ra 1 folder khác như vậy sẽ làm cho các file backup log không còn theo thứ tự nữa. Cách khắc phục là bạn phải bỏ log backup trong maintenance plan đi.

  • Bạch Nhựt (19/10/2014 11:09 pm)

    Chào A. Tâm

    Cho mình hỏi một vấn đề này. Mình có tạo Maintenance Plans backup full, log chạy job bình thường, sau một thời gian mình thấy log lớn quá nên có shink file log, sau khi shink thì không còn backup log trong job được nữa, mình phải làm một hành động là backup database lại thì job đó mới chạy. Có cách nào mà không bị trường hợp trên không ?

    Thanks nhiều.

    • Vũ Huy Tâm (23/10/2014 8:17 am)

      Log backup không làm giảm kích thước file log mà chỉ cho phép tái sử dụng không gian log cho các transaction mới. File log của bạn lớn có thể do có một transaction chạy lâu và chiếm nhiều đĩa (ví dụ một lệnh update dài hoặc bạn chạy index rebuild). Nếu vẫn còn đủ chỗ thì bạn không cần phải shrink, vì nếu transaction đó chạy lại thì file log lại lớn lên như cũ.
      Nếu đã shrink thì bạn cần chạy full backup rồi mới có thể chạy log backup. Maintainance plan gồm full và log backup như vậy là đủ, nếu thiếu đĩa thì cần bổ sung chứ đừng shrink log file.

  • võ thái đạt (19/01/2015 12:30 am)

    Cho mình hỏi, nếu DB bị sự cố lúc 3h sáng thì dữ liệu từ 2h-3h sẽ được phục hồi bằng cách nào?

  • Nguyễn Thanh Phong (03/03/2015 3:28 am)

    Hi anh Tâm!
    Anh cho em hỏi là làm cách nào để đặt lịch tự động backup log theo các khoảng thời gian chỉ định trong sql được vậy anh. Như VD anh nói bên trên thì có thẻ backup log 15′ 1 lần đó. Mong anh chỉ giáo thêm.
    Cảm ơn anh.

    • Red Devilic (05/03/2015 2:45 am)

      Bạn có thể viết các câu lệnh BACKUP, sau đó đặt nó vào một Schedule Job là được

      • lyhoai (03/05/2017 3:50 am)

        Trong sql có hỗ trợ tính năng đặt lịch backup rồi mà sao phải dùng schedule job nữa hả bạn

  • Thanh Phong (23/03/2015 9:31 pm)

    Hi bạn Red Devilic !
    Mình đang gặp một vấn đề là mình đã thiết lập các lịch backup tự động trong SQL xong sau đó mình đã restart lại các services trong SQL rồi nhưng sao các lịch này vẫn không hoạt động được. Mình đã thực hiện trên các máy server khác thì lịch này vẫn chạy bình thường, chỉ riêng một server là không chạy các lịch backup này được. Mong bạn chỉ giúp.
    Cảm ơn bạn nhiều.

    • Je t'Aime (17/04/2015 4:28 am)

      hj bạn Thanh Phong,
      sau khi bạn thiết lập các Schedule Job để backup tự động trong SQL xong thì bạn không cần restart lại services nào cả, mà bạn chỉ cần đảm bảo rằng service “SQL Server Agent” đang Running là được (thông thường thì sau khi cài SQL Server, default là service này không tự start)

      • AlexSander SupperTramp (13/05/2015 11:50 pm)

        Hi bạn Je t’Aime!
        cảm ơn bạn đã quan tâm tới câu hỏi của mình. Mình đã kiểm tra lại tất cả các services của SQL và đảm bảo rằng nó đang chạy. Sau khi đặt lịch xong mình có execute lịch này để chạy thử mà nó cứ quay hoài. Không rõ nó bị gì nữa nên hiện tại mình backup database cho sever này thủ công hàng ngày bằng query. Rất mong được bạn chỉ thêm để mình setup lịch backup tự động.
        Thanks!

  • Help me ! (01/05/2015 5:43 am)

    Mình mới tiếp quản 1 phần mềm chạy Ms SQL 2008, cứ đến khoảng 12g trưa phần mềm lại bị treo, các user bị bật ra, vào phần mềm thấy SQL backup ra file .MDF và file log của nó.

    Mình tịm mãi ko thấy chỗ lập lịch để tắt chế độ đó hoặc chỉnh lại giờ của nó.

    Mong các bạn hướng dẫn cho.

  • AlexSander SupperTramp (13/05/2015 11:34 pm)

    Bạn login vào SQL Sever sau đó vào Management–> Maintanance Plans rồi tìm các lịch bạkup theo database bạn đang kiểm tra rồi điều chỉnh lại thời gian hay kiểu bk của database đó. Còn bạn muốn tắt nó đi thì rất dể, chỉ cần xóa nó đi là xong nhưng nên cân nhắc vì nếu xóa đi thì data sẽ k được backup và nếu xảy ra sự cố thì bạn sẽ không có data dự phòng mà restore lại đâu nha.

  • Hỏa Tinh (10/08/2015 6:11 am)

    Chào anh Tâm, hiện tại em đang gặp khó khăn về Phục hồi dữ liệu như sau:

    16h7′ ngày 10/08/2015 Server bên em gặp sự cố. Em tiến hành phục hồi lại dữ liệu trên một server mới sử dụng SQL Server 2008 R2 (Cùng phiên bản với Server cũ mới hỏng).
    Em có những bản sau:
    FULL: lúc 2h sáng ngày 10/08/2015 (AutoBackup full hàng ngày lúc 2h sáng).
    Differential: Lúc 14h ngày 10/08/2015 (Autobackup Differential hàng ngày, 4 tiếng 1 lần bắt đầu từ 6h sáng).
    Logs: 14h5′; 14h20′; 14h35′; 14h50′
    15h5′; 15h20′; 15h35′; 15h50′
    16h5′.
    —-
    Em đã phục hồi dữ liều bản full đầu tiên và ok (Không lựa chọn option nào hết).
    Tới phục hồi lại Differential thì hệ thống báo lỗi.

    Mong anh và mọi người giúp em phục hồi lại dữ liệu với ạ, càng chi tiết càng tốt ạ.

    Thanks.

    • HeroTien0712 (11/08/2015 5:16 am)

      Hi,
      Bạn làm theo các bước sau:
      1. Restore full database chọn NO RECOVERY
      2. Differential: chọn NO RECOVERY
      3. LOG: chọn RECOVERY

      • HeroTien0712 (11/08/2015 5:16 am)

        Hi,
        Bạn làm theo các bước sau:
        1. Restore full database chọn NO RECOVERY
        2. Differential: chọn NO RECOVERY
        3. LOG: chọn RECOVERY
        Vậy thì sẽ ok nhé.

  • Hỏa Tinh (13/09/2015 4:49 am)

    Chào HeroTien0712,

    Cảm ơn bạn đã hướng dẫn mình. Mình thực hiện tương tự nhưng cứ tới bước 2. Differential là hệ thống lại báo lỗi như sau:
    http://i291.photobucket.com/albums/ll305/hoangpv/Li.jpg

  • MINHHICAN (20/04/2016 3:45 am)

    Chào Anh Tâm,
    Anh có thể làm 1 topic hướng dẫn cách back up theo anh nghĩ là tốt nhất đối với doanh nghiệp VN trong trường hợp :
    - Điều kiên CSVC tàm tạm 1 server.
    - Điều kiên CSVC tốt 2 sever (HA).
    em là chuyên viên triển khai nên mong 1 DBA chỉ giáo giúp.
    Thank you

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>