SQL Server Compressed Backups

Hello Folks,

You would have heard about SQL Server Compressed Backups.

The Compressed Backups is being introduced in SQL Server 2008 Enterprise and in later versions, but since the commencement of SQL Server 2008, every edition can restore a compressed backup. Well I am going to give some heads up on this:

  • With the help of compressing backups, we can save time and disk space.
  • The backup time backup time is usually reduced since the less data is being written to the disk.
  • It’s a relatively simple process to use compressed backups.
  • But it also has some restrictions:
    • Compressed and Uncompressed backups cannot co-exist in a media set.
    • All the previous versions of SQL Server cannot be able to read compressed backups.
  • Some are the impacts on performance while using compressed backups:
    • Since a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires fewer devices I/O and therefore usually increases backup speed significantly.
    • Compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations.
  • Compressed backups can be performed in two ways:
    • With the help of SQL Server Management Studio.
    • With the help of T-SQL.

With the help of SQL Server Management Studio:

Step 1: In the management studio window, perform this-1_SQL_Server_Compressed_Backups

Step 2: In the backup box, perform this-

2_SQL_Server_Compressed_Backups

With the help of T-SQL:

Just write the code below in a T-SQL window:

BACKUP DATABASE [TEST] TO DISK = N'C:\Backup\TEST.bak' WITH
NOFORMAT, NOINIT, NAME = N'TEST-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

You can see the result below:

3_SQL_Server_Compressed_Backups

Well this was all about Compressed Backups.

And also comments on this!!

 

Regards

Piyush Bajaj

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “SQL Server Compressed Backups”

  1. CPU time when compressing may vary. I did some testing for which there was almost no CPU work increase. The database was filled at 80% of blob storage, but previous testing showed that blobs also goes through compression (albeit less compressed). May be CPU usage is not a constant among all backups. It may vary depending on database content.

Leave a Reply

Your email address will not be published. Required fields are marked *