How to compress backup files and delete old backups?

[ebrahim abd]

dear friends,

i wrote a job to backup my server
but because of my hard disk limittition i want to write another job to compress my bak files and delete files that are older than 20 days .

my first job is :

declare @path nvarchar(1000) 
set @path='D:\MySolution\data\BackUp\Auto\mainserver_'+convert(nvarchar(10),getdate(),112)+'.bak'
BACKUP DATABASE [mainserver] TO  DISK =@path  WITH  INIT

please help me

[ck9663]

You're going to need to have an archiving/compressing software for that (winrar, winzip, etc..). You're going to need the Command Line add-in/support for that software and call it from sql server.

Good Luck!!!

~~ CK