Wednesday, October 17, 2012

The alternative for Zipping your SQL backups. Save download and restore time.

Sometimes you need a SQL database backup to analyze. To minimize the download time people compress the SQL database backup with tools like WINZIP, WINRAR, 7ZIP, ARJ etc...... This is nice but there is a more efficient way. First of all, I will explain the download and restore process of a WINZip backup.
You need to uncompress the database backup file before you can start the restore process it self. Example: you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:
  • 5 Gb for the zip file
  • 50 Gb for the backup file
  • 50 Gb for the restored database. (assume their is no empty space in the database)
In total 105 Gb of diskspace is needed.

The more efficient way. Use the Backup compression feature. Backup time is much faster because less disk IO is needed to write the backup file. A compressed backup file can be restored without a seperate uncompress proces. This will save a lot of disk space. In the previous example 50 Gb because you do not need to uncompress the WINZIP file. You will receive a 5Gb database which you can directly restore to the 50 GB database file(s).

In the option tab of the Backup database window you will find at the buttom the Set backup compression option. By default it is set to Use the default server setting. You can change this to Compress backup.

On server level you can change the default compression setting to compressed. Retrieve the server properties of the SQL server. Select the Database Settings property. Check the Compress backup checkbox.

As of now every SQL backup will be compressed.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

More information can be found here.

No comments: