Compress database backups in SQL Server 2000 and 2005

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

2 thoughts on “Compress database backups in SQL Server 2000 and 2005

  1. Pingback: Drop and re-create System Stored Procedures in SQL Server 2000 | SQLServerPedia

  2. Pingback: Run PSSDiag through a SQL Agent job | SQLServerPedia

Comments are closed.