There are many third products that do backup database and compressing file for you. I'd like to share some ideas about how to compress a huge .BAK file if you don't have a budget to buy third products.
Let me see we do a full backup of the database that creates a .BAK file plus a date when backup have been taken.
DECLARE FileName VARCHAR(100),@Date VARCHAR(10)
SET @Date =CONVERT(VARCHAR(10),GETDATE(),112)
SELECT @FileName = 'C:\BackupSQL\MyDataBase' + @Date+'.bak'
BACKUP DATABASE EXM_IscarData
TO DISK = @FileName
I forgot to mention that you need WINRAR program to be installed on the server.
Now that we have .BAK file ,create two batch files (one for compression, second one for restore) that contain
WINRAR a C:\BackupSQL\MyDataBase%1 C:\BackupSQL\MyDataBase%1.BAK
DEL "C:\BackupSQL\MyDataBase%1.BAK " ---Delete .BAK file
---to restore from .RAR file
WINRAR x C:\BackupSQL\MyDataBase%1 c:\
Put those files in C:\Windows\system32 directory.
The last thing is to call this command at Job's step
Decide what you are going to do restore or backup operations.
DECLARE @Str VARCHAR(100)
SET @Str = 'BatchFileName.bat ' + @Date
EXEC master.dbo.XP_CMDSHELL @Str, NO_OUTPUT
I'm sure that you need to put in a logic to restore or backup database.So finally you will have compresed file with .RAR extention which saves disk's space.