SQL 2008 SP3 DB Backup

Question

SQL 2008 SP3 DB Backup

HBK_4700

SSCrazy Eights

Points: 8932
More actions
January 15, 2013 at 8:29 am

#287600

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?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

Any Log activity while the backup executes is also backed up. This effectively means that when you restore the backup the log might be rolled forward, processing these transactions.

Steve Hall
Linkedin
Blog Site

arnipetursson SSCertifiable Points: 6557 More actions · Answer 2

Full database backups represent the database at the time the backup finished.

http://msdn.microsoft.com/en-us/library/ms186289.aspx

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 3

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

george sibbald SSC Guru Points: 104210 More actions · Answer 4

If you are talking about a sql native backup it is definitely consistent at the point the backup completed

read this[/url]

---------------------------------------------------------------------

sjimmo SSChampion Points: 11139 More actions · Answer 5

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

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 6

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.

sjimmo SSChampion Points: 11139 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

sjimmo SSChampion Points: 11139 More actions · Answer 9

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

george sibbald SSC Guru Points: 104210 More actions · Answer 10

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.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sjimmo SSChampion Points: 11139 More actions · Answer 14

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