How Can I Save Space While Doing SQL Backups

In SQL Server there are options of Compressed and Uncompressed Database Backups. Compressed backup are good option with newer versions of SQL (SQL 2008 onward) especially when there is spacing issue. As these are smaller in size than the uncompressed ones of the same data and typically require less Input/Output on device and it increases backup speed significantly.

As everything now a days about saving and making a difference so question arises how much one could save by changing backup type to compressed. Although it depends on various variables but below query will give you an estimate of savings you would make by changing to compressed backups. 

SELECT backup_size/compressed_backup_size FROM msdb.dbo.backupset;

If a compression ratio is around 4:1, that would indicate that you are saving about 75% of disk space.

Obviously it does not come without restrictions as it’s introduced in recent versions of SQL below are some limitations of using compressed backups.

•  Compressed and uncompressed backups cannot co-exist in a media set.
•  Previous versions of SQL Server cannot read compressed backups 
•  NT backups cannot share a tape with compressed SQL Server backups.

  So how do I go about changing the backup compression on my server?

To enabling backup compression at the server level, right-click on the instance name inside of server explorer in SSMS to bring up the Server Properties screen. Go to the 'Database Settings' and you should see the following: 

 

Secondly the same setting could be done on Server level through below  T-SQL Query

EXEC sys.sp_configure 'backup compression default', '1'

GO

RECONFIGURE WITH OVERRIDE

GO

If you want to implement this to a single database it could be done as below 

BACKUP DATABASE [AdventureWorks]

TO  DISK = N'E:\Backup\AWS_compress.bak'

WITH COMPRESSION

GO

I am a big fan of the best practices below I have collated a list of best practices when comes to dealing with SQL backups. 


 Best Practices when dealing with SQL Backups 

• Backups should not be on the same physical storage as your database files
• Automate your backups using scheduled jobs
• Use a test environment to verify the restore procedure of backups
• Test Recovery Options and Recovery Strategy
• Use Verification options (Use Checksum/Verify backups when finished)
• Backup System Databases daily (Master/Model/MSDB)
• Backup User Databases with Full/Differential and Log backups depending on business needs
• Highlight the importance of valid backups to your organisation
• Monitor your Backups to make sure no surprises