You administer a SQL Server 2008 database named Products . The Products database contains four filegroups,named Products1 , Products2 , Products3 , and Products4.
The database is used for online transactionprocessing (OLTP) with a high volume of transactions. Data is evenly distributed across the four filegroups.
The customer demographic information is contained in the Products1 filegroup, and its nonclustered indexes arelocated in the Products3 filegroup.
No other tables or indexes span multiple filegroups. You are designing a backup schedule for the Products database. Transaction log backups are performed every30 minutes.
All other backups must occur in the evening. While implementing a backup plan, you discover thatonly half of the database can be backed up each night.
You want to provide the maximum protection to theenvironment. Which backup schedule should you implement?
A.
Back up the Products1 filegroup on the first day. Back up the Products2 filegroup on the second day. Backup the Products3 filegroup on the third day. Back up the Products4 filegroup on the fourth day.
B.
Back up the Products1 and Products2 filegroups on Monday, Wednesday, and Friday. Back up the Products3 and Products4 filegroups on Tuesday, Thursday, and Saturday.
C.
Back up the Products1 and Products4 filegroups on Monday, Wednesday, and Friday. Back up the Products2 and Products3 filegroups on Tuesday, Thursday, and Saturday.
D.
Back up the Products1 and Products3 filegroups on Monday, Wednesday, and Friday. Back up the Products2 and Products4 filegroups on Tuesday, Thursday, and Saturday.
Explanation:
You should back up the Products1 and Products3 filegroups on Monday, Wednesday, and Friday and back upthe Products2 and Products4 filegroups on Tuesday, Thursday, and Saturday. —Nonclustered indexes must bebacked up with the tables on which they are based to ensure that data is in a consistent state. —
All options that back up Products1 with any filegroup other than Products3 are incorrect. The Products1 and Products3 filegroups must be backed up together because Products3 contains the nonclustered indexes for Products1 ‘s table. If you do not back them up at the same time, inconsistent data could be a result. You should not back up the
Products1 filegroup on the first day, the Products2 filegroup on the second day, the Products3 filegroup on the third day, and the Products4 filegroup on the fourth day. This solution would notallow you to restore Products1 and Products3 so that they are consistent with each other. For SQL Server to re-create an index, all database files that contain the base table and all database files that areaffected by the index creation must be in the same condition in which they were in when the index was firstcreated. If the index and the base table are contained in the same filegroup, you should back up the entire filegroup as asingle unit. If the index and the base table are contained in separate filegroups, you should back up all thefilegroups as a single unit. This will allow you to restore the indexes to a state consistent with the base table.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 Databases in SQL Server > Backup Overview (SQL Server)