データベース・バックアップの圧縮
1868 ワード
-- 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