Hi Friends,
Today, I am focusing on very interested feature provided by Microsoft SQL Server for DBA people. This feature is “Database Backup Compression”. This feature first introduced in SQL Server 2008, only for Enterprise editions. This feature is also available in later versions. If we want to use this feature then we need to turn on this feature. There are some major benefits of using database backup compression:
1- Less backup time: after compressing the data SQL Server takes backup, which really decrease the backup time for our database. But keep in mind that performs compression before writing the data to backup file will increase the CPU uses.
2- Less Restore time: the most beautiful feature of database backup compression is less time to restore the database. We all know that there are four steps perform by SQL Server to restore the database – file creation and initialization, data copy, redo ,undo. Here backup restore will take less amount of time in second step “data copy” due to database backup compression. This is just because of very less IO. That means this feature also help us to obtain a fast recovery time in case of disaster recovery.
3- Less Disk Space: Due to compression database backup file will take less amount of space on disk. Compression percentage is totally dependent on type of data, consistency of data, data is encrypted or not, database is compressed or not. If you are using data compression then database backup compression percentage will be very low. If you are not using data compression then it will provide you 80% to 85% compression percentage.
You can setup this feature at instance level by using SSMS and TSQL.
Using SSMS:
Connect your SQL Server Instance in SSMS. Then in object explorer right click on instance name and go to properties. This will open up server properties window. Here click on database settings. Here on right side panel just click on compress backup option to enable database backup compression setting at instance level as shown below:
Using TSQL:
Use Master; GO Exec sp_configure 'backup compression default','0'; Reconfigure with override;
You can also set this setting of database backup compression at database backup level.
Using SSMS:
When you take the backup through SSMS then go to Options under back up database window. Here you can change the setting as shown below:
Using TSQL:
I am having a database GEEKS on my test instance, here I am going to take full backup of GEEKS by using below TSQL
BACKUP DATABASE [GEEKS] TO DISK = N'F:\DATABASE\GEEKS_12-OCT-2012.bak' WITH NOFORMAT, NOINIT, NAME = N'GEEKS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
Regards
Prince Rastogi
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
