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.
Once 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)
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
