December 6, 2017 at 10:21 am
Hi All,
My understanding is when in DB is backed up in "simple" mode , even though log is not used, i a "Checkpoint" is still issued at backup to flush out all committed stuff in memory to the log AND written to the DB and does it as a matter of course. Stuff in mid flight i.e transaction not completed are not written to the DB.
I have a colleague who insists changing mode to "Full" before doing the backup insisting that in "simple" mode the the whole data is not backed because of stuff in the log (which I know is redundant) still contains stuff that is required to for the complete picture. Then the mode is changed back to simple
Is this the case ?
December 6, 2017 at 10:35 am
robinrai3 - Wednesday, December 6, 2017 10:21 AMHi All,My understanding is when in DB is backed up in "simple" mode , even though log is not used, i a "Checkpoint" is still issued at backup to flush out all committed stuff in memory to the log AND written to the DB and does it as a matter of course. Stuff in mid flight i.e transaction not completed are not written to the DB.
I have a colleague who insists changing mode to "Full" before doing the backup insisting that in "simple" mode the the whole data is not backed because of stuff in the log (which I know is redundant) still contains stuff that is required to for the complete picture. Then the mode is changed back to simple
Is this the case ?
What?
The transaction log is still used when a database is using the SIMPLE recovery model. When a full backup is completed enough of the transaction log is backed up to ensure a consisted database upon restore, this means being able to roll forward completed transactions that may not have been fully written to the database and to rollback those transactions that had not be committed to the log.
December 6, 2017 at 10:53 am
robinrai3 - Wednesday, December 6, 2017 10:21 AMHi All,My understanding is when in DB is backed up in "simple" mode , even though log is not used, i a "Checkpoint" is still issued at backup to flush out all committed stuff in memory to the log AND written to the DB and does it as a matter of course. Stuff in mid flight i.e transaction not completed are not written to the DB.
I have a colleague who insists changing mode to "Full" before doing the backup insisting that in "simple" mode the the whole data is not backed because of stuff in the log (which I know is redundant) still contains stuff that is required to for the complete picture. Then the mode is changed back to simple
Is this the case ?
Your understanding, as well as your colleague's, is incorrect.
Regardless of the recovery model, the transaction log is always used. Data in "mid-flight" is always committed to disk.
A full backup will always get the "whole data" in any recovery model.
The main difference between full and simple is that when a checkpoint is issued, simple truncates the log file from the oldest checkpoint to the most recent IF all of the transactions have been committed to disk.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 6, 2017 at 10:59 am
robinrai3 - Wednesday, December 6, 2017 10:21 AMI have a colleague who insists changing mode to "Full" before doing the backup insisting that in "simple" mode the the whole data is not backed because of stuff in the log (which I know is redundant) still contains stuff that is required to for the complete picture. Then the mode is changed back to simpleIs this the case ?
No.
Being that they insist and are so far off base, you really should bring this up with your boss. I'd be nervous having a colleague who doesn't understand backups and I'd be nervous if I was your company depending on the data.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply