March 16, 2010 at 10:59 am
Oleg,
I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 16, 2010 at 11:04 am
sjimmo (3/16/2010)
Oleg,I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.
I know you were not Steve, and I apologize if my reply caused you to think so. I was just curious to actually check the backup behavior, so it is now tattooed in my brains.
Oleg
March 16, 2010 at 11:06 am
I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.
Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).
From the manual:
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.
So if the log was restored then all the data would be present.
IMHO -- Mark D Powell --
March 16, 2010 at 11:14 am
Mark D Powell (3/16/2010)
I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).
From the manual:
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.
So if the log was restored then all the data would be present.
IMHO -- Mark D Powell --
I'm confused. For the data to be present after a restore, it has to be in the backup, doesn't it?
Could you explain what you are trying to say?
March 16, 2010 at 11:29 am
I think the link http://msdn.microsoft.com/en-us/library/ms186289.aspx explains it very succinctly that 'Full database backups represent the database at the time the backup finished.'
March 16, 2010 at 11:33 am
Cliff Jones (3/16/2010)
I think the link http://msdn.microsoft.com/en-us/library/ms186289.aspx explains it very succinctly that 'Full database backups represent the database at the time the backup finished.'
Nice find.
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 16, 2010 at 11:53 am
Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.
My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.
Mark D Powell
March 16, 2010 at 12:19 pm
Mark D Powell (3/16/2010)
Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.
Mark D Powell
But what is in the backup is what is restored from the backup. Any data not committed prior to the completion of the backup is still in the backup, just rolled back during the restore of the database.
March 16, 2010 at 12:21 pm
Mark D Powell (3/16/2010)
Lynn, after a restore of a database in full backup mode the database reflects both the contents of the database and the transactions recovered from the log files since you must restore the logs as part of the restore when full backup mode is used.My argument is that the official answer is based on what is in the database backup file and not in the restored database which is what the question asked.
Mark D Powell
Now I'm confused, too. Mark, you refer to the "official answer". Do you mean the one posted as the "Correct answer" and "Explanation" by Suja? Here they are for easy reference:
Correct answer: It depends. If the insert statement gets completed before the backup process is over then it will contain all 10000 rows
Explanation: Whenever a full backup is initiated it will place a pointer in the transaction log in order to understand from where the backup started. So any transaction happening,after the pointer, will be included in the backup. 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. And if insert statement gets completed before the backup process then all 10000 rows will be present in the table after restoring this backup.
The confusion I'm having is the apparent conflict between your statement that "... you must restore the logs..." and the idea that a "Full backup" includes any transaction logs necessary to recover the db to the point at which the backup completed. As Microsoft puts it:
Using a Full Database Backup to Restore the Database
You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.
See http://msdn.microsoft.com/en-us/library/ms186289.aspx (thanks to Cliff fo r this link).
March 16, 2010 at 12:41 pm
In which case the insert statement data would not be present unless it had completed prior to the backup ending.
The key word in the question is restored. As I quoted a full recovery database restore must include the logs hence the data will be present.
March 16, 2010 at 12:44 pm
Since the question only wanted to know whether the 10,000 rows would be included in the backup, I would concur that the explanation is correct. We do not need a complete database restore, we are just looking to see if we would have the 10,000 inserted records. True, some data would be lost with no log restore, but that was not what the question was after.
Is it possible I really understood the question??? 😉
KILL 51
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 16, 2010 at 12:52 pm
Ah. I think I see what you're getting at, Mark. The original explanation implies that as many of the 10000 rows as have been inserted by the end of the backup would then be included in a restored version of that database. That, of course, would not be true if they were part of a transaction that was committed after the backup finished, But wouldn't it still be true if the inserts were not designated within an explicit transaction? That is, if each insert were implicitly committed, then you could get some but not all of the 10,000 rows in your restored db.
Or am I still missing something here?
March 16, 2010 at 1:38 pm
Mark D Powell (3/16/2010)
Because this is a full backup ...From the manual:
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.
So if the log was restored then all the data would be present.
"Full backup" and "full recovery model" are two different things. You may do a full backup of a database that uses any of the three recovery models. A full backup always includes a part of the transaction log, no matter which recovery model is used. And that part of the transaction log will be processed during the restore process.
March 16, 2010 at 1:40 pm
Well, I am admitedly being a bit picky with this, but the question never states which version of SQL Server it applies to. This behavior changed (I think it was between 7.0 and 2000).
Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well?
March 16, 2010 at 2:22 pm
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" 🙂
ChrisMoix-87856 (3/16/2010)
This behavior changed (I think it was between 7.0 and 2000).
It's very interesting. Could you please describe the previous behavior?
ChrisMoix-87856 (3/16/2010)
Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well?
Yes, they do.
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply