March 16, 2010 at 2:24 pm
IIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup. SQL 2000 containes what was "in" the database at the end of the backup.
March 16, 2010 at 3:49 pm
ChrisMoix-87856 (3/16/2010)
IIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup.
You are mistaken. Here is a quote from BOL 7.0:
Creating and Restoring a Database Backup
Restoring a Database BackupThis process ensures that the restored database is a copy of the database as it existed when the backup operation completed, except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.
I found BOL 7.0 here: http://www.microsoft.com/technet/prodtechnol/sql/70/downloads/books.mspx (careful, there's 12MB .exe file).
March 17, 2010 at 2:42 am
probably some kind of rollback should have been in place.. 🙂
CirquedeSQLeil (3/16/2010)
Great question. Would have been better if I had not missed my click.:-D
March 17, 2010 at 2:50 am
Oleg Netchaev (3/16/2010)
sjimmo (3/16/2010)
Hugo, after getting the questin wrong;-) and re-reading a few times in disbelief I found on <http://msdn.microsoft.com/en-us/library/ms175477.aspx> - Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup.So, in a full backup, all completed transactions will be added to the full dump. everything else will be added to the transaction log dumps if being performed.
All completed transactions are added to the dump regardless whether they were initiated before or after the dump started, so long as they were committed before the dump is completed. Looks like the note on the BOL page you mention is the key. it states that full database backups "represent the whole database at the time the backup finished". Since I like to make the short story long, here is the small test to confirm, with the only exception it does not have the while loop for the while loop is evil as the select is already a loop: I used 2 instances of the SSMS and 3 query windows. In the first window of instance 1 I started a full backup of the AdventureWorks:
thanks for ur example, understood it completely...:-)
March 17, 2010 at 3:40 am
I go with Hugo's explanation, except that the backup of the log does take a finite time. Particularly if you have a large active log then it may take a while, so the transaction inclusion cutoff is some (indeterminate) time before the backup completes. The time that the data page backup completes can probably be approximated by the time the message gets generated
For pre-yearend backups I have used the concept in practice; timing my multi-hour backup to terminate maybe 15 minutes past the cutoff time for user processing, and taking a log backup after cutoff for good measure
March 17, 2010 at 6:18 am
vk-kirov (3/16/2010)
ChrisMoix-87856 (3/16/2010)
IIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup.You are mistaken. Here is a quote from BOL 7.0:
Creating and Restoring a Database Backup
Restoring a Database BackupThis process ensures that the restored database is a copy of the database as it existed when the backup operation completed, except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.
I found BOL 7.0 here: http://www.microsoft.com/technet/prodtechnol/sql/70/downloads/books.mspx (careful, there's 12MB .exe file).
Maybe I am remembering back to my 6.5 days (or before)... Good thing I hedged myself with the IIRC, etc. 🙂
March 17, 2010 at 9:09 am
On a restore, first all data pages are restored; then the log entries included in the backup are used to
a) roll forward any transactions that were committed while the backup was running, and
b) roll back any changes from transactions that were not yet commited when the backup finished.
The latter is required because the backed up data pages may already have been modified by those transactions.
This is completely correct, with the understanding that the restore is being done WITH RECOVERY [which is the default].
If WITH NORECOVERY is specified, then a) is done but b) is not. The uncommitted transactions might complete in a future restore operation, so they must not be rolled back.
What I'm not sure about is how the backup avoids chasing its tail. If it's backing up the log after the pointer, can't a long-running transaction be filling the log while the backup is trying to complete?
March 17, 2010 at 3:33 pm
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."
Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
March 17, 2010 at 6:23 pm
gamcall (3/17/2010)
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized.
2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication.
3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.
4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can obscure meaning.
5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations.
6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.
Sorry for the rant, but those who live in glass houses.....
March 17, 2010 at 6:57 pm
john.arnott (3/17/2010)
gamcall (3/17/2010)
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized.
2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication.
3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.
4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can obscure meaning.
5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations.
6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.
Sorry for the rant, but those who live in glass houses.....
[ join rant ]
I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.
[/ end rant]
March 17, 2010 at 8:06 pm
Lynn Pettis (3/17/2010)
john.arnott (3/17/2010)
gamcall (3/17/2010)
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized.
2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication.
3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.
4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can obscure meaning.
5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations.
6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.
Sorry for the rant, but those who live in glass houses.....
[ join rant ]
I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.
[/ end rant]
Beating the same rant drum. We are not here to analyze or criticize the linguistic ability, neither in written nor verbal form, of the members of this community.
If it is really a problem of interpretation of what has been said or meant, then ask directly rather than nitpicking.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 9:22 pm
Lynn Pettis (3/17/2010)
john.arnott (3/17/2010)
gamcall (3/17/2010)
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized.
2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication.
3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.
4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can obscure meaning.
5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations.
6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.
Sorry for the rant, but those who live in glass houses.....
[ join rant ]
I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.
[/ end rant]
Lynne, as always well said.
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 19, 2010 at 1:37 pm
vk-kirov (3/16/2010)
Hugo Kornelis (3/16/2010)
the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.I would like to add: "from the start of the oldest uncommitted transaction (which was in an uncommitted state when the backup started)", because without this remark one may think "the oldest uncommitted transaction which was in an uncommitted state when the backup finished to copy data pages" 🙂
If I undertand it rightly, the correct wording is more like "from the start of the oldest transaction that was uncommitted at the start of the backup, or from the first log entry after the start of the backup if there is no such transaction". The point is that a section of the log will be included in the backup even if all transactions are committed by the point in time at which the backup of everything other than the log is complete (unless there has been no update or rollback or commitment since the start of the backup and no transaction was uncommitted at the start of the backup). Maybe you intended your addition to be an explanation of Hugo's words, but without some sign like "that is" or "i.e." after the opening parenthesis it can be read as an additional condition on the time indicated in which case it seems to suggest that if there is not a transaction which is uncommitted at the time the backup (of pages, not including the log) is complete there is no defined starting time for the section of log to be backed up so it makes the description less clear than Hugo's explanation. My addition of an alternative start time is needed to cope with the case of a transaction that began after the backup started and committed before the end of the backup of the pages.
I actually believe that the answer is somehat misleading (although it is correct) because most people will (despite the inclusion of the word "all") assume that if the select statement does not complete then none of the rows will be in the backup. But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc and to the transaction log before the select statement completes, and since they are in the trasaction log they are in the backup, and if page copying is sufficiently low they will also be in backed up (dirty) pages (although they will not of course be included in any restore WITH RECOVERY from the backup). Even a low number like 10000 can be enough rows to make this happen (especially if there's not much RAM and each row contains a nice big varchar(MAX) value).
Tom
March 19, 2010 at 1:54 pm
gamcall (3/17/2010)
"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:
Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup.
Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.
:angry:
I think I have to comment that gamcall's insistance of using "is" instead of "gets" is silly because "is completed" is ambiguous (can describe either a state or an event) whereas "gets" resolves the ambiguity (it can only describe an event). Ambiguity in a technical scenario is a worse offence than using "how many" instead of "however many" (many versions of English allow "how many" in that context) or a trivial spelling error ("h" in were). So apparently he wants to place a duty on editors to fix trivia and at the same time change correct unambiguous English into ambiguous (and therefor, in a technical context requiring precision, incorrect:exclamationmark:) English.
Tom
March 19, 2010 at 1:57 pm
john.arnott (3/17/2010)
Sorry for the rant, but those who live in glass houses.....
Don't be sorry, I imagine most who see it will be gald to see that someone provided the deserved response quickly.
Tom
Viewing 15 posts - 31 through 45 (of 65 total)
You must be logged in to reply to this topic. Login to reply