Today, apps development has never been easier and faster and it provided anyone the means to gather vast amounts of key information needed by one’s business. The explosion of various data sources that can possibly be mined of useful data that might be key to one’s business adds to the pressure of managing tons of data.
The size of data to be managed has definitely gone over the roof and forces everyone to seriously take a look at how they should keep all these. Data occupies space in bits. The process in which these bits are written to some storage medium takes time. As the volume of bits increases, the time to store these bits to some digital storage also increases. Of course everybody knows that. But almost 7 years since this feature in SQL Server became available to help in managing data storage, it is still a surprise that not too many has utilized it. Some even aren’t aware that such feature exist in SQL Server.
This simple SQL Server feature is gold. It is the capability of SQL Server to do compressed backup be it full, differential or transaction log. How golden is this feature? Here I tested a 25GB test database to see some numbers:
| mssql uncompressed | mssql compressed | uncompressed + 7Zip normal | uncompressed + 7Zip fastest | |
| Backup size | 19.1 GB (20,587,249,664 bytes) | 3.49 GB (3,752,882,176 bytes) | 1.14 GB (1,234,287,895 bytes) | 1.67 GB (1,799,850,005 bytes) |
| Backup speed | 309 secs | 114 secs | 309 secs (backup) 5040 secs (compression) | 309 secs (backup) 897 secs (compression) |
Clearly, there is a lot of benefit here for anyone who will use compression in backups. Microsoft provided something neat and very convenient and seamless.
totogamboa.wordpress.com 