You administer a Microsoft SQL Server 2012 instance that contains a financial database
hosted on a storage area network (SAN).
The financial database has the following characteristics:
• 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.
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 backup size is as small as possible.
Which backup should you perform every two hours?
A.
BULK_LOGGED
B.
NO_CHECKSUM
C.
FULL
D.
RESTART
E.
CHECKSUM
F.
STANDBY
G.
DBO.ONLY
H.
NORECOVERY
I.
SIMPLE
J.
SKIP
K.
Transaction log
L.
COPY_ONLY
M.
Differential
N.
CONTINUE_AFTER_ERROR
Explanation:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/ms191429.aspx
http://msdn.microsoft.com/en-us/library/ms179478.aspx
.. up to now answer lacking !
0
0
Correct answer is K
1
0
I Agree with you trans Log takes the minimum space
0
0
K. Transaction Log
0
0
T-Log backup is the correct option.
Differential backup would only increase in size every 2 hours, also the old Diff backups becomes useless once you take a new Diff backup.
0
0
thats not right JackD. When you take a differential Backup it is added to the previous backup and it contains only changes since the last diff backup:
https://technet.microsoft.com/en-us/library/ms345448%28v=sql.105%29.aspx
you should look at the size of the datastorage and at the need to get the “backup size is as small as possible”. You will take 4 backups a day 7 days a week till the next FULLbackup and since you can drop all the made backups. it is 28 backups a week. the maximum backup size for the transactional log is 5GB cause there is not more space on drive E! I am not sure if one tranactional backup is smaller than 180MBytes. So this question is kind of messed up. cause you need to know how many transactions are happening and this is depending on the situation and business. In best case transactional backup is the “right” answer. In worst case the differential backup is the “right” answer!
0
0
You are wrong Josef. Differential backups tend to grow, since they contain all changes since the last FULL backup. Restoring a db only means restoring the full backup and the last differential backup.
So JackD is right, Josef is wrong.
0
0
A data file of 2 terabytes is located on a dedicated LUN (drive D).
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… Each data load adds 3 GB of data to the database.
I might be reading into it more than I should, but is sounds like there is more modification of data going on (2 Terabyte x 5% = 100 GB) than adding (3 GB).
Transactional would require capturing all changes, even if they were again changed during the backup, but differential would only need to store the page changed on the latest backup.
By close of business Friday, you would have 20 T-logs taking up space and would each need to be restored if the server went down before Sunday.
Differential by contrast would only need the most recent backup, but realistically you would not delete one differential backup until the other has completed, so 2 admittedly large differential backups Friday evening sounds better than 20 good sized t-logs.
Am I missing something?
0
0