SQL SERVER – Playing with Backups and Compression

The era of innovation with SQL Server administration side of things keep happening, it is strange that a lot of us miss these capabilities getting added into the system. I have seen processes define the way how DBA’s work. In a lot of places, the processes, scripts and behaviors are brought forward without a second thought. I wanted to share a recent story that happened at one of my client’s place. Let us talk my experience about playing with backups and compressions.

SQL SERVER - Playing with Backups and Compression backupcompression

I was brought in by one of my clients for performance tuning exercise. This was a routine procedure that I undertake. When I was at their office, one of the DBA asked if I could also look at their standard procedures used for daily Agent jobs. As I had a few hours left in that exercise, I thought to spend time with them. I went about looking at some of the maintenance plans and the scripts they were using. I went about asking when these were made, how they were brought forward from various migrations and so on. This is when I realized, some of the scripts for backups used by them were created for SQL Server 2005 time and were running easily even after 2 upgrades.

I went about asking why they were not using the backup compression feature of SQL Server? This is when the DBA asked me about how easy it change the script and if there are advantages that I need to know. I helped them change the script and this was a simple yet powerful change for them. The backup size reduced drastically. To showcase the same, let me run a script and show you.

-- Normal Backup performed on the Database
BACKUP DATABASE [AdventureWorks2016] TO DISK  
= 'C:\Backup\AdventureWorks_uncompressed.bak' WITH STATS = 10, NO_COMPRESSION, FORMAT;
GO
-- Normal Backup (with Compression enabled) performed on the Database
BACKUP DATABASE [AdventureWorks2016] TO DISK  
= 'C:\Backup\AdventureWorks_compressed.bak' WITH STATS = 10, COMPRESSION, FORMAT;
GO

As you can see, the above command changes just in a single word and it can start using the backup compression abilities.

SQL SERVER - Playing with Backups and Compression backup-compression-size-01

Our example was the easiest and you can see from the backup sizes that this has got us 5 times disk compression. This is a powerful and lesser appreciated feature when it comes to working with SQL Server.

I am sure you would have used this feature in your environment too, do let me know how efficient this has been for you? Send a note via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Backup, SQL Scripts, SQL Server, SQL Utility
Previous Post
SQL SERVER – System Function @@IDLE to Find System Ideal Time
Next Post
SQL SERVER – How to Find the In-Memory OLTP Tables Memory Usage on the Server

Related Posts

12 Comments. Leave new

  • Great, Thanks

    Reply
  • Brilliant! I had fallen into the trap of migrating processes from previous versions and missed this added feature. My backups have reduced from 34gb to 3gb so I can now get 10 backups in the same space.
    Just a word for future readers, the FORMAT option Mr. Pinal uses does NOT format the whole disk so don’t worry. It is required if you are overwriting a previous backup set and changing between compression and no_compression. The STATS option just shows the backup progress so is not required if you are creating unattended backups.

    Reply
  • Rémi BOURGAREL
    November 28, 2016 3:53 pm

    You can also see that the time needed to do the backup id most of the time greatly reduced (we divided our backup site by 6 and the backup time by 2 or 3). I wonder why it’s not enabled by default or event why they put it as a configuration setting

    Reply
    • Rémi there is a configuration option for ‘Backup Compression Default’. Whilst I would recommend using it in most situations, there is a CPU overhead for the compression so it may not be wise to use it by default for all backups

      Reply
  • Hi Pinal

    I want to know that are there any disadvantages of compressed backup other then CPU overhead?Please guide.

    Reply
    • CPU is only one which I know of. I also think in earlier version compression and encryption was not working well together, which have been fixed now. You need to check Microsoft official documentation.

      Reply
  • Hi Pinal, Then how can restore compressed backup as normal backup?

    Reply
  • Ivan Bonanno
    July 27, 2021 7:45 pm

    Did the compressed backup and restore test and looks works the same to restore.

    Reply

Leave a Reply