August 17, 2011 at 10:39 am
Hi,
I have configured the log shipping for 10 databases. The log shipping backup,copy & restore jobs run at every 15 mins starting from 12:00 am to 11:59 pm
The full backup job which takes the full backup of 10 databases runs daily at 9 pm and it runs for 3 hours
Here, during the Full backup, the log backups are also happening at regular schedule intervals of 15 mins i.e 9 pm, 9:15 pm, 9:30 pm, 9:45 pm ,10:00 pm, 10:15 pm...11:45 pm
Question:
1. What is the best practice for the above scenario?
2. What happens running Full & log backup of a database at same time?
3. If I want to restore the full backup of a database, then do I need to restore the Full backup occured at 9 pm with norecovery and then log backup of 9:00 pm, 9:15 pm, 9:30...11:45 pm?
Please advice how to manage this as per best practices allowing point in time recovery.
Thanks
August 17, 2011 at 1:35 pm
Backups occasionally fail or get corrupted by storage. Full backups do not break or clear the log chain. If last night's backup is not recoverable, you can use the prior night's full backup and restore forward provided you have the full log chain, even though a full backup occurred during that time.
So:
gmamata7 (8/17/2011)
Question:1. What is the best practice for the above scenario?
I would advise that the log backups continue on your schedule regardless of the full backup running.
2. What happens running Full & log backup of a database at same time?
You cause more I/O on your system. Small price to pay in order to keep grabbing logs.
While the full backup is running, your log backups will not clear out the inactive portion of the log, and the last step in a full backup is to add in enough transaction log backup to capture the changes that occurred to pages after they were backed up and roll the database forward on restore to the point at which the backup ended.
Once the full is complete, your transaction log backups will be able to clear the active portion of the log again.
3. If I want to restore the full backup of a database, then do I need to restore the Full backup occured at 9 pm with norecovery and then log backup of 9:00 pm, 9:15 pm, 9:30...11:45 pm?
That depends 🙂
It's based on when the backup ended, as the full backup includes the transaction log backups necessary to roll the database forward to that point in time. Fortunately, attempting to restore a log that's 'too late' (for example, if you try to use the 10:15pm log backup and you actually should have used the 9:45pm log backup) won't trash the restore, you'll simply get an error that you need to try again with an earlier log backup.
You're shooting at 10 databases in your plan, so the start and end times of the individual database backups will change over time.
My basic point: keep taking log backups on your schedule, and never stop doing that. Transaction log backups are the foundation of a functioning restore strategy. Regular full backups just make life easier when it's time to perform a restore. Additionally, sticking to a log schedule allows for predictable results: you know you expect a log backup every 15 minutes when you're digging out the files to do a restore. If the schedule changes depending on time of day, it just makes things look funny - a list of files every 15 minutes with the exception of gaps caused by intentional pauses looks at first like something failed or you don't have the files. During a business-critical under-pressure restore, the appearance of missing files slows you down while you check to be sure those files aren't missing, that they simply don't exist due to the schedule (and you may not be the one performing that restore, unless you never go on vacation, etc.).
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply