You work as the database administrator at Domain.com. Domain.com has five departments named Accounting, Sales, Marketing, Research and Manufacturing. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run on Windows Server2003. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores data for the Sales department. The CK_Sales database is accessed frequently throughout the day.
Mia Hamm, the manager of the Sales department is concerned that data may be lost should Certkiller -DB01 suffer hardware failure. Mia Hamm wants you to implement a recovery model and backup strategy for the CK_Sales database. You want to implement the highest level of recovery and you want to be able to restore the data for the CK_Sales database in the least possible time. What should you do? (Each correct answer provides part of the solution. Choose TWO.)
A.
Implement a Full Recovery model.
B.
Implement a Simple Recovery model.
C.
Perform a full backup and log backup every evening.
D.
Perform a full backup on Saturdays and differential backups Monday through Friday.
E.
Perform a full backup on Saturdays and inferential backups Monday through Friday.
Explanation:
The Full Recovery model provides the highest level of recovery as it does not clear data in the log files until they are backed up. You restore time is reduced by performing differential backups. With differential backups, all data since the last full backup is saved. This means that you only need the last full backup and the last differential backup when you need to restore data.
Incorrect Answers:
B: The Simple Recovery model clears the data in the log file as soon as the transaction in written to the database. This means that transactions that have been written to the database after the last backup could be lost in the event of hardware failure.
C: You should backup the log files throughout the day rather than at the end of the day as all transactions that occurred during the day could be lost should a hardware failure occur during the day.
D: Performing a full backup on Saturdays and inferential backups Monday through Friday would increase your restore time as each inferential backup performed after the last full backup must be restored in sequence. Thus, should a hardware failure occur on Friday, you would need to restore the last full backup from Saturday, and then the inferential backup from Monday, followed by the inferential backup from Tuesday until the last inferential backup.