Tape Backup Concern
-
Grettings All,
First things first....SQL Server 7.0 SP3 running on NT 4 SP6a and using Legato's Networker/SQL Business Suite for tape backups. 5 gig User database gets backed up to disk once per day with hourly log dumps and nightly tape backups.
Periodically, the tape backup will cause the hourly disk log dumps to fail as it maintains an exclusive lock on the db during the process. Then once released, the log dumps continue to complete normally.
Here's my concern. According to BOL, SQL Server needs an unbroken chain of successful log dumps in order to perform a restore. Let's say User DB does full disk back up at 9:30 PM. Hourly disk log dumps continue until midnight, then fail at 12:00 and 1:00 am due to lock set by tape back up. Subsequent hourly log dumps complete successfully until the morning.
Now if I suffer a problem/failure at 10:15 the next morning what are my recovery options? Can I only perform a disk restore up until 11:00 pm which was the last successful log dump before the tape backup conflict? Or will I be able to skip over the 2 failed log dumps (since they not due to a H/W or media failure) and continue restoring logs up until the last dump which would have been at 10 am? The language in BOL is kind of ambiguous on this. Thanks for your thoughts and comments.
My hovercraft is full of eels.
-
Log backup failure doesn't break the log backup chain. In your situation, 2:00am log backup will capture all transaction log entries since 11:00pm. You can restore full database backup from 9:30pm, 10:00pm log backup, 11:00pm log backup and then 2:00am log backup and onward untill 10:00am next day.
What you have to do is to test the restoration and try to find out why the tape backup causes the log backup failure and resolve it.
Edited by - Allen_Cui on 07/14/2003 1:49:45 PM
-
Thank you very much Allen and that was what I suspected. It makes sense the way you put it.
Legato is managed by our network admins therefore I don't really have much access to testing and configurations. And there are licensing issues as well.
But as I understand it, actually 2 backups are performed in the process using the SQL add-in. The first is a simple copy of all server files which will not capture my open DB's but will capture the most recent disk backups. The second portion is the 'open file manager' which only captures the DB files and consequently causes the locking issue. This is because it actually uses an SQL client to perform the backups and you can't run 2 on the same DB simultaneously. I don't know that there is a way around this but will investigate further with the admins. Thanks again. Regards,
Sandman
My hovercraft is full of eels.
But as I understand it, actually 2 backups are performed in the process using the SQL add-in. The first is a simple copy of all server files which will not capture my open DB's but will capture the most recent disk backups. The second portion is the 'open file manager' which only captures the DB files and consequently causes the locking issue. This is because it actually uses an SQL client to perform the backups and you can't run 2 on the same DB simultaneously. I don't know that there is a way around this but will investigate further with the admins. Thanks again. 
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply