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.

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.

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)