数据库 备份 压缩

--打开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

　　

转载于:https://www.cnblogs.com/jonhson/archive/2011/11/03/2234874.html