Post reply

Windows 2000 native compression

  • March 3, 2004 at 4:06 pm

    #160512

    Hi,

    Does anyone know of any issues with backing up SQL server to folders on a Windows 2000 file server that has file compression enabled?

    Does it cause any problems when restoring?

    What about windows 2000 encryption?

    We are testing the compression setting, I just wondered if anyone had experience with this.

    Thanks,

     

    Glen

  • March 3, 2004 at 5:29 pm

    #497140

    First of all, Microsoft doesn't recommend to backup to compressed folders (with no clear explanation why)!

    On one of our servers we had to compressed the backup folders couple month ago when the total size of backups went over 100GB with some individual .BAKs at 10-15GB. No major problems to report. However, the backup jobs now run 4-5 times longer than it was before. Plus, the CPU load is high - up to 90-100% sometime while the normal load is ~45-55%.

    I personally would not go with compression if we'd have other options.

     

  • March 4, 2004 at 12:35 am

    #497160

    We used to do this, a couple of years ago so this problem might have already been fixed.

    If a single database backup file goes over a limit (I seem to remember it was 2GB), the backup fails with no clear error message.  The way around it for us was to backup a single database to multiple backup files each less than the limit (whatever it was)

    PSS could replicate the issue and verbally confirmed that there was some problem but fell back on the point raised by the previous poster "this is not supported or recommended"

    Like I say this may no longer be an issue now

     

  • March 4, 2004 at 3:24 am

    #497199

    All our backups are done to a compressed folder (Backupsize: > 15 Gb per db).

    We never had any problems with the backup or restore of these files; we use the restore option often for test purposes, and it never failed.

  • March 4, 2004 at 6:23 am

    #497239

    Same here, our backups have always been done to compressed folders and we've never had any problems. We're backing up ~150GB nightly, with the largest single files over 30GB.

    I've never done any timing tests of compressed vs. uncompressed, however disk fragmentation gets pretty bad on a partition with compressed folders. Backups to a clean drive run approx 3x faster than backups to that same drive after a week, but then things stabilize and we get consistent performance after that.

     

  • March 4, 2004 at 7:56 am

    #497294

    We had problems from the first day after we turned on compression.  On large backup jobs we would often get failed jobs because the machine couldn't keep up. 

    As a solution we now backup the database and then zip the file (and password protect it).  However, in the near future we are getting some larger and faster disks so we won't have to zip. 

    There are also products that will backup and compress the file for you.  dbAssociates LiteSpeed to name one.  Haven't used it but an associate swears by it for her large backup jobs. 

    SJ

    Accepting all invites

  • March 4, 2004 at 8:00 am

    #497299

    We use litespeed and I can honestly not say a bad word about it... it does exactly what it says on the tin!

     

  • March 4, 2004 at 9:26 am

    #497333

    Thanks for the feedback everyone.  We are going to give a try.  Since everyone is sharing the size of their backups I will divulge that we are packrats and are backing up nearly a Terabyte of SQL files along with a silly amount of transaction logs.

    Thanks again,

    Glen

  • March 4, 2004 at 12:51 pm

    #497379

    We backed up to a compressed drive for a while, and had no problems with the procedure per se. But the original purpose was not to save disk space; the idea was to save transfer time when the disk was subsequently backed up to tape. Unfortunately it didn't work, becuase Win 2K helpfully uncompressed the files before the backup software got them!!

    Now I backup to an uncompressed disk and then zip them up for the tape backup.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply