Today in one of the online communities, a question was asked about compressing the database backups taken in SQL Server 2000 and SQL Server 2005. SQL Server 2008 has the Backup Compression feature built-in. But in SQL Server 2000 & SQL 2005 there is no such feature. Hence this has to be done using third party compression tools.
A month ago, I had this requirement on couple of servers. The drive holding the backup files was running out of space and the customer was in the process of adding space to the drive. Since having atleast 2 full backups was critical from DR perspective, I had written the below script which compresses the previous day’s backup. After the compression is successful, the original .bak file would be deleted. I had used my favorite compression tool, 7-zip for this purpose. As always the coding standard was not very polished but it had helped to accomplish the need of the hour.
set nocount on if exists (Select name from sysobjects where name='trcfiles') drop table trcfiles create table trcfiles (files varchar(100)) go truncate table trcfiles go insert into trcfiles(files) exec master..xp_cmdshell 'dir D:MSSQLMSSQL.1MSSQLBackup*.bak /OD /B' go DECLARE @filename varchar(200) DECLARE @CMD varchar(1000) DECLARE trc_cursor CURSOR FOR select top 1 * from trcfiles where files is not null open trc_cursor FETCH NEXT FROM trc_cursor INTO @filename WHILE @@FETCH_STATUS=0 begin IF (SELECT COUNT(*) from trcfiles where files is not null) < 2 goto stopit SET @CMD = 'D:MSSQLMSSQL.1MSSQLBackup7z7z.exe a D:MSSQLMSSQL.1MSSQLBackup' + @filename + '.zip D:MSSQLMSSQL.1MSSQLBackup' + @filename --print @cmd exec xp_cmdshell @cmd SET @CMD = 'del D:MSSQLMSSQL.1MSSQLBackup' + @filename + ' /Q' --print @cmd exec xp_cmdshell @cmd FETCH NEXT FROM trc_cursor INTO @filename end stopit: close trc_cursor deallocate trc_cursor drop table trcfiles