January 15, 2013 at 8:29 am
Hi,
Our prod db backup job starts at 1 AM and finish at around 5:30 AM.
So my question is How does the backup work – Keep on reading and writing at the same time?
is it a snapshot of the database when backup job started? or it includes all the transactions between 1 AM and 5:30 AM?
January 15, 2013 at 9:41 am
Full database backups represent the database at the time the backup finished.
January 15, 2013 at 9:43 am
I checked with senior DBA here.
backup is the snapshot as of the moment the backup statement commenced. anything after that will not be there in full backup.
in my case backup is as of 1 am 1/13
January 15, 2013 at 9:45 am
January 15, 2013 at 9:56 am
backup is the snapshot as of the moment the backup statement commenced. anything after that will not be there in full backup. in my case backup is as of 1 am 1/13
A database dump first performs a checkpoint, committing any data that has not been committed to that point. All data changes are held in the transaction log until the dump finishes. The dump will then backup those changes.
A great reference is Pul Randal - http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/
as well as http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/
Please take the time to read these links, and then go back to challenge your Sr DBA;-)
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
January 15, 2013 at 10:17 am
Thanks ALL. i got it.
so current situation:
i need to restore database to test something (need to restore before 4 AM 1/13/2013)
1/13 backup started at 1 AM and finished at aroung 6 AM.
I have the tlogs(every 15 min) from 1/12 11:15 AM to till time on local disk...rest went to tape, we are not taking diff backups.
we are taking native sql backups, backup size is 230 GB
is there any way in sql server to restore as of before 4 AM 1/13 using 1/13 full backup completed at 6 am? or anyway to to roll backward to a point in time after the restore is completed.
how to proceed now? please advise.
January 15, 2013 at 10:24 am
Your backup started before the time you want and ended after. The data should be there when you restore. The T-Log dumps from 1/12 are now worthless. You cannot restore previous T-Log dumps to a database that was backed up after.
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
January 15, 2013 at 10:33 am
nihal9200_kwada (1/15/2013)
Thanks ALL. i got it.so current situation:
i need to restore database to test something (need to restore before 4 AM 1/13/2013)
1/13 backup started at 1 AM and finished at aroung 6 AM.
I have the tlogs(every 15 min) from 1/12 11:15 AM to till time on local disk...rest went to tape, we are not taking diff backups.
we are taking native sql backups, backup size is 230 GB
is there any way in sql server to restore as of before 4 AM 1/13 using 1/13 full backup completed at 6 am? or anyway to to roll backward to a point in time after the restore is completed.
how to proceed now? please advise.
To restore to 1/13 4:00 AM you will need the previous days (1/12) full backup file plus all the t-log backups since that backup up to the t-log backup that includes the point in time to which you want to restore.
January 15, 2013 at 10:56 am
Lynn
To restore to 1/13 4:00 AM you will need the previous days (1/12) full backup file plus all the t-log backups since that backup up to the t-log backup that includes the point in time to which you want to restore
Since their 1/13 database dump started on 1/13 at 1:00 AM, wouldn't that mean that the T-Log dumps for 1/12 have stopped at 1:00? I believe the LSN chain broke at the start of the new database dump. My understanding is that once the backup begins, it takes any dirty data and commits it. It then places a marker on the T-Log so that when the db dump completes the t-log is then dumped making the db dump complete. Thus the reason that you would need the previous full dump but cannot apply T-Log dumps to a full dump completed after the T-Log dumps.
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
January 15, 2013 at 10:59 am
sjimmo (1/15/2013)
Lynn
To restore to 1/13 4:00 AM you will need the previous days (1/12) full backup file plus all the t-log backups since that backup up to the t-log backup that includes the point in time to which you want to restore
Since their 1/13 database dump started on 1/13 at 1:00 AM, wouldn't that mean that the T-Log dumps for 1/12 have stopped at 1:00? I believe the LSN chain broke at the start of the new database dump. My understanding is that once the backup begins, it takes any dirty data and commits it. It then places a marker on the T-Log so that when the db dump completes the t-log is then dumped making the db dump complete. Thus the reason that you would need the previous full dump but cannot apply T-Log dumps to a full dump completed after the T-Log dumps.
from 2008 on you can take tlog backups whilst the full backup runs, they just don't truncate the log. A full backup does not 'dump' the log.
---------------------------------------------------------------------
January 15, 2013 at 11:01 am
sjimmo (1/15/2013)
I believe the LSN chain broke at the start of the new database dump.
Full backups do not and never have broken the log chain,
My understanding is that once the backup begins, it takes any dirty data and commits it. It then places a marker on the T-Log so that when the db dump completes the t-log is then dumped making the db dump complete.
Mostly correct, yes
Thus the reason that you would need the previous full dump but cannot apply T-Log dumps to a full dump completed after the T-Log dumps.
Err.... not following.
p.s. Backup, not dump.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2013 at 11:15 am
george sibbald (1/15/2013)
sjimmo (1/15/2013)
Lynn
To restore to 1/13 4:00 AM you will need the previous days (1/12) full backup file plus all the t-log backups since that backup up to the t-log backup that includes the point in time to which you want to restore
Since their 1/13 database dump started on 1/13 at 1:00 AM, wouldn't that mean that the T-Log dumps for 1/12 have stopped at 1:00? I believe the LSN chain broke at the start of the new database dump. My understanding is that once the backup begins, it takes any dirty data and commits it. It then places a marker on the T-Log so that when the db dump completes the t-log is then dumped making the db dump complete. Thus the reason that you would need the previous full dump but cannot apply T-Log dumps to a full dump completed after the T-Log dumps.
from 2008 on you can take tlog backups whilst the full backup runs, they just don't truncate the log. A full backup does not 'dump' the log.
I may be wrong, but I thought this was true in SQL Server 2005 as well.
January 15, 2013 at 11:32 am
Lynn Pettis (1/15/2013)
george sibbald (1/15/2013)
sjimmo (1/15/2013)
Lynn
To restore to 1/13 4:00 AM you will need the previous days (1/12) full backup file plus all the t-log backups since that backup up to the t-log backup that includes the point in time to which you want to restore
Since their 1/13 database dump started on 1/13 at 1:00 AM, wouldn't that mean that the T-Log dumps for 1/12 have stopped at 1:00? I believe the LSN chain broke at the start of the new database dump. My understanding is that once the backup begins, it takes any dirty data and commits it. It then places a marker on the T-Log so that when the db dump completes the t-log is then dumped making the db dump complete. Thus the reason that you would need the previous full dump but cannot apply T-Log dumps to a full dump completed after the T-Log dumps.
from 2008 on you can take tlog backups whilst the full backup runs, they just don't truncate the log. A full backup does not 'dump' the log.
I may be wrong, but I thought this was true in SQL Server 2005 as well.
Indeed it was. In 2000 a full backup just blocked log backups (didn't cause them to fail). In 2005 onward they can run concurrently.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2013 at 11:37 am
Gail - Thanks
Lynn - you are correct on 2005. Just finished some light reading.
Just proves you never stop learning.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply