Script backup MSSQL
Đô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 đó.
SET NOCOUNT ON ;
-- Chép tập tin 7za.exe vào thư mục chứa backup, vd: C:\DatabaseBackup
-- 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ìnhISQL.EXE hoặc SQLCMD.EXE tùy theo phiên bản SQL Server của bạn. (Trong màn hình Task Scheduler bạn nhấp chọn Create Basic Task để tạo một Task mới > Đặt tên Task là Auto-Backup-Data > Trong cửa sổ Trigger bạn đặt thuộc tính tự động thực thi Task, trong này tôi chọn là Daily nghĩa là Task này sẽ được Windows thực thi mỗi ngày một lần > Màn hình Daily nhấp Next > Tại cửa sổ Action bạn chọn Start a program > Trong Start a program bạn nhấp Browse… > Đi chuyển đến thư mục C:\Program Files\Microsoft SQL Server\90\Tools\Binn > Chọn file SQLCMD.EXE > Click chọn Open the Properties dialog for this task when i click Finish và nhấp Finish > Cửa sổ Properties của Task hiện ra chọn Tab Actions sau đó nhấp chọn Edit > Tại ô Add arguments bạn nhập lệnh sau:-S <your server name or IP> -E -i <local file>
Trong đó:
– <your server name or IP>: là tên hoặc IP của Data Server
– <local file>: là đường dẫn đến file .sql ScriptTrong ví dụ này là
-S 192.168.1.5 -E -i E:\Backup\Database\Autobackup.sql )
- Cách 2: Tạo một Job nếu SQL Server của bạn có cài đặt SQL Agent.