数据库 备份 压缩
--打开XP_CMDShell命令 -- To allow updates. EXEC sp_configure 'allow updates', 0 GO -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO declare @database_name varchar(100) declare @bakname varchar(100) declare @bakfile varchar(100) declare @rarfile varchar(100) declare @rarcmd varchar(256) declare dbname cursor for select name from sys.databases where database_id > 4--游标取所有数据库名 open dbname FETCH NEXT FROM dbname INTO @database_name WHILE (@@FETCH_STATUS = 0) begin set @bakfile = 'F:\backup\' set @rarfile = 'F:\backup\' set @bakname = @database_name+cast(Year(GetDate()) as varchar(4))+cast(Month(GetDate()) as varchar(2))+cast(Day(GetDate()) as varchar(2)) set @bakfile = @bakfile + @bakname + '.bak' set @rarfile = @rarfile + @bakname + '.rar' BACKUP DataBASE @database_name TO DISK = @bakfile WITH INIT , NOUNLOAD , NAME = @bakname, NOSKIP , STATS = 10, NOFORMAT set @rarcmd ='C:\Progra~1\WinRAR\WinRAR.exe a -sv- -v4481m'+@rarfile+' '+@bakfile //分卷压缩 exec master..xp_cmdshell @rarcmd FETCH NEXT FROM dbname INTO @database_name end CLOSE dbname DEALLOCATE dbname --关闭XP_CMDShell命令 -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 0 GO -- To update the currently configured value for this feature. RECONFIGURE GO