In-depth Study on Compressed Backups in SQL Server and Its Storage Needs

In this article we learn about compressed backups in SQL Server and compare them with Trace Flag

In SQL Server 2008 and all its later editions, users are given access to what is called the compressed backup. Meaning once you backup your database you can also compress it to reduce the amount of space it occupies. But this is something a lot of SQL users or DBAs would already know, in this article we would be discussing how exactly are compressed backups created with a predefined initial size.

One of the most logical answers given to this query is that the compression feature in SQL Server calculates the storage space that is available for the backup on the disk. And on the basis of this calculation, it gives the backup an initial size.  This brings us to the conclusion that compression is actually occurring at Memory, and not on the disk. To get into the details of this logic, continue reading.

Initial Backup

To start with, we will take our initial backup as the reference. You can also make use of a test backup. Let’s say it is currently of 2.5 GB. Now if you are making use of a SQL Server edition after 2008, your backups would get compressed automatically, as the backup process begins. At this stage you would be able to see an initial size that has been set for the backup. This is usually one-third of the size of the database.

Once the process of backup is complete, you will be able to see exactly how much space is being taken up by the compressed backup. More than often it is lesser than the earlier estimated space. This indicates that SQL has not only used lesser than estimated amount of space, but also freed the extra disk space for the operating system to utilize.

Using Trace Flag

To compare the results derived above, we will now make use of trace flag 3042. This will enable us to incrementally build the SQL Server backup instead of allowing it to pre-determine the size of the file. Through these two separate backup files, we will be able to make a comparison.

When you begin making use of Trace Flag, you will be able to notice a clear increase in the amount of disk space as the backup process continues. This is precisely what the trace flag does, it ensures that the files grow only when they are required to grow. Once this process comes to an end, you will also come to the conclusion that the size of both the backups is actually the same. This explains that SQL Server anyway never allocates a backup size that is equal to the size of the database.

Backup compression for SQL Server databases is one of the crucial features that users make use of. The databases stored in the system are anyway heavy files containing large amounts of data, so the key thing to do is to ensure that data is regularly backed up. For backing up data you can either make use of existing options, or use third party tools that specialize in sql recovery.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including accdb repair and sql recovery software products. For more information visit https://www.datanumen.com/