Restore Timing of Full Backup + Transaction Logs

I have a full database backup that kicked off at 3:41am and completed at
3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
start and completion times are variable depending on how long the prior step
s
took. The database also has hourly transaction log backups scheduled that
take place on the half hour, so in this case, there was a transaction log
backup that kicked off at 3:30 and finished at 3:43.
In this case, it's pretty obvious that I will restore the full backup and
then restore subsequent logs starting with the next log that kicked off at
4:30, but this possibility of overlap got me wondering about a scenario wher
e
the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
is the full backup capturing just the exact snapshot that it has at 3:20, an
d
the 3:30 log would still apply in order to get the changes from 3:20 on? Or
will the full backup account for all data and changes to data from 3:20-3:40
?
--Shelley
SQL Server 2000 Enterprise Edition, sp3I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> It's true that the log file backup that are done while the full database
> backup runs is redundant, but IF it runs you need the log file if you
> have to do a restore. Even though the changes in the log might already
> be included in the FULL database backup file, SQL server records that a
> log file backup has been performed and it's needed as a part of the
> logfile backup sequence.
>
Good catch, I meant to include that in my "To avoid confusion"
paragraph... :-(|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator