How to Set Backup Compression in SQL Server

Microsoft has introduced backup compression feature from SQL Server 2008. This feature is very useful to save the time, reduces device I/O and  storage cost and it is also a good backup strategy option. You can configure backup compression at instance level so that if you perform any database backup, that database will be compressed by default or you can also set backup compression at individual database level. Here I am listing both the options.

Backup Compression at Database Level

You can set backup compression by T-SQL or by SQL Server Management Studio (SSMS).

Set Backup Compression with T-SQL

BACKUP DATABASE [BooksInfo] TO DISK = 'C:\Backup\BooksInfo.bak' 
WITH COMPRESSION
GO

This backup will compress your data upto 60%-80% depending upon type of data stored in database, like if you have mostly text data then you will get more compressed backup file or if you have images or videos then you will get less compressed backup file.

Set Backup Compression with SSMS

You can also set backup compression by SQL Server Management Studio from below path.

Right click on Database –> go to Tasks –> Back Up… –> backup database window will pop up –> go to Options page –> In compression section set backup compression to Compress backup –> click OK.

BackupCompressionOnce you have set the backup compression, now you can take the database backups in compressed file format.

Backup Compression at Instance Level

You also have option to set backup compression at instance level so that whenever you will backup any database it will be automatically in the form of compression for all your databases. You can set backup compression at instance level by T-SQL as follows.

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

It will enable Compress Backup option at instance level. If you want to set it by SSMS then you can follow below path and set the same.

Right click on database instance –> go to Properties –> it will pop-up Server Properties –> go to Database Settings page –> tick mark Compress backup option –> click OK. (refer below figure)

BackupCompressionInstanceLevel

Now, if you will take any database backup, it will be in compressed format by default.

But if you want to take database backup in normal format i.e. without compression and your instance level setting is enabled, then you can use WITH NO_COMPRESSION as shown below.

BACKUP DATABASE [BooksInfo] TO DISK = 'C:\Backup\BooksInfo.bak' 
WITH NO_COMPRESSION
GO

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

Leave a Reply

Your email address will not be published.