You are a database administrator managing the SQL Server databases of your company. You have created anew database named Db01 to store the data for a new division in your company. While creating the database,you did not specify the recovery model for the database.
What will be the default recovery model for the Db01 database?
A.
the Simple Recovery model
B.
the Bulk-Logged Recovery model
C.
the recovery model of the model database
D.
the recovery model of the master database
Explanation:
The recovery model for the Db01 database will be the same as the recovery model for the model database.When you create a new database in SQL Server 2008, the recovery model, by default, is the same as that of the model database. The recovery model of a database specifies the type of backup and restore operations that maybe performed on the database. Depending on the type of backup and restore operation you want to perform on adatabase, you can specify one of the following three recovery models:
? Simple Recovery model: This model requires minimum overhead to maintain the database. The SimpleRecovery model requires less disk space than the other two recovery models. This recovery model doesnot require maintenance of transaction log backups. After the transactions in a particular log are complete,the inactive log is truncated. In the Simple Recovery model, recovering to the point of failure is notpossible. You can recover only up to the most recent data backup.
? Full Recovery model: This model involves a backup of the transaction logs and provides recovery of thedatabase up to a specific point in time if all the transaction logs up to that point in time are intact. Thisrecovery model should be used if the database must ensure minimum data loss in the event of a failure.
? Bulk-Logged Recovery model: This model supports large bulk operations. Transaction log backups similarto the Full Recovery model are required. This recovery model does not support point-in-time recovery. Thedatabase can be recovered only up to the last complete backup.
All the other options are incorrect because they do not represent the default recovery model of the Db01 database. The default recovery setting for the Model database is the Full Recovery model, but this setting can bechanged.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Manage and configure databases.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Transaction Log Management > Recovery Models and Transaction Log Management > Choosing the RecoveryModel for a Database