Backup compression in SQL server 2008R2 and above 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.
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.