SQL Server 2008 Interview Q&A

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.
EXEC sp_configure ‘backup compression default’, ‘1’

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s