Đặt lịch tự động sao lưu cơ sở dữ liệu với SQL 2000,2005 hoặc phiên bản cao hơn


1.  Sao lưu tự động với SQL 2000 :

– Việc thiết lập đặt lịch tuỳ theo mô hình hoạt động của doanh nghiệp khi chạy các ứng dụng trên CSDL sẽ có các thiết lập backup tương ứng.

à Ví dụ một bản đặt lịch như sau: Chọn đặt lịch theo Tuần (weekly). Tạo bản backup full vào ngày thứ 2 đầu tuần và thứ 7 cuối tuần, các ngày khác trong tuần có thể để chế độ backup Difference(nối tiếp) theo các bước bên dưới :

– Trước tiên phải kiểm tra xem services : SQLServerAgent đã được Start chưa. (Start à Run à gõ services.msc)

– Chạy chương trình SQL Server Enterprise Manager à Click chọn database cần đặt lịch à All Tasks à Backup Database

* Tạo bản backup Full cho ngày thứ 2 và ngày thứ 7 :

– Chọn mục Database – Complete : Chế độ tạo bản backup full.

– Click vào Add để thiết lập đường dẫn và tên file vào thư mục lưu trữ backup cho ngày thứ 2.

– Chọn mục Overwrite existing media : Chế độ ghi đè file dữ liệu đang tồn tại.

– Chọn mục Schedule để thiết lập thông tin cho việc đặt lịch.

– Chọn mục Recurring à Change để chọn thiết lập lịch sao lưu theo Ngày – Tháng – Năm.

* Tạo bản backup Differential cho ngày thứ 3 – 4 – 5 – 6 – CN:

– Chọn mục Database – Differential : Chế độ tạo bản backup nối tiếp.

– Click vào Add để thiết lập đường dẫn và tên file vào thư mục lưu trữ backup cho các ngày thứ 3 – 4 – 5 – 6 – CN.

– Chọn mục Overwrite existing media : Chế độ ghi đè file dữ liệu đang tồn tại.

– Chọn mục Schedule để thiết lập thông tin cho việc đặt lịch.

–> Các lịch backup được lưu trong mục Management –> SQL Server Agent –> Jobs

2. Sao lưu tự động với SQL 2005,2008 :

–   Phương pháp này kết hợp giữa VBScript và T-SQL với Task Manager trong window để tạo thành hệ thống sao lưu CSDL tự động cho SQL 2005 trở đi.

* Bước 1 :

–  Tạo file T-SQL với nội dung tương ứng bên dưới (Lưu file với định dạng *.sql)

–  Đường dẫn lưu file tùy chọn. Ví dụ: C:\SQL_Backup\backupDB.sql

*Code :

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)

–month variable

IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2

SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))

ELSE

SET @monthSTR= ‘0’ + CAST(MONTH(GETDATE()) AS CHAR(2))

–day variable

IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2

SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))

ELSE

SET @daySTR=’0′ + CAST(DAY(GETDATE()) AS CHAR(2))

–hour variable

IF (SELECT LEN(DATEPART(hh, GETDATE())))=2

SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

ELSE

SET @hourStr= ‘0’ + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

–minute variable

IF (SELECT LEN(DATEPART(mi, GETDATE())))=2

SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

ELSE

SET @minStr= ‘0’ + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

–name variable based on time stamp

SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr

–=================================================================

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)

SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)

WHILE @IDENT IS NOT NULL

BEGIN

SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT

/*Change disk location here as required*/

SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”C:\SQL_Backup\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’

EXEC (@SQL)

SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN (‘TEMPDB’)

END

* Bước 2 :

–  Tạo file VBScript để xóa những file sao lưu cũ trùng lặp thay thế bằng file sao lưu mới. Script này cũng có thêm chức năng ghi log lại nhật ký sao lưu dữ liệu.

+  Bạn cần tạo một file trống C:\SQL_Backup\script\log.txt  để ghi log nhật ký sao lưu.

Lưu và đặt script vào theo đường dẫn C:\SQL_Backup\script\deleteBAK.vbs

* Code :
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘location of the database backup files
sFolder = “C:\SQL_Backup\”

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

‘used for writing to textfile – generate report on database backups deleted
Const ForAppending = 8

‘you need to create a folder named “scripts” for ease of file management &
‘a file inside it named “LOG.txt” for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & “\scripts\LOG.txt”, ForAppending)

objFile.Write “================================================================” & VBCRLF & VBCRLF
objFile.Write ”                     DATABASE BACKUP FILE REPORT                ” & VBCRLF
objFile.Write ”                     DATE:  ” &    FormatDateTime(Now(),1)   & “” & VBCRLF
objFile.Write ”                     TIME:  ” &    FormatDateTime(Now(),3)   & “” & VBCRLF & VBCRLF
objFile.Write “================================================================” & VBCRLF

‘iterate thru each of the files in the database backup folder
For Each itemFiles In files
‘retrieve complete path of file for the DeleteFile method and to extract
‘file extension using the GetExtensionName method
a=sFolder & itemFiles.Name

‘retrieve file extension
b = fso.GetExtensionName(a)
‘check if the file extension is BAK
If uCase(b)=”BAK” Then

‘check if the database backups are older than 3 days
If DateDiff(“d”,itemFiles.DateCreated,Now()) >= 3 Then

‘Delete any old BACKUP files to cleanup folder
fso.DeleteFile a
objFile.WriteLine “BACKUP FILE DELETED: ” & a
End If
End If
Next

objFile.WriteLine “================================================================” & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing

* Bước 3 :
–  Tạo file bat kết hợp với T-SQL và VBScript.
–  Lưu file này với tên bất kỳ, đuôi file *.cmd (Ví dụ: databaseBackup.cmd )

* Code:
sqlcmd.exe -S hp540\sqlexpress -E  -i “C:\SQL_Backup\backupDB.sql”
c:\SQL_Backup\scripts\deleteBAK.vbs

– Trong đó:
+ hp540\sqlexpress : Là IntanceName của SQL, tùy theo từng máy.
+ C:\SQL_Backup\backupDB.sql : Là đường dẫn tới file chứa các câu lệnh thực thi backup Database.

* Bước 4:
–  Tạo tác vụ backup tự động với windows Task Scheduler.
–  Mục đích để backup định kì, có thể là Ngày, tuần, tháng tùy thích.
–  Mở Coltrol Panel –> Scheduled Tasks rồi đưa vào các thông tin theo tùy chọn của bạn.

* Bổ sung :
–  Trường hợp chỉ muốn tự động sao lưu một CSDL nào đó thì chỉ cần comment một số đoạn trong file T-SQL như sau :
* Code:
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
–SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)
–WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = 8
/*Change disk location here as required*/
SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”C:\Saoluu\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
EXEC (@SQL)
–SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN (‘TEMPDB’)
END
–  Muốn biết database_id bằng bao nhiêu thì chỉ việc copy câu lệnh sau đưa vào tab Query trong SQL chạy thử sẽ hiện ra danh sách các CSDL hiện có.

Gửi phản hồi

Please log in using one of these methods to post your comment:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s