SQL Server 2008 Interview Questions - I

Which types of compression supported by SQL Server 2008?
SQL Server 2008 supports two types of compression namely as below

• Database Backup Compression


• Data Compression

Explain Database Backup Compression
SQL Server 2008 introduces a new feature called Database Backup Compression (DBC). This feature allows DBA’s to compress SQL Server 2008 database backups natively rather than taking uncompressed native SQL Server database backups or using a third-party tool. By default, database backup compression feature is turned off in the SQL Server 2008.
Using the Database Backup Compression feature, we can perform Full, Differential and Transactional log compressed backups.

Which SQL Server editions supports database backup compression feature?
Currently this feature is only available in the Enterprise Edition of SQL Server 2008. However, all editions of SQL Server 2008 allow the restoration of compressed database backup.

What are the pre-requisites for using database backup compression feature?

1. SQL Server 2008 Enterprise edition


2. Enable database compression at server level


3. User WITH COMPRESSION clause while taking FULL, DIFFRENTIAL and LOG backup

How to enable database backup compression feature at server level?
You can use below TSQL code to enable the DBC at server level.
USE MASTER
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO