You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales and product data. The Simple Recovery Model is implemented for the CK_Sales database. A Full backup of the CK_Sales database is performed every Saturday at 8:00 P.M.
The sales data is updated frequently throughout the day. Over the last six months the CK_Sales database has grown considerably. At present the Full backup requires 6 hours to complete. You are concerned that data loss may occur should Certkiller -DB01 suffer a hard disk failure. You decide to implement additional backups of the CK_Sales database on a daily basis. However, the backup may only run between midnight and 4:00 A.M. on week nights.
What should you do? (Each correct answer presents part of the solution. Choose two.)
A.
Schedule a Full backup of the CK_Sales database at 12:00 P.M. every week night.
B.
Schedule a differential backup of the CK_Sales database at 12:00 P.M. every week night.
C.
Create two filegroups. Backup a different filegroup every other week night.
D.
Backup the transaction log every week night.
Explanation:
Creating two filegroups and backing up a different filegroup every other week night will allow you to backup the database within the given time frame during the week. You should also backup the transaction logs every night to ensure full recoverability as only one filegroup is backed up each night.
Incorrect Answers:
A: A Full backup requires six hours to complete but the backup can only run from 12:00 P.M. until 4:00 P.M. during the week. Thus there is insufficient time to complete a Full backup during the week.
B: A differential backup will backup all data that has changed since the last Full backup. Towards the end of the week the differential backup may take quite some depending on the number of changes made since the last Full backup and may require more than 4 hours to complete.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: database backups [SQL Server]