PDA

View Full Version : Script để sao lưu tất cả Database trong SQL Server



trumserver
28-02-2010, 12:29 AM
Đô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.

001 SET NOCOUNT ON ;

002 -- Phiên bản: 1.3 - 2009-12-07 - Tương thích SQL 2000

003 -- Hướng dẫn

004 -- Chép tập tin 7za.exe vào thư mục chứa backup, vd: C:\DatabaseBackup

005 -- 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

006 -- 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

007

008 ----- Biến cấu hình

009 DECLARE @CFG_BACKUP_PATH NVARCHAR(256)

010 DECLARE @CFG_DAYS_DELETE INT

011

012 ----- Cấu hình thư mục Backup, chú ý: đường dẫn không được có khoảng trắng

013 SET @CFG_BACKUP_PATH = 'C:\DatabaseBackup'

014 ----- Cấu hình số ngày giữ bản Backup

015 SET @CFG_DAYS_DELETE = 30

016

017 DECLARE @Today DATETIME

018 DECLARE @TodayName CHAR(8)

019 SET @Today = GETDATE()

020 SET @TodayName = CONVERT(CHAR(8), @Today, 112)

021

022 DECLARE @id INT

023 DECLARE @name VARCHAR(50)

024 DECLARE @path VARCHAR(256)

025 DECLARE @cmd VARCHAR(256)

026

027 ----- Tạo thư mục tạm

028 DECLARE @TempDir VARCHAR(256)

029 SET @TempDir = @CFG_BACKUP_PATH + CHAR(92) + CONVERT(VARCHAR(256), NEWID())

030 SET @cmd = 'md ' + @TempDir

031 EXEC xp_cmdshell @cmd, no_output

032

033 ----- Bảng chứa danh sách DB

034 DECLARE @dbList TABLE

035 (

036 dbno INT IDENTITY,

037 dbname NVARCHAR(256)

038 )

039

040 ----- Lấy danh sách DB đang online

041 INSERT INTO @dbList ( dbname )

042 SELECT name

043 FROM master.dbo.sysdatabases

044 WHERE ( name NOT IN ( 'tempdb' ) )

045 AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

046

047

048 ------ Bắt đầu Backup

049 SELECT @id = dbno,

050 @name = dbname

051 FROM @dbList

052 WHERE dbno = 1

053 WHILE @@ROWCOUNT = 1

054 BEGIN

055 PRINT N'++ Sao lưu Database: ' + @name

056 SET @path = @TempDir + CHAR(92) + @name + '.bak'

057

058 BACKUP DATABASE @name TO DISK = @path

059

060 SELECT @id = dbno,

061 @name = dbname

062 FROM @dbList

063 WHERE dbno = @id + 1

064 END

065

066 PRINT N'++ Nén thư mục: ' + @TempDir

067

068 ----- Xóa tập tin RAR nếu đã có

069 SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + CHAR(92) + @TodayName + '.ZIP'

070 EXEC xp_cmdshell @cmd, no_output

071

072 DECLARE @Count INT

073 DECLARE @StartTime DATETIME

074 SET @StartTime = GETDATE()

075 ----- Nén

076 SET @cmd = @CFG_BACKUP_PATH + '\7za.exe a -bd -y -tzip -mx2 '

077 SET @cmd = @cmd + @CFG_BACKUP_PATH + CHAR(92) + @TodayName + '.ZIP ' + @TempDir

078 + '\*.bak"'

079 -- PRINT @cmd

080 EXEC xp_cmdshell @cmd, no_output

081

082 SET @Count = DATEDIFF(second, @StartTime, GETDATE())

083 PRINT N'++ Thời gian nén: ' + CONVERT(VARCHAR, @Count) + ' giây'

084 SET @Count = DATEDIFF(second, @Today, GETDATE())

085 PRINT N'++ Thời gian xử lý: ' + CONVERT(VARCHAR, @Count) + ' giây'

086

087 ---- Xóa thư mục tạm

088 SET @cmd = 'rd /s /q ' + @TempDir

089 EXEC xp_cmdshell @cmd, no_output

090

091

092 ----------------------------------- Xóa các file .RAR cũ hơn @CFG_DAYS_DELETE

093 DECLARE @OlderDateName CHAR(8)

094 SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112)

095

096 ----- Lấy danh sách tập tin

097 CREATE TABLE #delList

098 (

099 subdirectory VARCHAR(256),

100 depth INT,

101 [file] BIT

102 )

103 INSERT INTO #delList

104 EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1

105 DELETE #delList

106 WHERE RIGHT(subdirectory, 4) <> '.ZIP'

107

108 SELECT @Count = COUNT(1)

109 FROM #delList

110 PRINT N'++ Số phiên bản hiện có trong thư mục: ' + CONVERT(NVARCHAR, @Count)

111

112 SELECT TOP 1

113 @name = subdirectory

114 FROM #delList

115 WHERE LEN(subdirectory) = 12

116 AND RIGHT(subdirectory, 4) = '.ZIP'

117 AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName

118

119 WHILE ( @@ROWCOUNT = 1 )

120 BEGIN

121 PRINT N'++ Xóa phiên bản: ' + @name

122 SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + CHAR(92) + @name

123 EXEC xp_cmdshell @cmd, no_output

124

125 DELETE #delList

126 WHERE subdirectory = @name

127

128 SELECT TOP 1

129 @name = subdirectory

130 FROM #delList

131 WHERE LEN(subdirectory) = 12

132 AND RIGHT(subdirectory, 4) = '.ZIP'

133 AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName

134 END

135

136 DROP TABLE #delList

137 ---------------------------------------- Hoàn tất xóa phiên bản cũ

138

139

140 PRINT N'++ Hoàn tất.'

141 PRINT ''

142 PRINT ''

143 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:
view sourceprint?1 EXEC master.dbo.sp_configure 'show advanced options', 1

2 RECONFIGURE

3 EXEC master.dbo.sp_configure 'xp_cmdshell', 1

4 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.