It seems like a good idea to backup SQL Server 2005 databases to a compressed folder, however if your database is larger than roughly 30 GB, you will get the following error:
BackupMedium::ReportIoError: WRITE failure ON backup
device 'G:\Backups\200501220402.BAK'. Operating system
error 33(The process cannot access the file because
another process has locked a portion of the file.).
One way to get around this is to stripe the backup to multiple files, for instance:
BACKUP DATABASE AdventureWorks TO DISK='G:\Backups\200501220402.BAK1', DISK='G:\Backups\200501220402.BAK2', DISK='G:\Backups\200501220402.BAK3' WITH FORMAT, CHECKSUM;
Not ideal, but there are two options:
- Buy LiteSpeed Backup which includes compressed backups
- Wait for SQL Server 2008, which includes compressed backups
#1 by Jeffrey Aven at January 29th, 2008
| Quote
Another option not mentioned, look into HyperBac, allows you to perform SQL 2005/2000 backups with integrated compression and no changes to the backup scripts/procedures
#2 by admin at January 30th, 2008
| Quote
Thanks Jeffrey! I hadn’t heard of HyperBac before.
#3 by Steve Ware at May 18th, 2008
| Quote
Nice note. This post saved me some searching time and is the only thing that addressed this issue that a Google search linked me to. Regards.