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 also you can use 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 expend it.

Select database then right-click on the selected database then click on "Task" 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 completed successfully.