Một Ví Dụ Sao Lưu/Khôi Phục Dữ Liệu

Vũ Huy Tâm

Tiếp theo bài “Các Kiểu Backup Trong SQL Server“, trong bài này tôi sẽ giới thiệu script để thực hiện việc sao lưu/khôi phục sử dụng cả ba loại backup đã nêu.

Script 1. backup database

USE master
GO
IF DB_ID('TEST') IS NOT NULL DROP DATABASE TEST
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.Table1(c INT)
GO
INSERT dbo.Table1 VALUES(1)
 
-- thời điểm t1: full backup
BACKUP DATABASE TEST TO DISK = 'D:\Backup\Test_FULL.bak' WITH INIT
 
-- thêm một bản ghi mới
INSERT dbo.Table1 VALUES(2)
 
-- thời điểm t2: differential backup
BACKUP DATABASE TEST TO DISK = 'D:\Backup\Test_DIFF.bak'
WITH INIT, DIFFERENTIAL
 
-- thêm một bản ghi mới thứ ba
INSERT dbo.Table1 VALUES(3)
 
-- thời điểm t3: transaction log backup
BACKUP LOG TEST TO DISK = 'D:\Backup\Test_TRAN.trn'
WITH INIT
 
-- thêm một bản ghi mới thứ tư
INSERT dbo.Table1 VALUES(4)
 
-- thời điểm t4: transaction log backup lần nữa
BACKUP LOG TEST TO DISK = 'D:\Backup\Test_TRAN.trn'

Chú ý khi lệnh BACKUP khi có lựa chọn “WITH INIT” thì mỗi lần thực hiện sẽ ghi đè lên file hiện tại. Các lệnh full backup, differential backup, và transaction log backup đầu tiên dùng lựa chọn này. Riêng lệnh backup thứ tư (thời điểm t4) không có lựa chọn này do đó các transaction log backup nối đuôi (append) vào nhau – Đây là việc có chủ ý vì ta muốn duy trì tất cả các transaction log backup.

Giả sử sau đó xảy ra sự cố, ta mô phỏng sự việc này bằng cách xóa database:

USE master
GO
ALTER DATABASE TEST SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE TEST

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

Script 2. Khôi phục database

USE master
GO
-- bước 1: khôi phục từ bản full backup
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_FULL.bak' WITH NORECOVERY
 
-- bước 2: khôi phục từ bản differential backup
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_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 TEST FROM DISK = 'D:\backup\Test_TRAN.trn' WITH FILE = 1, NORECOVERY
RESTORE DATABASE TEST FROM DISK = 'D:\backup\Test_TRAN.trn' WITH FILE = 2
GO
USE Test
GO
SELECT * FROM table1
 
c
----
1
2
3
4

Vậy là database đã được khôi phục trở lại trạng thái như trước khi có sự cố. 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 =  x”, 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 “Test_TRAN.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… (Từ “FILE” có lẽ ra đời từ hồi backup ra băng từ là chủ yếu, và mỗi bản backup được gọi là 1 “file”, chứ không đồng nghĩa với “file” trong hệ điều hành thời bây giờ). 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\Test_TRAN.trn'



Tags: , , , , ,

22 Comments
Posted on 13/9/2010 | Categories: Database Administration

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

Comments
  • Kiem Nguyen (11/10/2010 7:26 pm)

    Bạn ơi cho mình hỏi nếu model của Database mình set là Simple thì làm như trên có được ko? Theo mình biết thì khi set simple thì ko backup log được.

    • Vũ Huy Tâm (12/10/2010 6:24 pm)

      Chào bạn,
      nếu recovery mode set là Simple thì không backup log được, vì log lúc đấy sẽ bị ghi đè hoặc truncate thường xuyên. Chỉ dùng được khi mode là Full hoặc Bulk-log thôi.

      • Hoang Minh (02/08/2011 12:53 am)

        Chào bạn, mình có thắc mắc nếu để mode là Full hoặc Bulk-log thì điều gì xảy ra, có phải data sẽ lớn hơn nhiều so với Simple ?, như vậy mình có phải shrink data thường xuyên không ?. Tks bạn nhiều.

  • Kiem Nguyen (14/10/2010 7:52 pm)

    Thanks bạn nhiều. Đợt db mình có backup diff nhưng ko hiểu sao khi restore thì ko được mình tưởng set simple thì ko backup diff được nhưng mình mới phát hiện ra hằng ngày có 1 task của nhà cung cấp máy chủ họ backup full lên device của họ nên khi mình restore nó báo lỗi.

    Các bài viết của bạn rất có chất lượng và giá trị nếu bạn rành thì viết 1 bài về replicate và index cho view để cộng đồng cùng học hỏi nghen.

    Thanks bạn!

  • Vũ Huy Tâm (15/10/2010 2:25 pm)

    Cám ơn bạn, lúc nào có điều kiện tôi sẽ viết về các đề tài bạn gợi ý

  • Nhat (01/11/2010 7:50 am)

    Bài viết của bạn hay đấy. Hình như bạn chuyên về SQL phải không?

  • HuyTranphu (18/11/2010 8:14 pm)

    cảm ơn bạn, bài viết này rất có ích với mình. Hi vọng bạn sẽ tiếp tục có những bài viết hay hơn nữa. Ví dụ về chống phân mảnh trong database

  • Vũ Huy Tâm (20/11/2010 7:36 pm)

    cứ tiếp tục phải hay hơn nữa thì… mệt quá ;)

  • den_than_90 (18/01/2011 6:35 am)

    Cho mình hỏi câu này: " Sau khi backup thông tin về device backup, thông tin về các bản backup được lưu trữ trong các table hệ thống nào?"

  • Vũ Huy Tâm (19/01/2011 9:11 am)

    backup history được lưu ở các bảng trong msdb database. Bạn tìm các bảng mà tên có chứa từ "backup". Ví dụ câu lệnh sau trả về thông tin các backup và thư mục chứa file backup từ hôm qua đến giờ:

    SELECT

    bs.database_name,

    bs.backup_start_date,

    bs.backup_finish_date,

    bs.type, /*D=Database, L=Log*/

    bs.backup_size,

    bmf.physical_device_name

    FROM msdb.dbo.backupset bs

    JOIN msdb.dbo.backupmediafamily bmf

    ON bmf.media_set_id = bs.media_set_id

    WHERE DATEDIFF(d,bs.backup_start_date,GETDATE())<2

    ORDER BY

    bs.database_name,

    bs.backup_start_date

  • Sunrise_a7 (12/02/2011 1:19 am)

    Rất hay, mình đang cần tìm hiểu về mới vấn đề ngày. hi

    Bạn hình như chuyên về quản trị SQL.

    Cám ơn bạn rất nhiều.

    Hi vọng được đọc nhiều bài của bạn hơn nữa.

  • Omni Nguyen (25/03/2013 11:55 pm)

    Anh có nhiều bài viết hay quá. EM làm ok hết rồi.
    Anh cho em hỏi 1 câu: nếu với bản backup trên mà em mang sang 1 hệ thống khác, cài MSSQL:
    1. version MSSQL giống thế thì có restore như trên được không anh?
    2. version MSSQL cao hơn hay thấp hơn thì có restore đc ko anh?

    Em cảm ơn anh nhiều

  • Omni Nguyen (26/03/2013 12:23 am)

    Cả 1 việc nữa em mới nghĩ ra thêm câu hỏi là: Nếu em restore DB như trên mà muốn đổi tên DB thì có option nào không anh?
    EM cảm ơn anh!

    • zBackup.vn (01/05/2016 6:06 am)

      Thì bạn chỉ cần chạy lệnh RESTORE DATABASE với tên database mới thôi. Ví dụ:

      RESTORE DATABASE NewDB
      FROM DISK = 'C:\DBFull.bak'

      Lưu ý là nếu database cũ vẫn còn thì có thể xảy ra trường hợp Data file và Log file của database mới và database cũ bị trùng nhau. Khi đó lệnh RESTORE trên sẽ fail. Bạn phải dùng thêm tùy chọn WITH MOVE để phục hồi Data file và Log file của database mới sang đường dẫn khác.

      Bạn tham khảo thêm bài viết về phục hồi database SQL Server với tên khác và lưu trữ ở thư mục khác ở link này: http://backupacademy.zbackup.vn/sql-server/phuc-hoi-database-voi-ten-khac-va-luu-tru-o-thu-muc-khac/

      - zBackup.vn

  • Vũ Huy Tâm (28/03/2013 9:57 am)

    Nếu bạn restore sang một server khác có cùng version hoặc cao hơn thì ok, nếu là version thấp hơn thì không được.

  • NHAN (13/04/2013 6:21 am)

    bạn giúp tui xem lỗi này với Cannot drop the database ‘TEST’, because it does not exist or you do not have permission.

    • Lucario (14/04/2013 10:12 pm)

      Bạn thử xem lại các vấn đề sau:

      - Kiểm tra lại chính xác tên đầy đủ của database.
      - Kiểm tra trạng thái hiện tại của database.
      - Quyền hạn của User bạn dùng để drop database ‘TEST’

  • Nguyễn Ngọc Chúc (07/07/2013 2:14 pm)

    Anh vui lòng cho e hỏi:

    - Thứ tự trong việc thực hiện các loại backup có ảnh hưởng như thế nào đến việc thực hiện Bạckup Restore?
    Ý em là:

    - Khi em thực hiện Backup, có phải lúc nào cũng phải backup bản full trước, rồi mới backup các bản deff và log ko?
    - Khi thực hiện restore, có phải lúc nào cũng phải restore bản full ở chế độ NORECOVERY trước, rồi mới thực hiện restore các bản deff và log ko?

    em đang phân vân ko hiểu chổ đó, mong anh giải thích giùm.

  • Vũ Huy Tâm (11/07/2013 1:30 pm)

    - Lần đầu tiên sau khi database được set up, bạn phải backup full một lần trước rồi mới có thể backup diff và log.
    - Sau khi đã full backup lần đầu, bạn có thể backup diff và log tùy ý. Và việc backup full bao nhiêu lâu một lần là tùy thuộc vào bạn. Ví dụ bạn có thể backup full 1 lần vào đầu năm và trong cả năm đó chỉ backup diff và log. Tuy nhiên nếu database có nhiều update thì bản backup diff có thể rất lớn.
    - Khi restore, đúng vậy bạn cần restore bản full with NORECOVERY trước rồi mới restore từ backup diff và log.

  • tung (11/11/2013 5:43 am)

    Bác tâm cho em hỏi là differential backup nếu dùng lần thứ 2 trở đi thì cũng không thêm dòng WITH INIT phải không ạ ? Và nó giống backup log phải không ạ?

  • Thanh Phong (24/01/2014 12:21 am)

    Chào các bạn.

    Mình đang gặp trường hợp thế này nhờ các bạn chỉ giúp nhé. Hiên tại mình quản lý sever của công ty, máy sever có cài sql 2008 R2. Khi mình đổi pas của máy sever sau đó đăng nhập vào sql thì không được nên đành phải tạo lại mật khẩu cũ. Mong các bạn chỉ giúp mình cách khắc phục tình trạng vừa nêu nếu mình muốn đổi pass máy nhé. Thanks!

  • Minh Long (17/04/2017 5:18 am)

    Xin hỏi mình backup full 1 lần hàng ngày, còn lại backup up log 1 tiếng /lần. Mà không cần backup up full mà không cần backup different thì có vấn đề gì không nhỉ

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>