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-

Step 2: In the backup box, perform this-

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:

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