Home > Backup and Recovery > SQL Server 2005 Backup Compression

SQL Server 2005 Backup Compression

Well… not “out of the box” as in SQL Server 2008. But that could be done with a script.

The problem though is that comparable to “native” SS2008 compression, compressing database and transaction log files in SS2005 would take times more CPU, I/O and storage and, as a result, would take much more time to run. This is because SS2008 performs compression before writting to disk, whereas here, in SS2005, all files will be written to disk first, then compressed.

Nevertheless, there might be certain cituations where using archiving of database and transaction log backups might come handy.

Any archivator, which supports archiving of large-enough files could be used for this. I use 7-zip for that purpose – it’s free, fast and . Here are two other free archivators: IZArc, PeaZip

Here, you need to enable xp_cmdshell which runs OS commands from withing SS Scheduled Job, not forgetting to disable it after the execution.

Bold marks the custom directories and file names.

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

DECLARE @cmd VARCHAR(1000)
DECLARE @db_bkp_files_dir varchar(100)
DECLARE @archive_destination_dir varchar(100)
DECLARE @archive_name varchar(100)
DECLARE @7z_path varchar(100)
set @archive_destination_dir = ‘D:\Backup_test’ –destination dir
set @7z_path = ‘”C:\Program Files\7-Zip
set @db_bkp_files_dir = ‘D:\Backup_test’ –db backup files origin 
set @archive_name = ‘DBFullBackup_‘ + convert(varchar, getdate(), 112) + substring(replace(convert(varchar, getdate(), 108), ‘:’, ”), 1, 4)
SET @cmd = @7z_path + ‘\7z” a -t7z -mx5 -ms=off ‘ + @archive_destination_dir + ‘\’ + @archive_name + ‘.7z ‘ +  @db_bkp_files_dir + ‘\*’ 
–print @cmd
EXEC xp_cmdshell @cmd

EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: