Use Backup Compression

Thomas Rushton advocates for enabling backup compression:

SQL Server backup compression – does what it says on the tin. Instead of SQL Server taking a backup by reading pages / extents of data from the database file and writing them out to the backup file, it compresses the data before it writes. It’s not the best compression you’ll get, as it won’t read the entire file before compressing it; however, it’s good enough that on OLTP databases that contain normal varchar / numeric data you could see savings of over 75% – indeed, I have some databases that save 90% of disk space. And that’s made my storage guys happy. Well, less unhappy.

One side note:  if you’re also encrypting backups (available in the product since SQL Server 2014), encryption happens after compression, so you can take advantage of both.

Related Posts

Generating SQL With Biml

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages: This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas […]

Read More

Automation With Powershell Desired State Configuration

Jess Pomfret takes us on a journey to a desired state: PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC).  It uses declarative syntax instead of the usual imperative syntax of PowerShell.  This means that you describe your desired state rather than the specific steps needed to get there.  There […]

Read More

1 Comment

  • Thomas Rushton on 2016-12-14

    It used to be that you could either compress or create a password-protected backup; but not both. Nice to see that they’ve sorted that out. 🙂

Comments are closed

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031