ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam
Go to Exam

Exam 70-764 topic 1 question 75 discussion

Actual exam question from Microsoft's 70-764
Question #: 75
Topic #: 1
[All 70-764 Questions]

You administer a Microsoft SQL Server 2016 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 2016 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
Show Suggested Answer Hide Answer
Suggested Answer: K 🗳️
Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
by DS at Aug. 7, 2019, 8:48 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Zikato
4 years, 5 months ago
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15 NORECOVERY Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.
upvoted 1 times
...
fkarata1
4 years, 7 months ago
Answer: C - FULL 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.
upvoted 1 times
...
mrn0107
5 years, 4 months ago
I beleve that answer should be backup option "tail log backup" and than the database will go in norecovery mode. Tail log backup will be shipped to secondary database.
upvoted 2 times
KC
4 years, 10 months ago
Totally agree. The key is not losing any transactions... that’s the hint that you need to backup the most recent tail log before you begin recovery.
upvoted 1 times
...
...
TheSwedishGuy
5 years, 5 months ago
Not a huge fan of this question. It asks about backup options but actually wants an answer on which restore option to use.. Strange!
upvoted 1 times
Hoglet
4 years, 5 months ago
Incorrect NORECOVERY is an option when taking a transaction log backup. This option has a number of features. 1. It doesn’t complain is the db is not present 2. It put the db into RECOVERING so nothing else can write to it When failing over transaction log shipping, you want to ship the very last activity and stop further activity, so this option is very useful for that The “classic” case of this option is when you data file/hard disk of the db has failed. You would backup the transaction log with this option, so that SQL just does as ordered and doesn’t complain the the db is unavailable. You can then recovery the db to the point of failure, rather than the last “standard” log backup
upvoted 1 times
...
...
Guest
5 years, 7 months ago
Guess option K is also correct for backup, just the explanation should be different Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation. https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
upvoted 1 times
...
Guest
5 years, 7 months ago
Agreed with restore with NORECOVERY However the question asks which backup option to use
upvoted 3 times
...
peach
5 years, 9 months ago
There is a K. No Recovery, J-N answer is in horizontal format
upvoted 2 times
...
DS
5 years, 10 months ago
there is nO K
upvoted 2 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel