w3resource

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.

Go to:


PREV : Verifying the Integrity of a Backup File.
NEXT : Restoring a Specific Table from a Backup.



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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.