Hi friends, this article discusses the “Backup Compression” feature in SQL Server.
About Backup Compression in SQL Server
How many times have we backed up a database and then we have tried to minimize the size of the backup set by compressing it with an archiving utility?
I am quite sure that such cases were too many 🙂
SQL Server 2008 was the first release of SQL Server that introduced Backup Compression. This enhancement allows us when backing up a database to activate backup compression which is performed on the fly.
To do this you have to right-click on a database, select Tasks, then select Backup. In the Back Up Database dialog which appears after this, if you go to the Options tab, you will notice that there is a feature on the bottom of the dialog which says Compression. Then you can set the backup compression for the current database. To this end you are presented with the following options:
1. Use the server default setting
2. Compress backup
3. Do not compress backup
If you select the Compress backup or Use the server default setting and your instance of SQL Server is setup to compress the backups by default, when you initiate the backup process the backup set will be automatically compressed thus achieving high compression rations and reducing the disk storage needed for the backup set.
Note: If you have performed a backup operation on the database before and you have not used the backup compression option but now you are trying to use it you will get an error. Also, if you have used the backup compression before and now you are not using it when backing up the database, again you will get an error. The reason for this is that by the time you have a non compressed backup set, you cannot add a compressed backup to this and vice versa.
One solution to the above restriction is to use the option Backup to a new media set and erase all existing backup sets. This will allow you to proceed with the backup but it will erase the previous backup sets for the specific database. To this end, it is always better to decide the backup method to be followed for each database before you start taking backups of it.
Learn more about SQL Server Development – Enroll to our Course!
Enroll to our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link) and sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
- SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
- SQL Server 2022 Generally Available!
- An existing History Table cannot be specified with LEDGER=ON – How to Resolve
- What is Data Management and why it is Important?
- What is Data Security and which are its Main Characteristics?
- Data Security vs. Data Privacy
- What are NoSQL Databases?
- Differences Between Batch and Streaming Data
- What is Data Compliance within the Data Management Scope?
- How to Connect to SQL Server Databases from a Python Program
- How to Resolve: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)
- Useful Python Programming Tips
- Main Data Structures in Python
- Working with Python on Windows and SQL Server Databases (Course Preview)
- How to Write to a Text File from a C++ Program
- How to Establish a Simple Connection from a C# Program to SQL Server
- The timeout period elapsed prior to obtaining a connection from the pool
- Closing a C# Application (including hidden forms)
- Changing the startup form in a C# project
- Using the C# SqlParameter Object for Writing More Secure Code
- Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHub TV)
Check our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.