Verify my current backup strategy n suggest

Question

Verify my current backup strategy n suggest

Sudiendra

SSCommitted

Points: 1839
More actions
April 7, 2009 at 3:41 pm

#202658

T-Log backup - Every 15 Minutes and Retention Period - 2 Days
Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)
Full Backup - Every week and Retention Period - 1 week (Till next full backup)
This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...
two databases are highly transactional
Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..
Thanks,
Sudhie.

Viewing 15 posts - 1 through 15 (of 25 total)

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

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

Sudiendra (4/7/2009)
Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)
Full Backup - Every week and Retention Period - 1 week (Till next full backup)

What happens if one of your backups is damaged and unusable?

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

Sudiendra SSCommitted Points: 1839 More actions · Answer 2

valid point...Will increase in retention period override this Problem?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

Sudiendra (4/7/2009)
T-Log backup - Every 15 Minutes and Retention Period - 2 Days
Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)
Full Backup - Every week and Retention Period - 1 week (Till next full backup)
This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...
two databases are highly transactional
Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..
Thanks,
Sudhie.

And also what is your clients business model WRT to data loss? and also are you offloading it to a tape?

and include to verify your backups

Backup Mantra:

Backups, Early backups, Frequent backups and verified backups

Sudiendra SSCommitted Points: 1839 More actions · Answer 4

Krishna - We have SAN replication enabled. Although i know nothing much about that..all drives are replicated to another server..

Now considering SAN replication, will this strategy work?

BTW, Backup drive is not replicated

Thanks,

Sudhie.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

Sudiendra (4/7/2009)
Krishna - We have SAN replication enabled. Although i know nothing much about that..all drives are replicated to another server..
Now considering SAN replication, will this strategy work?
BTW, Backup drive is not replicated
Thanks,
Sudhie.

Sorry Sudiendra, I don't have much idea about SAN drives. Lets wait for someone to answer this for us.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Sudiendra (4/7/2009)
T-Log backup - Every 15 Minutes and Retention Period - 2 Days
Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)
Full Backup - Every week and Retention Period - 1 week (Till next full backup)
This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...
two databases are highly transactional
Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..
Thanks,
Sudhie.

You didn't mention how often you are backing up the disk backups to tape. No backup strategy is complete without taking tape backups into account.

I prefer to do a full backup every day, if possible. I usually keep the transaction log backups at least three days. Since I have a full backup every day, I usually do not do a differential backup. I expect that all backups on disk will be backed up to tape every day.

Sudiendra SSCommitted Points: 1839 More actions · Answer 7

Michael Valentine Jones (4/7/2009)

We have San replication, so we switched off tape backups..

Thanks,

Sudhie.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Michael Valentine Jones (4/7/2009)
Since I have a full backup every day, I usually do not do a differential backup.

Michael,

so If a disaster happens would you restore the Full and all the subsequent Transaction log backups? but that would be many Tlog files to backup, say if you are taking for every 15mins.

Incorporating differential backups into the plan will increase the available recovery points between full backups, isn't it?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

Yes, you will need to restore the transaction log backups created after your last full backup up until the point of time you want to recover to.

It is no different if you have differential backups, except that you only need to apply the transaction log backups created after the last differential backups.

Differential backups do not increase your available recovery points, since you can recover to any point in time with transaction log backups.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Sudiendra (4/7/2009)
Michael Valentine Jones (4/7/2009)
We have San replication, so we switched off tape backups..
Thanks,
Sudhie.

That is an extermely bad idea. If your database becomes corrupt, you will have nothing to recover from, because the corruption will be replicated by the SAN also. You should turn your tape backups back on immediately.

Think that can't happen? Read the links below about a company that used your backup strategy and went out of business because of it.

Mirroring is Not a Backup Solution

http://hardware.slashdot.org/article.pl?sid=09%2F01%2F02%2F1546214&from=rss

"Journalspace.com have fallen, never to return. The post on their site describes how their entire database was overwritten either through some inconceivable OS or application bug, or more likely a malicious act. Regardless of how the data was lost, their undoing appears to have been that they treated the drive mirroring as a backup and have now paid the ultimate price for not having point in time backups of the data that was their business."

Why back up? Ask JournalSpace

http://www.brentozar.com/archive/2009/01/why-back-up-ask-journalspace/

Sudiendra SSCommitted Points: 1839 More actions · Answer 11

I agree with that article, But for some jobs we are suggested not to run backups in parallel. So we are not finding good time to accommodate this long running backups.

In this regard, we have chosen this backup strategy. I have decided to increase retention period now from our discussion.

I am still talking to admins on backup strategy for the drive which holds backups.

Sudiendra SSCommitted Points: 1839 More actions · Answer 12

Thanks for quick turn around every one, keep them coming. I will post an article on SAN replication if i get it.

BTW, this is my first longest thread in ssc 😛

Thanks,

Sudhie.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Any backup strategy that does not include getting your database backups to tape is incomplete and a disaster waiting to happen.

It doesn't have to be database corruption. It could just be data that is deleted from the database or updated by accident and is not noticed until after all your backups on disk are gone.

I have had to recover data from tapes that were two years old.

Don't become the latest post on this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

maechismo_8514 SSCoach Points: 17510 More actions · Answer 14

Michael Valentine Jones (4/7/2009)
Don't become the latest post on this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

OMGGGG!!!:w00t: That's the scariest thread I have ever seen, I am going to advise the same to rest of my DBA's to look at the thread 🙂 or even my management and show them how important it could be though I always stress more on our DR strategy always explaining to them how important they are. Now this wil give a better understanding to them not understanding but consequences. Thanks michael