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.