By Bad Seed
Detailed Article for "SQL Server Backup Compression" is available at :
Follow us at more Updates at
Website : http://sqlserver-training.com/
Twitter : http://twitter.com/dbatag/
Facebook : http://facebook.com/dbatag/
Linked In : http://in.linkedin.com/in/dbatag/
Backup compression was introduced in SQL Server 2008 Enterprise, which reduces the size of backups and the time that it takes to create them.
Using backup compression is a simple process. Transact-SQL—WITH COMPRESSION in the BACKUP statement,
BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:\AWBackupCompressed.bak' WITH COMPRESSION
By default, no backups are compressed. You can change this default setting by using sp_configure to set the value of the backup_compression_default setting as shown in the following code example. Alternatively, you can change the default setting by selecting the option in the Server Properties dialog box.
EXEC sys.sp_configure N'backup compression default', N'1'
RECONFIGURE WITH OVERRIDE
Although compressed backups require less disk space to store them and less time to create, there is a tradeoff. The compression is a CPU-intensive operation that can impact other operations that occur on your servers. To avoid such contention, you can use Resource Governor to limit the CPU usage for a backup operation.
Compression and transparent data encryption are mutually exclusive.
Creating a backup with compression is supported only in SQL Server 2008 Enterprise Edition; all SQL Server 2008 editions can restore a compressed backup.
Visit the following link for more details and SQL Server Tutorials