How To Compress A Native Backup In SQL Server

 The compressions of backups in SQL Server is purely to improve the performance of the server itself. The idea behind a backup compression is to reduce the disc usage in your drive. I was working with one of my friends who is a recent explorer in SQL Server and wanted to gather information on backup compression. It would be a good time to fabricate the native backup to all my SQL friends.

The native backup compression feature has been in use right since the SQL Server start-point. However, the compression was available only in the Enterprise Edition of SQL Server 2008 or earlier. If you are using any of the SQL Server 2008 or older version, then the backup compression is going to work only on Enterprise Edition. Further, the backup compression feature is made available starting from SQL Server 2008 R2.

The usage of native backups in SQL Server is still optional and can be performed using a third-party too. While comparing the performance of the native backup with the third-party tool, the third-party tool performs much better.

However, let’s check on how to compress a native backup in SQL Server using GUI.

Step 1: Open the SSMS and Right-Click on your server.

Step 2: Navigate to Database Setting and check the Compress backup option

Compress Native Backup SQL Server 1

You are all set with enabling the native backups with compression. 

Once we are done with enabling the native backup compression option using GUI, we have another method to perform backup compression through the script. 

USE [master]
GO
BACKUP DATABASE [DBCompression_Testing]
TO DISK = N'D:\Backup\DBCompression_Test\NativeCompressionBackup.bak'
WITH COMPRESSION, STATS = 10
GO


Compress Native Backup In SQL Server 2

Using the script is simpler and more efficient than using the GUI. So personally, I prefer to run the backup through scripts than using GUI. However, both options are well enough to use.

For your reference:

How to create a full backup
How to create a differential backup
How to create a log backup

0 comments: