From Long time SQL Server DBAs and Professionals are awaiting for a automatic compressed backup utility for SQL server, But It became true only with SQL Server 2008. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what’s even better is that it’s easy to use.
All you have to do is append the WITH COMPRESSION option to your backup statements. This bit of code will back up the AdventureWorks2008 database using compression:
BACKUP DATABASE AdventureWorks
TO AWBackup
WITH COMPRESSION
GO
Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog.
we can also set the Compressed Backup option in Database Settings for SQL Server properties Dialog. At the time of Backup, It automatically takes the compression option into consideration. so when we take backup SQL Server automatically delivers the compressed backup.
SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.
SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It’s great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time.
Related Posts
- BACKUP Compression in SQL Server 2008
- Backup SQL Server Database using SSMS
- An Introduction to Microsoft SQL Server 2008 – Part 1
- How to Verify the SQL Server Backup
- Backup and Restore Transaction Log in SQL Server
Tags: Backup Compression, Compress Backup, SQL Server 2008, SQL Server Backup, SSMS Backup, TSQL Backup
