You are the database administrator for your company. You maintain a database named Hr1 on a SQL Server2008 instance named Sql1.
The database size is approximately 45 GB and uses the Full Recovery model. The Hr1 database consists of a primary filegroup and four secondary filegroups.
Each filegroup contains three datafiles that are located on different disks. The Hr1 database is an online transaction processing database.
You perform a full backup of the database everytwo days. The Transaction_details table in the database contains all the transaction details and is updatedfrequently.
You are required to frequently back up the data in the table to prevent loss of transaction details in theevent of a failure. Which backup strategy should you implement?
A.
You should increase the frequency of performing a full database backup.
B.
You should increase the frequency of performing a differential database backup.
C.
You should increase the frequency of performing backups of data files containing the Transaction_details table.
D.
You should increase the frequency of performing backups of the filegroups containing the Transaction_details table.
Explanation:
You should increase the frequency of performing backups of the filegroups containing the Transaction_details table. In this scenario, you must ensure that the data in the Transaction_details table is backed up more oftenbecause the data in this table changes frequently. You should only perform frequent backups of filegroupscontaining the table without backing up the entire database or taking a differential backup of the database. Toperform a filegroup backup, you should use the BACKUP DATABASE statement with the
FILEGROUP clause. You should not increase the frequency of performing a full database backup. In this scenario, only the modifieddata in the Transaction_details table should be backed up by taking a backup of the filegroup containing thetable. You should not increase the frequency of performing a differential database backup. In this scenario, only themodified data in the Transaction_details table should be backed up. Therefore, a differential backup that willbackup all the changes since the last full backup need not be performed. Additionally, the existing database islarge. Therefore, the backup size will increase every time a differential backup is performed, which is notrecommended. You should instead perform a new full backup of the database as a base for differential backups.To perform a differential database backup, you should use the BACKUP DATABASE
statement with the DIFFERENTIAL clause. You should not increase the frequency of performing backups of data files containing the Transaction_details table. In this scenario, you do not know which data files contain the Transaction_details table. All the filegroupsin the database contain three data files each, and this table can exist in more than one data file. Therefore, youshould back up the filegroup containing the Transaction_details table. This will back up all the files in thefilegroup.Objective:
Maintaining SQL Server InstancesSub-Objective:
Back up a SQL Server environment.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 > Creating Full and Differential Backups of a SQL Server Database > Full FileBackups