December 11, 2008 at 12:04 am
Comments posted to this topic are about the item Backup and Recovery
December 11, 2008 at 1:03 am
My answer to this question is and was Yes.
Even though it wasn't stated that a full backup was made before the transaction log backup, you would normally assume it, and the question even implies it.
It is not possible to make a transaction log backup without having made a full backup first. So if the transaction log backup worked (nowhere in the question it is written it didn't), it implies that a full backup was made.
Guess that everybody who answered yes, should get their points.
December 11, 2008 at 2:01 am
The explanation is misleading. Especially this part:
you cannot use the log backup to recover the database
since it implies that you can MAKE a log backup after creating a new database and running two insert statements only.
I disagree with Michael about the question being unclear or even implying a full backup. Since all actions (creating the DB, two inserts, and the log backup) are explicitly stated, why would I assume an extra action not included in the question is being done anyway? And even if I assume that, why would I assume it being done at the right time?
To me, the question clearly described all actions taken, and the full backup was not among them, so I answered NO. Not "No, because the log backup contains insufficient information" or similar, but "No, because you don't even HAVE a log backup". But the explanation is unclear and confusing.
December 11, 2008 at 2:29 am
I agree with michael kipp
MVDBA
December 11, 2008 at 2:30 am
It is not possible to make a transaction log backup without having a full backup first. So if the transaction log backup available, it implies that a full backup has already been taken.
Steps to recover to point in time
1. Restore Full back with NORECOVERY option
2. Restore Transaction Log with RECOVERY option and STOPAT* clause.
December 11, 2008 at 2:31 am
Hugo Kornelis (12/11/2008)
The explanation is misleading. Especially this part:you cannot use the log backup to recover the database
since it implies that you can MAKE a log backup after creating a new database and running two insert statements only.
I disagree with Michael about the question being unclear or even implying a full backup. Since all actions (creating the DB, two inserts, and the log backup) are explicitly stated, why would I assume an extra action not included in the question is being done anyway? And even if I assume that, why would I assume it being done at the right time?
To me, the question clearly described all actions taken, and the full backup was not among them, so I answered NO. Not "No, because the log backup contains insufficient information" or similar, but "No, because you don't even HAVE a log backup". But the explanation is unclear and confusing.
Clearly the actions and outcome of them were not explicitely described, otherwise it would have mentioned that the log backup failed. If the answers were more like you stated, then I would agree on your answer No, but the question states that there is a log backup, which means there had to be a full backup first.
December 11, 2008 at 2:49 am
Michael Kipp (12/11/2008)
Clearly the actions and outcome of them were not explicitely described, otherwise it would have mentioned that the log backup failed. If the answers were more like you stated, then I would agree on your answer No, but the question states that there is a log backup, which means there had to be a full backup first.
Hi Michael,
The actions were described, the outcome was not included. For none of the actions.
If I give this list of steps to a truly junior DBA, I envisage only two possible outcomes:
1) He's a "literal dummy", doing exactly as told. The log backup will fail, but that won't bother him (he might not even notice). My attempt to restore to a point in time will fail, because there is no backup. (And the junior DBA will never grow beyond his current junior skill level).
2) He's a "eager learner", checking effects of his action and curing problems. He will definitely notice the error message and then attempt to correct the problem. Google, Books Online, or a colleague will help him, and he'll correct the problem by taking a fulll backup first, and then a log backup. Ideally, he will also report about the problem and the solution to me. Either way, I'm still unable to do the point in time restore, since the log backup chain starts after the full backup, which is already after the point where I want to restore. (And the junior DBA has definitely potential to grow to medior level and beyond).
You seem to take the position that the step "do a log backup" implies taking corrective action if it fails. A suspect position in my eyes, since "DO" implies executing a command only; "MAKE" a log backup could easier be construed to ensure that a log backup really is created. But even if you do take that position, then you still can't restore to between the two inserts, since the full backup would have been taken after the second insert. I see no way how the question can imply a full backup being taken on any other time.
December 11, 2008 at 3:01 am
Arshad Ali (12/11/2008)
It is not possible to make a transaction log backup without having a full backup first. So if the transaction log backup available, it implies that a full backup has already been taken.Steps to recover to point in time
1. Restore Full back with NORECOVERY option
2. Restore Transaction Log with RECOVERY option and STOPAT* clause.
Hi Arshad,
The question never states that a log backup is available. But even if it is, it will be based on a full backup taken after the second insert (so explanation in my previous reply), so the sequence of commands you suggest will result in this error:
Msg 4335, Level 16, State 1, Line 1
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
December 11, 2008 at 3:01 am
I see no way how the question can imply a full backup being taken on any other time.
Hi Hugo,
the reason why I think there was a full backup is that the questions says: "can I recover to the point in time between the inserts using the log backup?". For me this means there is a log backup. So, if the possible answers were more like you mentioned then everything would be clear. Reading the explanation of the solution, it is not even any mentioned that you can't even make a log backup, so my guess is that the person who asked the question overlooked this issue.
December 11, 2008 at 3:09 am
Michael Kipp (12/11/2008)
the reason why I think there was a full backup is that the questions says: "can I recover to the point in time between the inserts using the log backup?". For me this means there is a log backup. So, if the possible answers were more like you mentioned then everything would be clear. Reading the explanation of the solution, it is not even any mentioned that you can't even make a log backup, so my guess is that the person who asked the question overlooked this issue.
Hi Michael,
That's why I was, in my previous reply, somewhat willing to grant you that the error message returned from the BACKUP LOG command might have prompted someone to do a full backup. But that would still be after the point in time where the restore should stop, so it won't change the answer to the question.
But maybe we should just agree to disagree on this; it's starting to take more of my time then I should spend here... 🙂
December 11, 2008 at 3:22 am
I agree that the transaction log cannot be backed-up unless a full database backup already exists. At least, this is the case for 2005. Is it different in 2008 (this version is specifically stated in the question)???
Andy
December 11, 2008 at 4:39 am
heh - due to my reading habit of merging words from line above/below into the one i'm reading where they make sense - I read the backup had been created rather than the database :w00t:
Awesome reading skills fail.
:hehe:
December 11, 2008 at 5:34 am
Hi Andy,
I don't think so. I created a database in SQL Server 2008, set the recovery model to FULL, started taking transaction log back without taking full back first and I got the same error which we used to get in SQL Server 2005. See the error below and product version details below:
===================================
System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
December 11, 2008 at 5:46 am
I also made the assumption, along with the others in the 70+% who answered yes at this point, that a full backup was available.
I also understand, although I don't totally agree with, Hugo's point that all steps were pointed out so you should not have assumed there was a full backup.
Of course knowing Steve, and what a great DBA he is, it is only natural to assume that the first thing he did after creatring the database was schedule regular full backups 😀
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2008 at 7:19 am
michael vessey (12/11/2008)
I agree with michael kipp
I agree with Hugo. No was the best answer because no full backup was mentioned in the question, so the log backup would have failed, thus no log backup (or point-in-time recovery) is possible. But the explanation was unclear.
Cheers,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply