Database Backup With Compression in SQL Server 2012

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".

Database-backup-in-SQL-Server.jpg

This will open the following window:

Database-backup-window-in-SQL-Server.jpg

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.

Database-backup-location-in-SQL-Server.jpg

Now next click on "Options" and the following screen appears:

Database-backup-window-with-option-in-SQL-Server.jpg

Now select Compress Backup.

Database-backup-window-with-Compresion-option-in-SQL-Server.jpg

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.

Database-backup-window-in-SQL-Server.jpg

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

Database-backup-successfully-in-SQL-Server.jpg

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

Next Recommended Readings