How to Create a Compressed Backup in SQL

Creating a Backup with Compression

Write a SQL query to create a compressed backup of a database.

Solution:

-- Create a compressed backup of the MyDatabase database.
BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backups\MyDatabase_Compressed.bak'
WITH COMPRESSION, INIT;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to create a compressed backup to reduce storage requirements.
• Key Components :
• WITH COMPRESSION: Compresses the backup file to save disk space.
• INIT: Ensures the backup overwrites any existing file at the specified location.
• Why Use Backup Compression?:
• Compressed backups reduce storage costs and improve transfer speeds.
• They are ideal for environments with limited storage capacity.
• Real-World Application :
• In cloud environments, compressed backups reduce storage fees.

Notes:

• Compression increases CPU usage during backup creation.
• Test compression settings to balance performance and storage savings.
• Ensure sufficient CPU resources for compression.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a compressed full backup of a database named "SalesDB" and store it in a cloud storage location.
• Write a SQL query to create a compressed differential backup of a database named "HRDatabase" and test its restoration process.
• Write a SQL query to create a compressed transaction log backup of a database named "InventoryDB" and monitor CPU usage during the process.
• Write a SQL query to create a compressed backup of a database named "FinanceDB" and compare its size with an uncompressed backup.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Verifying the Integrity of a Backup File.
Next SQL Exercise: Restoring a Specific Table from a Backup.