Đôi khi những thứ có vẻ phức tạp lại dễ dàng hơn nhiều khi bạn tận dụng sức mạnh của T-SQL để thực hiện các công việc lặp đi lặp lại. Một trong những công việc này có thể là cần phải sao lưu tất cả các cơ sở dữ liệu trên máy chủ của bạn. Đây không phải là một việc lớn, nếu bạn có một số ít các cơ sở dữ liệu, nhưng tôi đã thấy một vài máy chủ, nơi có hơn 100 cơ sở dữ liệu trên cùng một SQL Server. Bạn có thể sử dụng Enterprise Manager để sao lưu cơ sở dữ liệu hoặc thậm chí sử dụng kế hoạch bảo dưỡng, nhưng sử dụng T-SQL là một phương pháp đơn giản hơn nhiều và nhanh hơn.
Với việc sử dụng của T-SQL bạn có thể tạo ra các bản lưu dự phòng, nén lại để tiết kiệm không gian và tự xóa đi các bản lưu cũ. Quá tuyệt vời, một ứng dụng Backup tốt nhất thì các tính năng cơ bản cũng chỉ như thế.
Dưới đây là một kịch bản T-SQL dành cho SQL Server 2000 trở lên, cho phép bạn sao lưu từng Database đang hoạt động (bỏ qua các database đang bị lỗi, offline...). Để kịch bản này hoạt động, bạn sẽ cần thay đổi một số thông tin cấu hình mà tôi đã ghi chú trong đó.
Kịch bản này sao lưu mỗi database của bạn thành từng tập tin theo dạng DATABASE-NAME.BAK, cuối cùng nó sẽ nén tất cả tập tin sao lưu được thành một tập tin đặt tên theo dạng YYYYMMDD.ZIP lưu vào thư mục mà bạn cấu hình. Sau khi quá trình nén hoàn tất, đoạn mã cuối cùng sẽ kiểm tra trong thư mục có bao nhiêu tập tin cũ hơn, những tập tin nén cũ hơn số ngày bạn cấu hình sẽ tự động bị xóa.
SET NOCOUNT ON ;
-- Phiên bản: 1.3 - 2009-12-07 - Tương thích SQL 2000
-- Hướng dẫn
-- Chép tập tin 7za.exe vào thư mục chứa backup, vd: C:\DatabaseBackup
-- Tạo Job Schedule trong SQL Server để chạy đoạn SCRIPT, mỗi ngày chạy 1 lần vào 3h sáng
-- Sửa @CFG_BACKUP_PATH = <Thư mục chứa Backup> - thư mục này không được có khoảng trắng hoặc tên dài
----- Biến cấu hình
DECLARE @CFG_BACKUP_PATH NVARCHAR(256)
DECLARE @CFG_DAYS_DELETE INT
----- Cấu hình thư mục Backup, chú ý: đường dẫn không được có khoảng trắng
SET @CFG_BACKUP_PATH = 'C:\DatabaseBackup'
----- Cấu hình số ngày giữ bản Backup
SET @CFG_DAYS_DELETE = 30
DECLARE @Today DATETIME
DECLARE @TodayName CHAR(8)
SET @Today = GETDATE()
SET @TodayName = CONVERT(CHAR(8), @Today, 112)
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @cmd VARCHAR(256)
----- Tạo thư mục tạm
DECLARE @TempDir VARCHAR(256)
SET @TempDir = @CFG_BACKUP_PATH + CHAR(92) + CONVERT(VARCHAR(256), NEWID())
SET @cmd = 'md ' + @TempDir
EXEC xp_cmdshell @cmd, no_output
----- Bảng chứa danh sách DB
DECLARE @dbList TABLE
(
dbno INT IDENTITY,
dbname NVARCHAR(256)
)
----- Lấy danh sách DB đang online
INSERT INTO @dbList ( dbname )
SELECT name
FROM master.dbo.sysdatabases
WHERE ( name NOT IN ( 'tempdb' ) )
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
------ Bắt đầu Backup
SELECT @id = dbno,
@name = dbname
FROM @dbList
WHERE dbno = 1
WHILE @@ROWCOUNT = 1
BEGIN
PRINT N'++ Sao lưu Database: ' + @name
SET @path = @TempDir + CHAR(92) + @name + '.bak'
BACKUP DATABASE @name TO DISK = @path
SELECT @id = dbno,
@name = dbname
FROM @dbList
WHERE dbno = @id + 1
END
PRINT N'++ Nén thư mục: ' + @TempDir
----- Xóa tập tin RAR nếu đã có
SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + CHAR(92) + @TodayName + '.ZIP'
EXEC xp_cmdshell @cmd, no_output
DECLARE @Count INT
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
----- Nén
SET @cmd = @CFG_BACKUP_PATH + '\7za.exe a -bd -y -tzip -mx2 '
SET @cmd = @cmd + @CFG_BACKUP_PATH + CHAR(92) + @TodayName + '.ZIP ' + @TempDir
+ '\*.bak"'
-- PRINT @cmd
EXEC xp_cmdshell @cmd, no_output
SET @Count = DATEDIFF(second, @StartTime, GETDATE())
PRINT N'++ Thời gian nén: ' + CONVERT(VARCHAR, @Count) + ' giây'
SET @Count = DATEDIFF(second, @Today, GETDATE())
PRINT N'++ Thời gian xử lý: ' + CONVERT(VARCHAR, @Count) + ' giây'
---- Xóa thư mục tạm
SET @cmd = 'rd /s /q ' + @TempDir
EXEC xp_cmdshell @cmd, no_output
----------------------------------- Xóa các file .RAR cũ hơn @CFG_DAYS_DELETE
DECLARE @OlderDateName CHAR(8)
SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112)
----- Lấy danh sách tập tin
CREATE TABLE #delList
(
subdirectory VARCHAR(256),
depth INT,
[file] BIT
)
INSERT INTO #delList
EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1
DELETE #delList
WHERE RIGHT(subdirectory, 4) <> '.ZIP'
SELECT @Count = COUNT(1)
FROM #delList
PRINT N'++ Số phiên bản hiện có trong thư mục: ' + CONVERT(NVARCHAR, @Count)
SELECT TOP 1
@name = subdirectory
FROM #delList
WHERE LEN(subdirectory) = 12
AND RIGHT(subdirectory, 4) = '.ZIP'
AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName
WHILE ( @@ROWCOUNT = 1 )
BEGIN
PRINT N'++ Xóa phiên bản: ' + @name
SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + CHAR(92) + @name
EXEC xp_cmdshell @cmd, no_output
DELETE #delList
WHERE subdirectory = @name
SELECT TOP 1
@name = subdirectory
FROM #delList
WHERE LEN(subdirectory) = 12
AND RIGHT(subdirectory, 4) = '.ZIP'
AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName
END
DROP TABLE #delList
---------------------------------------- Hoàn tất xóa phiên bản cũ
PRINT N'++ Hoàn tất.'
PRINT ''
PRINT ''
PRINT ''
Trong kịch bản này có sử dụng công cụ nén miễn phí 7-Zip, bạn có thể tải về miễn phí ở địa chỉ: http://www.7-zip.org/download.html - chú ý là bạn cần tải phiên bản dòng lệnh (Command Line Version).
Cài đặt:
- Bạn cần tạo một thư mục chứa bản lưu, ví dụ: D:\BACKUP
- Chép tập tin 7za.exe vào thư mục D:\BACKUP
- Nếu bạn sử dụng phiên bản SQL Server 2005 trở lên, bạn cần bật "xp_cmdshell" - tính năng này cho phép chạy lệnh ngoài trong kịch bản. Bạn chỉ cần chạy đoạn T-SQL:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
Có 2 cách để kịch bản này hoạt động tự động:
- Cách 1: Cấu hình bằng Windows Scheduler, kịch bản được chạy bằng chương trình ISQL.EXE hoặc SQLCMD.EXE tùy theo phiên bản SQL Server của bạn.
- Cách 2: Tạo một Job nếu SQL Server của bạn có cài đặt SQL Agent.
Rất vui nếu bài viết này giúp các bạn tiết kiệm nhiều thời gian với công việc sao lưu nhàm chán.
