Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: backing up large pre-grown DB's

    So we have a few servers with about 2TB DB's (four 490GB files) and we're looking at options to back them up and store the backup files. Since we're already in Amazon's cloud we were thinking of just filling up 1TB volumes and imaging the volume to their cheap S3 storage.

    My question is: 'What are my options to minimize the file size of the backup files?'.

    Since we pre-grow our DB's we have a decent amount of free space that I believe gets backed up and will contribute to the large *.BAK files. We're looking into buying a product like HyperBac from red-gate (Silent compression for faster, smaller SQL Server backups – SQL HyperBac) which we will probably end up buying regardless of the answer to this question. I think we'd still benefit from being able to backup only the data-portion of the files and not the empty space. Is this possible without shrinking the DB prior to performing the backup (and then expanding when done)?

    We're running SQL 2008 Standard Edition (so no Enterprise features are available to us).

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Without using compression you're still gonna be backing up only the used portion of the data devices. I did a little experiment awhile back with 2 test databases (1GB and 10GB), and in Standard Edition environment the resulting backup sizes were 2MB and 8MB respectively. Both databases were empty.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Thanks rdj...

    same question for transaction log files, we pregrew our log file for the same DB mentioned above to 490GB, only 30GB is being used however (Full recovery model).

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    then only 30GB will be backed up, so only the used size of the log will be backed up. You can validate this by reviewing the dbo.backupset table (backup_size column).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •