Transaction log backup during file backup

Question

Transaction log backup during file backup

Karim Hemani

SSCrazy

Points: 2063
More actions
January 1, 2006 at 11:33 pm

#152686
Hi,
I had a couple of questions regarding backups that I would appreciate if someone answered:
- Does a file backup create a copy of the file as it existed at the time the backup started or the backup finished?
- Why shouldn't one backup the transaction log while a file backup is running and wait till it has completed? I mean, what difference does it really make?
Karim

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I can see cans with worms spilling out of them depending on how you interpret "file backup"....

If by "File Backup" you mean using some sort of open file manager and backing up the files, then that is definitely not the way to go with MSSQL (or most other DBs for that matter)...

If instead you mean a traditional MSSQL backup, then you shouldn't do a transaction log backup at the same time as a file backup(I've never tried it) - it doesn't really make sense to do so.

I'll assume that you are using FULL recovery (you can't do transaction log backups in SIMPLE recovery mode)...

Your SQL backups work like so.. A full backup is taken. At regular intervals, transaction log backups are taken - these could also be referred to as incremental backups. To restore, you must restore the full backup and then each and every one of the incremental log backups in order. You could think of the full backup as acting as an "anchor" for the subsequent log backups... Thus, if you were doing a log backup whilst also doing a full backup, which full backup does the log refer to? The prior full backup plus any prior log backups, or the incomplete full backup you are currently doing???

As for the first question, I am not entire sure... The backup file is certainly consistent to a given point in time (ie, you can have activity in the DB whilst the backup is occurring). I've look in BOL and did a quick google search - couldn't find a real answer I'm afraid. You could try to simulate by making a HUGE DB, starting a backup and then when the backup is 50% done, make a data change. Let the backup finish. Then restore and see if your data change is there...

Good luck

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 2

Backup is at point of time it is started. If you make a change while the backup is running, the pages that need to be changed will be grabbed and backed up, interrupting the backup order that is in process. Once those pages are backed up, the transaction completes and the backup continues.

If you are thinking to backup your log every minute and your full backup takes a hour, the full backup will be consistent from when it started. The logs since the start time will need to be applied to recover.

SQL Server tracks the order of backups and you couldn't restore them out of order and have issues.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Maybe this is just misunderstanding of your post, Steve, but I was pretty sure that when backup is terminating, all transactions that were finished during the backup are written with it, effectively making the backup a picture of the status in which the database was in the moment when backup ended. However, full backup should be followed by a log backup to capture transactions that are not yet commited.

If full backup is in progress, SQL Server will not allow log backups - all attempts to backup log during full backup will fail and return error message saying this.

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

"In Microsoft SQL Server 2005, you can back up the log while a data or differential backup is running. "

http://msdn2.microsoft.com/en-us/library/ms190217.aspx

jg

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Nice MS article from Jeff - would be nice if it said what the implications of a concurrent log backup are (if any)?

Main thing I liked from the MS article is I now know about COPY backups - very useful

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

Ian,

My understanding is that the ability to perform log backups during a full backup is intended to "cover the gap" that exists in SQL 2000 v point-in-time restore. This, according to the MS tech guy at the SSUG in Waukesha, WI, allows the administrator to do a point-in-time restore even if that point is (was) during a full backup.

That's how I remember it, anyway.

jg

Lubo Plaskon Old Hand Points: 303 More actions · Answer 7

Thanks guys,

I didn't know if it's possible to do a point-in-time recovery for the time between the start of a full backup and the first log backup after. Looks like it is not possible in SQL 2000 and the best I can do to narrow the gap is to backup the log right after the full backup.

Please correct me if I'm wrong. Thanks,

LP