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 implement log shipping of the financial database to another SQL Server 2012 instance.
You decide to failover to this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?
A.
Differential
B.
Transaction Log
C.
FULL
D.
SIMPLE
E.
SKIP
F.
RESTART
G.
STANDBY
H.
CHECKSUM
I.
DBO_ONLY
J.
COPY_ONLY
K.
NORECOVERY
L.
NO_CHECKSUM
M.
CONTINUE_AFTER_ERROR
N.
BULK_LOGGED
Explanation:
http://technet.microsoft.com/en-us/library/ms187103.aspx
http://msdn.microsoft.com/en-us/library/ms191233.aspx
http://msdn.microsoft.com/en-us/library/ms178117.aspx
K
B?
K for me
Norecovery.
Can one perform a ‘NORECOVERY’ without a Transaction Log backup?!
Question states: You need to ensure that all transactions will be replicated to the secondary database… meaning the most current t-log needs to be backed up.
I believe the valid backup to perform is a Transaction Log backup -WITH NORECOVERY. So, option B is the right answer; if two answer choices are allowed, then it’d be B & K.
Transaction log is not a backup option , it is a recovery model. since we are asked about a backup option it is K to be right!
@JosefTheGreat: Nobody answered my poser; neither did you!! Transaction Log backup is a backup type, just like Full, Differential, and Copy-Only. The three (3) recovery models are Full, Bulk-logged, and Simple; there’s no T-log recovery model.
That said, can you select a ‘NORECOVERY’ backup option (under ANY scenario) without specifying a Transaction Log Backup?! I don’t think so…!!!
I personally feel the question is not rightly scripted. In order to bring the secondary SQL database up to date transaction-wise, the shipped logs need to be restored WITH NORECOVERY in order to leave the database in the recovering state…
But the question states: “You need to ensure that all transactions will be replicated to the secondary database. Which backup option should you use?”
…The question should have been: “Which backup option must be included with the T-log backup?”
Guys,
I had to go look for the following question in the series; it is how the above question should have normally been worded to clearly indicate what was being asked, and what needed to be done:
Q: “You administer a Microsoft SQL Server 2012 database. The database is currently configured to log-ship to a secondary server. You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup. You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?”
…To which the obvious answer would be: NORECOVERY.
Well, it took you a whole lot of text to accept the obvious….again