Backing up to a NTFS compressed folder

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:

  1. Buy LiteSpeed Backup which includes compressed backups
  2. Wait for SQL Server 2008, which includes compressed backups

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in SQL Server Administration. Bookmark the permalink.

5 Responses to Backing up to a NTFS compressed folder

  1. Jeffrey Aven says:

    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. admin says:

    Thanks Jeffrey! I hadn’t heard of HyperBac before.

  3. Steve Ware says:

    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.

  4. pradeep says:

    Was this issue “error 33(The process cannot access the file because another process has locked a portion of the file.).” on Windows Server 2003 Servers or Windows Server 2008 R2 Servers

  5. Howdy fantastic website! Does running a blog similar to this take a massive amount work? I’ve virtually no understanding of coding but I had been hoping to start my own blog soon. Anyways, should you have any ideas or tips for new blog owners please share. I know this is off subject nevertheless I just needed to ask. Many thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>