August 20, 2007 at 8:30 am
Guys,
I recently compressed the folders where we store backups, as suggested here: http://www.databasejournal.com/features/mssql/article.php/3064361. Once I did it, however, the backup time increased from 1 hour to 5 hours.
Can anyone tell me whether this is normal and if there is a way to avoid this?
Thanks a lot!
August 20, 2007 at 8:50 am
Never heard of this, but maybe a workaround : have you tried backing up to a different folder then moving to that folder?
August 20, 2007 at 9:06 am
This is fairly normal behavior, since it requires the system to compress the file on the fly. You may be able to get it to run faster by backing up to multiple files at the same time.
backup database [pubs]
to
disk = 'P:\MSSQL\Backup\pubs\pubs_db_200708201100_001.BAK' ,
disk = 'P:\MSSQL\Backup\pubs\pubs_db_200708201100_002.BAK' ,
disk = 'P:\MSSQL\Backup\pubs\pubs_db_200708201100_003.BAK' ,
disk = 'P:\MSSQL\Backup\pubs\pubs_db_200708201100_004.BAK' ,
disk = 'P:\MSSQL\Backup\pubs\pubs_db_200708201100_005.BAK'
with
init,
stats = 5
August 20, 2007 at 9:40 am
Normally, a compressed backup takes less time than a regular backup if we rule out difference in backup devices. It is always true in my experience (I cannot say there is no exception in other DBAs). A compressed backup using LiteSpeed takes a fraction of time of a regular backup in both MS SQL Server and Oracle. A compressed backup using built-in compression in Sybase also takes a fration of time of a regular backup. Using BackupExec also takes a fraction of time of a regular backup.
My understanding is, the rate of a system to compress data is faster that of a system to write data on a disk.
From your referenced article, may we draw a conclusion that backup a file using windows built-in function is not good from the pointview of performance ?
August 20, 2007 at 9:49 am
I have never seen a backup to a compressed folder take less time than a backup to an uncompressed folder.
If you read the OPs link, that it what it is about, not using a utility like LiteSpeed.
August 20, 2007 at 10:39 pm
Compressed folders are slow, no doubt about it, especially if the compressed folder is on another server - the data still crosses the network uncompressed...
That said, a product like LiteSpeed (Quest), SQLBackup (RedGate) or SQLSafe (Idera) might be a good investment if you really need backup compression - all of them compress the data before writing to disk and are usually faster than even a native backup. I think you'll also find that the compression rate between a compression program like LiteSpeed and a compressed volume is substantially better when using the compression program.
Joe
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply