Transactional Log backup with frequency of 1 month

Question

Transactional Log backup with frequency of 1 month

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
January 6, 2015 at 12:09 pm

#293449

HI ,
I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .
and i am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .
So should i need to take any other precautions or concerns with this approach.?
Thanks in advance ...

Viewing 10 posts - 1 through 9 (of 9 total)

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

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 1

I would personally be very nervous about a strategy that includes only 1 full backup in a 5 year period - but that is likely because I work in an environment where things change far more frequently than once per month.

I would be more likely to use the Simple Recovery Model and do a full backup immediately after the changes are made each month. I would not like to find myself having to restore the original full backup and 4 years + worth of transactional backups to get back to last month's data.

stevenb 14609 SSCommitted Points: 1832 More actions · Answer 2

It also depends on where you're storing your backups.

If you backup to tape, you can run into issues with misplacing a tape from 5 years ago, or having a tape not work right after sitting idle for 5 years.

Even if you are storing things on disk, 5 years leaves you open to a lot of exposure. All it takes to ruin all 5 years worth of backups is one problem with your initial full backup. Without that initial full backup, everything else is useless. That's a single point of failure that I would not be comfortable with.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

One full backup every 5 years and one transaction log backup every month???

I do hope that client's data loss allowance in the case of a disaster is 'all of it'.

Maybe instead simple recovery and a full backup once a month?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

RamSteve (1/6/2015)
HI ,
...
So should i need to take any other precautions or concerns with this approach.?
...

Yes, update your resume every 2 weeks!

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Alvin Ramard (1/6/2015)
RamSteve (1/6/2015)
HI ,
...
So should i need to take any other precautions or concerns with this approach.?
...
Yes, update your resume every 2 weeks!

Hahahahahaha :hehe: 😀

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

GilaMonster (1/6/2015)
Alvin Ramard (1/6/2015)
RamSteve (1/6/2015)
HI ,
...
So should i need to take any other precautions or concerns with this approach.?
...
Yes, update your resume every 2 weeks!
Hahahahahaha :hehe: 😀

Differential update should be sufficient. 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 7

What's the concern with taking more frequent full backups? Or maybe differentials?

The thing is, it's not just a question of backing up, it's also a question of restoring. When you go to restore, the transaction logs are essentially replayed. Depending on the size of those logs from your monthly load, that could make for a very long recovery if, let's assume the worst, you had to restore eleven logs (again, depends on the volume).

I really do think a safer bet would be monthly full backups or maybe an annual full and then monthly differentials (although that still gives me the heebie-jeebies).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 8

Still all of those scenarios make me nervous. What happens when you go to restore and this month's file is corrupt? Nope, I don't like it one bit. At a very minimum I would want to see 2 fulls a month just in case one of them failed.

You need to have more on your side than a single file that could have so many things happen to it.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 9

Yeah, Sean makes an excellent point.

Remember, you're not building a backup plan. You're building a recovery plan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning