2

Recently we got a new server to take care of and found that there are two data drive:

S: 1 TB where resides 10 databases(mdfs and ldf's, though they should be on different drives but that's how we got the set up) where max size of a DB being 780 GB and rest pretty small.

X: similar scenario here as well a DB of 720 GB resides rest small and bits, total of 10 DB's

Now, we've been asked to perform the backup strategy and for that i am sure we are short of backup drive as there does not exist any other drives apart from C: and above two.

Since this being sql server 2008R2 enterprise, Compression feature has been enabled and we are good to take compressed backups:

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups, means using the compression feature how can i predict close to the amount of disk space required to backup these 20 user databases.

  • 1
    You should assume the same size because there's not an easy way to accurately predict compression levels, especially depending on future data change, future addition of features that affect how compression works (like TDE). Also unless you have trace flag 3042 enabled, you will need the size of the full backup available before compression actually makes it smaller anyway... – Aaron Bertrand Jan 27 '15 at 20:12
1

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups

From Books Online Document. If you see the last part it says that

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

When you start compressed backup you would see some size of backup file created on the drive but this would not be the correct size during backup operation it can grow and final size would increase. You can initiate a backup with compression and see what is the size then tentatively multiply it by number of days you want to keep it plus few more space. This would again give you tentative size of backup drive. I would also say it would be safer to more space to backup drive

1

You can't completely predict it, but you can backup smaller databases with compression and if the data sets are similar (based on the factors listed below), you can predict a ratio that you may be able to apply to the larger databases.

It's easiest to just quote the MSDN on compression here:

To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size

For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained. Major factors include:

  • The type of data.
  • Character data compresses more than other types of data.
  • The consistency of the data among rows on a page.
  • Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.
  • Whether the data is encrypted.
  • Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.
  • Whether the database is compressed.
  • If the database is compressed, compressing backups might not reduce their size by much, if at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.