Is data being written to the Db also written to a backup while it's being performed

Question

Is data being written to the Db also written to a backup while it's being performed

Colin Smith

SSC Enthusiast

Points: 195
More actions
July 9, 2008 at 1:56 am

#194838

I've always been under the impression that when a full backup is carried out, it is a snapshot of the database at the point in time that the backup is first started, but I have a situation where I am scheduling a full back to be done at 7:25pm and there are still entries in one of the database's tables for 7:35pm. This table has entries written to it regarding batch jobs that are run against the database, it's a type of logging table and the entry for 7:35pm is for a job that is scheduled to start at 7:30pm.
There are no job steps prior to the backup and it's not part of a maintenance plan, its a straight T-SQL command in the following format...
"BACKUP DATABASE , NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name= )
RESTORE VERIFYONLY FROM DISK = N WITH FILE = @i"
Any guidance in understanding the backup process would be much appreciated.
Thanks Col

Viewing 6 posts - 1 through 5 (of 5 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Colin Smith (7/9/2008)
I've always been under the impression that when a full backup is carried out, it is a snapshot of the database at the point in time that the backup is first started

Colin,

you're impression is wrong. When the backups starts it checks the last LSN number in the transaction log. Once all datapages are written to the backup file all commited transactions which happened after the inital LSN will be applied to the backup file. So the backup is the database at the end of the backup process.

[font="Verdana"]Markus Bohse[/font]

Colin Smith SSC Enthusiast Points: 195 More actions · Answer 2

Thanks Markus, that makes sense with what I'm seeing and now I'm hoping you or someone else can help me with a follow on question. For parallel testing purposes, I'd like to take a backup of production that is just prior to a batch process starting, so is it possible create a backup that is a "snapshot" at the point in time of the backup process begins or do I just estimate when the backup process will finish and adjust the backup schedule accordingly.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

If you're using Enterprise edition of SQL2005 you could take a database snapshot.

[font="Verdana"]Markus Bohse[/font]

Colin Smith SSC Enthusiast Points: 195 More actions · Answer 4

Thanks Markus,

Unfortunately not using Enterprise, but thanks anyway.

Cheers, Col

happycat59 One Orange Chip Points: 29389 More actions · Answer 5

I would suggest that you take a full backup sometime well before your batch process commences and transaction log backup regular after that. If you need to restore to a point in time, you can easily do this by using the "STOPAT" option of the restore command.