Mixing
Backup cut-off time for Full, Differential, Log, File, Partial backups
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
My understanding was that all backups contain data up to the time/point when backup operation was completed.
Administering a SQL Database Infrastructure - Exam Ref 70-764 says:
Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.
(emphasis mine)
However, for differential backups, the book implies something different:
Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.
Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?
How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?
sql-server backup
edited 54 mins ago
BradC
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
3
down vote
favorite
My understanding was that all backups contain data up to the time/point when backup operation was completed.
Administering a SQL Database Infrastructure - Exam Ref 70-764 says:
Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.
(emphasis mine)
However, for differential backups, the book implies something different:
Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.
Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?
How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?
sql-server backup
edited 54 mins ago
BradC
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
My understanding was that all backups contain data up to the time/point when backup operation was completed.
Administering a SQL Database Infrastructure - Exam Ref 70-764 says:
Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.
(emphasis mine)
However, for differential backups, the book implies something different:
Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.
Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?
How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?
sql-server backup
edited 54 mins ago
BradC
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
My understanding was that all backups contain data up to the time/point when backup operation was completed.
Administering a SQL Database Infrastructure - Exam Ref 70-764 says:
Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.
(emphasis mine)
However, for differential backups, the book implies something different:
Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.
Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?
How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?
sql-server backup
sql-server backup
edited 54 mins ago
BradC
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 54 mins ago
BradC
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 54 mins ago
BradC
6,05853259
edited 54 mins ago
BradC
6,05853259
edited 54 mins ago
BradC
6,05853259
6,05853259
asked 2 hours ago
variable
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
variable
1398
asked 2 hours ago
variable
1398
1398
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
3
down vote
See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):
A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
- Checkpoint the database and note the current log sequence number
- Start reading the data files
- Stop reading the data files, and note the current log sequence number
- Read enough of the log file to capture transactions completed between steps 1 and 3.
A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.
Regarding Differential backups, Randal (in the same article linked above) says:
A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.
I'll look for an authoritative source about log backups.
Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.
answered 2 hours ago
BradC
6,05853259
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
add a comment |Â
up vote
0
down vote
If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.
My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop
answered 48 mins ago
Alen
34115
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):
A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
- Checkpoint the database and note the current log sequence number
- Start reading the data files
- Stop reading the data files, and note the current log sequence number
- Read enough of the log file to capture transactions completed between steps 1 and 3.
A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.
Regarding Differential backups, Randal (in the same article linked above) says:
A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.
I'll look for an authoritative source about log backups.
Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.
answered 2 hours ago
BradC
6,05853259
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
add a comment |Â
up vote
3
down vote
See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):
A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
- Checkpoint the database and note the current log sequence number
- Start reading the data files
- Stop reading the data files, and note the current log sequence number
- Read enough of the log file to capture transactions completed between steps 1 and 3.
A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.
Regarding Differential backups, Randal (in the same article linked above) says:
A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.
I'll look for an authoritative source about log backups.
Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.
answered 2 hours ago
BradC
6,05853259
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
add a comment |Â
up vote
3
down vote
up vote
3
down vote
See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):
A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
- Checkpoint the database and note the current log sequence number
- Start reading the data files
- Stop reading the data files, and note the current log sequence number
- Read enough of the log file to capture transactions completed between steps 1 and 3.
A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.
Regarding Differential backups, Randal (in the same article linked above) says:
A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.
I'll look for an authoritative source about log backups.
Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.
answered 2 hours ago
BradC
6,05853259
See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):
A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
- Checkpoint the database and note the current log sequence number
- Start reading the data files
- Stop reading the data files, and note the current log sequence number
- Read enough of the log file to capture transactions completed between steps 1 and 3.
A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.
Regarding Differential backups, Randal (in the same article linked above) says:
A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.
I'll look for an authoritative source about log backups.
Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.
answered 2 hours ago
BradC
6,05853259
edited 1 hour ago
answered 2 hours ago
BradC
6,05853259
answered 2 hours ago
BradC
6,05853259
answered 2 hours ago
BradC
6,05853259
6,05853259
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
add a comment |Â
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
The book says - Differential contains data till the backup operation was executed. Is this incorrect?
– variable
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
"Executed" is vague and (in my opinion) is not an attempt to be technical about the matter. Paul Randal was on the SQL engine team, so is a trusted expert.
– BradC
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
Ok, thanks for this; Ill keep the post open for some more time to get more views from others. Thanks.
– variable
2 hours ago
1
1
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
The same applies to diff and file(group) as to full backup. "Partial" backup is same as fil(group) backup. Log backups are special since they include everything in the ldf file since last log backup (up to the point of end-of-that-log-backup).
– Tibor Karaszi
16 mins ago
add a comment |Â
up vote
0
down vote
If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.
My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop
answered 48 mins ago
Alen
34115
add a comment |Â
up vote
0
down vote
If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.
My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop
answered 48 mins ago
Alen
34115
add a comment |Â
up vote
0
down vote
up vote
0
down vote
If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.
My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop
answered 48 mins ago
Alen
34115
If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.
My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop
answered 48 mins ago
Alen
34115
answered 48 mins ago
Alen
34115
answered 48 mins ago
Alen
34115
answered 48 mins ago
Alen
34115
34115
add a comment |Â
add a comment |Â
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219859%2fbackup-cut-off-time-for-full-differential-log-file-partial-backups%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password