Some transaction log backup questions
-
Hi,
We have some maintenance plans set up for a large db(30gb) and several small ones. We have a nightly db backup, hourly txn backup, weekly db backup and weekly txn backup(yeah, who knows why
- I'm just trying to fix this mess). Daily stuff is written to E:\Backups, weekly is sent to E:\WeeklyBackups. The large database is NOT backed up daily - this is development with limited space. It has come to my attention that the txn log backups have been failing for a while. While inspecting the set up, I saw that the large database and some associated ones were set to bulk-logged. I think we were running out of space on this server when a developer was doing some massive updates and told him to use bulk-logged. Now I read that after doing a bulk operation in bulk recovery model, you need to do a backup - is that correct?
Next, can I have just transaction log backups done hourly in the E:\Backups folder when the full backup is in the E:\WeeklyBackups folder? I imagine there is some kind of tie between full backups and txn backups. What is stopping me from making a backup in one folder doing numerous txn backups then doing another backup in another folder and then someone coming along and trying to apply the txn logs to the 2nd backup - does this concern the LSN in some way?
Finally, Is there a way to see what jobs have failed in SSMS? Looking at these jobs, it seems the only way is to view the history for each one - say it ain't so
.Thanks for helping me out
sam
-
"I imagine there is some kind of tie between full backups and txn backups. What is stopping me from making a backup in one folder doing numerous txn backups then doing another backup in another folder and then someone coming along and trying to apply the txn logs to the 2nd backup - does this concern the LSN in some way? "
I just read in BOL that if a txn log was created before a backup it will not be applied. I guess my real question should have been - why would a transaction log backup not be created. I'm running the maintenance plan and some are created and some are not.
-
If the database is in SIMPLE mode you can't do a log backup.
Log backups ONLY apply to the previous Full Backup. You should be doing EITHER daily or weekly or monthly full backups, not more than one for the same database. You can use different plans for different databases. For example: backup all system databases once a week and do a log backup once a day. Backup all user databases once a day and do the log backups every four hours.
(System dbs don't change much, user dbs do change often).
Yes, you can have different backups go to different locations.
-SQLBill
-
>>You should be doing EITHER daily or weekly or monthly full backups
I'm guessing they wanted to keep a week backup while also doing nightlys as well. We have space restrictions, so we cannot keep a weeks worth of backups on development.
-
Okay. Lets say you do your daily backups at 10 PM, log backups every 4 hours (4 am, 8 am, noon, 4 pm, 8 pm, midnight). And you do a weekly backup on Friday night at 11 PM with a log backup at 1130 PM.
Someone accidentally deletes a table Saturday morning at 5 am. What do you restore? Well, you would have to restore the Friday 11 PM weekly backup, the 1130 PM tlog backup, the midnight log backup and the 4 am log backup. All of the log backups from Friday night at 1100 pm to Saturday night at 8 pm belong to the Weekly backup, not the daily backup.
-SQLBill
-
Thanks a lot Bill.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply