Hello!

I've got a test question that I don't know how to interpret:

"The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You need to ensure that the minimum amount of data is lost.

Which recovery model should the database use?"

I don't understand based on what should I chose an answer?  Full backup can be performed in the Full, Bulk-logged and Simple recovery models. Given that types of additional backups (11:00-17:00) are not specified how can I answer this question?

Thank you in advance,

Michael

Hello!

I've got a test question that I don't know how to interpret:

"The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

Based on the above lines I would choose Bulk-logged recovery model as my option but the requirement of minimum amount of time for load  and minimum amount of data to be lost makes picking an answer a difficult job. 

What are the answer options you have for this question. 

>Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

These are typically transaction log backups to ensure point-in-time recovery. Differential backups MON-FRI at night.

Here is what BOL recommends: "We recommend that you minimize your use of the bulk-logged recovery model. The best practice is to switch to the bulk-logged recovery model right before a set of bulk operations, perform the operations, and then immediately switch back to the full recovery model."

http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

The recovery sequence the following for WED 11:39 failure:

1. Restore SUN full backup (no recovery)

2. RESTORE TUE-to-WED night differential backup (no recovery)

3. RESTORE 11AM transaction log backup (rec

Thank you all for your replies!

Answer options are as the following:

A. FULL
B. DBO_ONLY
C. CONTINUE_AFTER_ERROR
D. CHECKSUM
E. NO_CHECKSUM
F. SIMPLE
G. Transaction log
H. SKIP
I. RESTART
J. COPY_ONLY
K. NORECOVERY
L. BULK_LOGGED
M. Differential
N. STANDBY

"Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours." - theoretically these backup operations can be the differential or transaction log ones.

"You need to ensure that the minimum amount of data is lost." - I suppose it means 11:00 - 17:00 backups must be transaction log backups because they allow point in time recovery. In any case the answer should be A: Full

Are there any other ideas?

Best regards,

Michael

"Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours." - theoretically these backup operations can be the differential or transaction log ones.

Only theoretically! Practically, differential backup is slow, transaction log backup is fast. You do differential backup during the night. Transaction log backups during the day.

My answer: it should be FULL recovery model with switching to BULK_LOGGED for the duration of the uploads.

BOL: "Considerations for Switching from the Full or Bulk-Logged Recovery Model

A database can be switched to another recovery model at any time. If a switch occurs during a bulk operation, the logging of the bulk operation changes accordingly.

Switching between full and bulk-logged recovery models is useful before and after large bulk operations. The full recovery model, which fully logs all transactions, is intended for normal use. The bulk-logged recovery model is intended to be used temporarily during a large bulk operationassuming that it is among the bulk operations that are affected by the bulk-logged recovery model (for more information, see Operations That Can Be Minimally Logged). If you switch between the full and bulk-logged recovery models during a bulk operation, logging of the bulk operation changes accordingly."

http://msdn.microsoft.com/en-us/library/ms190203(v=sql.105).as

Ok, I understand.

"My answer: it should be FULL recovery model with switching to BULK_LOGGED for the duration of the uploads." - but there's no such option: either A) Full  or L) BULK_LOGGED...?

• Edited by MF47 Wednesday, November 07, 2012 8:57 AM

Hi Michael,

I would choose Full recovery model.

The Bulk-Logged model provides higher performance and lower log space consumption for certain large-scale operations (for example, create BCP or bulk insert). It does this at the expense of some flexibility of point-in-time recovery. If you choose Bulk-logged model, when the database failed during the bulk insert process, you could only restore to the last backup. Because the log is not detail enough to make the database restore to the point of failure, some data may be lost. It can only recover to the end of any backup. Point-in-time recovery is not supported.

If you choose full recovery model, it can recover to an arbitrary point in time.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Thanks.

Thank you all very much!

• Edited by MF47 Monday, November 12, 2012 2:08 PM

so answer is full backup right.

I have some input: This question begin with DB size and disk capacity:

A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through
Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified
each day.

By considering only 5GB space left on log drive, and 5% or 2TB modifed every day, I would say leave me no choice (if not alter log drive size) to use simple recovery option.

even Bulk_copy still record such 10G around data to log. well, backup 3 times daily might make you think T-log backup performed, but dont be so sure cuz we dont know the pattern of business data change, could be in the morning one hour exceed 5G.