You are the SQL administrator for your company. A new SQL Server 2008 computer named SQL_test has beendeployed for the application developers employed by yourcompany. SQL_test has two databases named Customers and Products . The SQL_test databases must be backed up on a weekly basis. You need to ensure that any backups taken forthe databases on SQL_test minimize the space occupied by the backup files using the least amount ofadministrative effort. What should you do?
A.
Execute the BACKUP statement with the WITH COMPRESSION argument for each of the databasebackups.
B.
Use the sp_configure system stored procedure to set the backup compression default configurationoption to ON.
C.
On the Options page of the Backup Database dialog box for each of the database backups, select the Compress backup option of the Set backup compression list.
D.
Create a maintenance plan that backs up the databases with the Compress backup option of the Setbackup compression list.
Explanation:
You should use the sp_configure system stored procedure to set the backup compression default configuration option to ON . This will ensure that any backups taken for the databases on SQL_test minimize thespace occupied by the backup files using the least amount of administrative effort. By enabling default backupcompression, you ensure that all backups taken for the server are compressed. You should not execute the BACKUP statement with the WITH COMPRESSION argument for each of thedatabase backups. Although this will minimize the space occupied by the backup files created with the BACKUP statement, it will not do so for all backups. Future backups taken using a BACKUP
statement will also need toinclude the WITH COMPRESSION argument. You should not select the Compress backup option of the Set backup compression list on the Options
page ofthe Backup Database dialog box for each of the database backups. Although this will minimize the space occupied by the backup files created, it will not do so for all backups. Any future backups that are taken using the Backup Database dialog box will also need to include the Compress backup option. You should not create a maintenance plan that backs up the databases with the Compress backup option of the Set backup compression list. Although this will minimize the space occupied by the backup files created by themaintenance plan, it will not do so for all backups. Any future backups that are taken using a maintenance planwill also need to include the Compress backup option.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Back up databases.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > BackingUp and Restoring Database in SQL Server > Backup Overview > Backup Compression