March 30, 2012 at 12:15 pm
I'm taking a Practice Test that comes with Microsoft's 70-432 Training Kit.
One of the questions I missed contains a "correct" answer that does not look correct to me. The question is:
"You are the database administrator on call for City Power & Light. You receive a call at 3 A.M. that the GridControl database is offline due to a disk failure where the data files were stored. The backup drive attached to the machine is accessible and contains the most recent full backup along with all the differential and transaction log backups taken since the last full backup. You need to get the database back online before 8 A.M., when the utility has power consumption peak. You need to recover the database with minimal loss of data.
"What is the first step that you need to perform to recover the GridControl database?"
I selected: Restore a full backup.
The "correct" answer: Back up the tail of the log.
If the disk where the data files are stored is lost, is it really possible to backup the tail of the log? If the MDF file is gone but you have the LDF file on a different disk, is it still possible to do a tail of the log backup?
March 30, 2012 at 12:25 pm
From Books Online...
Tail log backups capture the tail of the log [font="Arial Black"]even if the database is offline[/font], damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.
The answer to the question is correct. Take a tail-log backup.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 12:27 pm
Yes, it is. BACKUP LOG <database_name> WITH NORECOVERY.
From Books Online:
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.
To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.
March 30, 2012 at 12:29 pm
Here's another:
"Wide World Importers has offices in Moscow, Paris, and Chicago. The Shipments table contains all the data concerning the company's import/export business. All data is entered in the local language. To provide redundancy, you have implemented merge replication without filters so that each location has all the data. The application is configured to filter out any data that does not apply to the users' current location. For example, users in Chicago do not see any of the data entered for either Moscow or Paris.
You are reviewing the contents of the database and see that a large amount of data in every table that is unintelligible.
What is the most likely cause of the problem?"
My answer: The tables are defined with non-Unicode data types.
The software's answer & explanation: "The collation sequence between the databases does not match.
"The replication engine replays transactions that occur against the databases. Because data at each location is stored in the local language, you have three character sets to deal with in the data. If you attempt to insert a character that does not exist for the collation sequence defined, Microsoft SQL Server cannot translate the data and you end up with unintelligible information. The best way to avoid this problem is to use Unicode data types, which can handle all characters used within a language set. In this particular case, the data types in the tables would be non-Unicode and the collation sequences between the databases do not match."
Based on this explanation, it looks like both answers would be correct, but the "best" way to avoid the problem would be to use Unicode datatypes, so my answer would be more correct. Am I reading this right?
March 30, 2012 at 12:31 pm
So a tail of the log backup does not require the data file to exist. Thanks!
Dan
March 30, 2012 at 1:11 pm
dan-572483 (3/30/2012)
So a tail of the log backup does not require the data file to exist. Thanks!
Provided the DB is in full recovery or is in bulk-logged recovery and there are no minimally logged operation in the active log.
Books Online: Tail-Log Backups
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2012 at 1:15 pm
Lynn Pettis (3/30/2012)
Yes, it is. BACKUP LOG <database_name> WITH NORECOVERY.
When the data file is damaged or missing, the backup log must have the NO_Truncate option, or it will fail with an error. Norecovery is primarily for when you're about to restore over an online and operational database and want to be sure you have all the committed transactions before doing so.
From Books Online:
If the database is online, before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
If the database is offline and does not start.
Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.
BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2012 at 1:17 pm
GilaMonster (3/30/2012)
Lynn Pettis (3/30/2012)
Yes, it is. BACKUP LOG <database_name> WITH NORECOVERY.When the data file is damaged or missing, the backup log must have the NO_Truncate option, or it will fail with an error. Norecovery is primarily for when you're about to restore over an online and operational database and want to be sure you have all the committed transactions before doing so.
From Books Online:
If the database is online, before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
If the database is offline and does not start.
Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.
BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }
Thanks Gail, I misread what I saw. Good to see the clarification. I had the right answer, just the wrong command, which if left unchecked could be an issue.
April 2, 2012 at 12:27 pm
For anyone who reading this topic who found something in the Training Kit that doesn't look right, the Training Kit book & Practice Tests do contain errors. They are detailed in KB968609.
If you are doign this course print it out and save yourself a lot of time scratching your head.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply