Database Backup With Compression in SQL Server

In this article, we will see how to do Database Backup Compression in SQL Server 2012. To do that, you can use SQL Server Management Studio and a Transact-SQL statement. So let's have a look at a practical example of how to do database backup with compression. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

To visually create a Database Backup

Now Press F8 to open the Object Browser in SQL Server Management Studio and expand it.

Select the database, then right-click on the selected database, click on "Task" and then click on "Back Up".

This will open the following window.

To specify the destination of the database, select the destination then click on the Add Button. We then see a new dialog box, click on the browse button to choose the location.

Now, next click on "Options" and the following screen appears.

Now select Compress Backup.

The above image contains three options to select.

1. The first option, "Use the default server settings" tells the backup to use the server's default backup compression setting. By default, it is set to have backup compression off.
2. The Second option, "Compress Backup" turns backup compression on.
3. The "Do not compress backup" option turns it off.

Now click again on the "General" option.

Now, you will see that the database backup has been completed successfully.

Now click on the OK Button. The database backup file has been created for the given location.