sql server資料庫備份壓縮拷貝執行個體

--資料庫備份壓縮拷貝執行個體：前提要安裝RAR壓縮軟體

--聲明變量

declare @day varchar(10),@dbname varchar(20),@filename varchar(100),@cmd_str varchar(500),@local_name varchar(500)

--初始變量值

set @day = convert(varchar(10),getdate(),112)

set @dbname='database'

set @filename=@dbname+@day

set @local_name='y:\backup\database\'+@filename

--sql備份

backup database database to disk=@local_name with init

--删除備份

set @cmd_str='del y:\backup\database\'+@dbname + convert(varchar(10),DATEADD(DAY,-2,getdate()),112)

EXEC master..xp_cmdshell @cmd_str

--删除RAR

set @cmd_str='del y:\backup\database\'+@dbname+'.rar'

EXEC master..xp_cmdshell @cmd_str

--壓縮到網絡驅動器上

set @cmd_str='"C:\Program Files\WinRAR\WinRAR.exe" a y:\backup\database\'+@dbname+'.rar ' + @local_name

print @cmd_str+char(10)

EXEC master..xp_cmdshell @cmd_str

--網絡認證

EXEC master..xp_cmdshell 'net use w: \\200.168.15.9\backup "baijin830" /user:"WINDOWS-72AMTFX\administrator"',no_output

--網絡拷貝

set @cmd_str='copy x:\backup\database\'+ @dbname+'.rar ' + 'w:\'

print @cmd_str+char(10)

EXEC master..xp_cmdshell @cmd_str

go