SQL Server – Database Backup Compression – Faster Disaster Recovery

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:

1_SQL_Server_Database_Backup_Compression_Faster_Disaster_Recovery

   

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:

2_SQL_Server_Database_Backup_Compression_Faster_Disaster_Recovery

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 TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.