Thursday, January 26, 2012

Compress database backups in SQL Server 2000 & 2005

A question by interviewer 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.

But i collected this script flog one of website:

Just try it and give me the working status...

set nocount on
    if exists (Select name from sysobjects where name='trcfiles')
    drop table trcfiles
    create table trcfiles (files varchar(100))
    truncate table trcfiles
    insert into trcfiles(files) exec master..xp_cmdshell 'dir D:\MSSQL\MSSQL.1\MSSQL\Backup\*.bak /OD /B'
    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
        IF (SELECT COUNT(*) from trcfiles where files is not null) < 2 goto stopit
        SET @CMD = 'D:\MSSQL\MSSQL.1\MSSQL\Backup\7z\7z.exe a D:\MSSQL\MSSQL.1\MSSQL\Backup\' + @filename + '.zip D:\MSSQL\MSSQL.1\MSSQL\Backup\' + @filename
        --print @cmd
        exec xp_cmdshell @cmd
        SET @CMD = 'del D:\MSSQL\MSSQL.1\MSSQL\Backup\' + @filename + ' /Q'
        --print @cmd
        exec xp_cmdshell @cmd
        FETCH NEXT FROM trc_cursor INTO @filename
    close trc_cursor
    deallocate trc_cursor
    drop table trcfiles

1 comment:

  1. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...


Popular Posts