August 23, 2010 at 8:39 am
I've never had to create or schedule backups before and I have some questions I haven't been able to find answers to by googling or reading books. I'm hoping I can get some opinions and answers here.
I have a large database that is only updated once a day. I was planning on scheduling full backups once a month with differentials once a week and logs daily. I'm not sure I need to be so complicated. Do I need the differentials in there or should I just do full weekly and logs daily?
If I were to do the original plan, with full happening first Sunday of the month and differential happening every Sunday, will the differential still run on days the full does? Is there a way to keep that from happening since it wouldn't be needed?
The database has 2 very large tables that I've partitioned by year. I've also set them up so information that doesn't change and is older will be on a different file. I've backed that file up and shouldn't have to back it up again, so the full backups shouldn't take all that long. The rest of the database has its own file, making 3 files for the database, Primary, Stable and Current. I'm planning on backing up Primary and Current on the above schedule.
The more I think about it, the more I think I only need Full weekly and Log daily, but I'd still like to know if this makes sense or if I'm missing something. And I'd really like to know about the scheduling problem I mentioned. Is there a way to keep full and differential from backing up at the same time? Does SQL Server know not to do this somehow?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 23, 2010 at 9:06 am
If it's only updated once a day (so no need to worry about point-in-time restore) I wouldn't bother with log backups at all.
Weekly full, daily diff right after the daily update, simple recovery. Schedule them for different times of the day and no worries about clashes.
Is the older data on a read-only filegroup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 9:21 am
I agree with Gail. I would run a log daily just to clear out the changes from that day.
August 23, 2010 at 9:25 am
Steve Jones - Editor (8/23/2010)
I would run a log daily just to clear out the changes from that day.
Unless there's something that mandates full recovery (mirroring/log shipping) I'd just switch it to simple and forget about log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 9:27 am
If you set to simple, then Gail is correct. But if you have it in full recovery for any reason, you need those log backups. Not necessarily for recovery, but just to prevent an out of control log.
August 23, 2010 at 10:14 am
GilaMonster (8/23/2010)
If it's only updated once a day (so no need to worry about point-in-time restore) I wouldn't bother with log backups at all.Weekly full, daily diff right after the daily update, simple recovery. Schedule them for different times of the day and no worries about clashes.
Is the older data on a read-only filegroup?
The older data is not on a read-only filegroup, but the load is a fully automated process and no-one but the process has write permissions to anything in the database. Well, and me. I figure if there are every any changes (which there shouldn't be), I'd just run a full backup again when they're done.
I had it on Bulk-Logging for the initial load and had switched it to Full. I had switched it to Full to take advantage of the Log backups, but as you pointed out, if I go to Differential instead, I can go to Simple mode.
Thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 23, 2010 at 10:15 am
GilaMonster (8/23/2010)
Steve Jones - Editor (8/23/2010)
I would run a log daily just to clear out the changes from that day.Unless there's something that mandates full recovery (mirroring/log shipping) I'd just switch it to simple and forget about log backups.
We may go to log shipping in the future, if we can scrape up a server for the destination. If/When that happens, I'll go back to Full mode.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 23, 2010 at 10:17 am
Steve Jones - Editor (8/23/2010)
If you set to simple, then Gail is correct. But if you have it in full recovery for any reason, you need those log backups. Not necessarily for recovery, but just to prevent an out of control log.
Yeah, I'd forgotten to run my log backup for a week while processing the ETL Load at night and the log went to 160 Gig. I will enjoy having them scheduled nightly.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 23, 2010 at 10:21 am
Just be aware that if you go Simple, you won't be able to do piecemeal restores (restoring filegroups from backups taken at different times), unless those filegroups are read-only. That requires log backups to roll the DB forward to a consistent point in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 10:30 am
GilaMonster (8/23/2010)
Just be aware that if you go Simple, you won't be able to do piecemeal restores (restoring filegroups from backups taken at different times), unless those filegroups are read-only. That requires log backups to roll the DB forward to a consistent point in time.
Well, I'm not taking log backups or differentials of that filegroup anyway. If I want to restore that filegroup, it's all or nothing. It is only more recent data that might have that issue and those I'm taking differentials and periodic fulls of. Are you saying I'll be unable to restore the more recent files if the old one isn't read-only? I didn't see anything on Read Only in BO. I'll go look again.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 23, 2010 at 1:56 pm
If you're taking file/filegroup backups, when you restore you'll need to restore Primary, restore the other files/filegroups, then restore log backups that cover the interval at least between the oldest of the backup that you used and the newest.
Goggle - piecemeal restores.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 2:09 pm
GilaMonster (8/23/2010)
If you're taking file/filegroup backups, when you restore you'll need to restore Primary, restore the other files/filegroups, then restore log backups that cover the interval at least between the oldest of the backup that you used and the newest.Goggle - piecemeal restores.
Great! Thanks for the help. I'll be a-googling this week. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 24, 2010 at 9:56 am
GilaMonster (8/23/2010)
If you're taking file/filegroup backups, when you restore you'll need to restore Primary, restore the other files/filegroups, then restore log backups that cover the interval at least between the oldest of the backup that you used and the newest.Goggle - piecemeal restores.
Thanks again for the assistance. I believe I'll be leaving the database in Full recovery mode and scheduling logbackups to keep the log under control. Having read the msdn on Piecemeal Restores, Full recovery mode seems much more robust and provides more options. As I don't think our system will suffer from keeping it as Full instead of Simple, I'll go with Full. Especially as I hope to implement Log Shipping in the near future.
As for the backups, Full each Sunday with differentials each day.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply