You manage an instance of SQL Server 2008. The server contains a database named Corpdb . You configure theoptions for the Corpdb database as shown in the following graphic:
You are creating a transaction log backup of the Corpdb database. You open the Back Up Database – Corpdb dialog box to perform the transaction log backup. However, you discover that the Transaction Log option is notavailable in the Backup type drop-down list. You want to ensure that the Transaction Log option is available in the Backup type drop-down list.
What should you do? (Choose two. Each answer represents a complete solution.)
A.
Change the recovery model of Corpdb to Full.
B.
Ensure that you have the BACKUP LOG permission.
C.
Ensure that you are member of the db_backupoperator fixed database role.
D.
Change the recovery model of Corpdb to Bulk-logged.
Explanation:
You should change the recovery model of Corpdb to Full or Bulk-logged . Transaction logs allow you to restore adatabase to a particular point in time or to the point of failure if the database fails. In SQL Server 2008, transactionlogs are stored in the C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA folder by default. Transaction log backups can only be created for databases that use the full or bulk-loggedrecovery model. You can use the following Transact-SQL statement to change the recovery model for the Corpdb database: ALTER DATABASE Corpdb SET RECOVERY FULL; To perform a transaction log backup for the Corpdb database, you can use the following Transact-SQLstatement: BACKUP LOG Corpdb TO DISK = ‘D:LogBackupsCorpdbLog.bak’; You should not ensure that you have the BACKUP LOG
permission. This permission allows users to createtransaction log backups. However, having the BACKUP LOG permission is not useful unless the recovery modelof the database is set to either full or bulk-logged. You should not ensure that you are member of the db_backupoperator fixed database role. This fixed databaserole allows users to back up databases and transaction logs and create checkpoints. However, being a memberof the db_backupoperator fixed database role is not useful unless the recovery model of the database is set toeither full or bulk-logged.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 > Technical Reference > Transact-SQLReference > BACKUP (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies s and Enterprise Development > SQL Server >SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations >Administration > Administration: How-to Topics > Backing Up and Restoring How-to Topics (Transact-SQL) >How to: Create a Transaction Log Backup (Transact-SQL)