SQL Server 2008 R2 Backup Best Practices

I am looking for everyones opinions on this topic, currently we are using Symance Backup Exec 12 with SQL Agents to back up our databases. We do a disk to disk to tape topology using Full Backups. When restoring we can only restore from tape which is not the best solution.

In the past I have ran maintenance plans and backed up actual SQL backups that way along with using a SQL agent.  What does everyone reccomend for backup performance and ease of restore.

Should both be implented? Would it be over kill?
AFRITSupportAsked:
Who is Participating?
 
AnujConnect With a Mentor SQL Server DBACommented:
Yes, Restoring from tape is slow .  The best way is backup your database to Disk first then copy to tape Instead of moving. Always keep a latest copy of backup in the disk which helps you fast restore in the event of disaster rather depending on tape. So when you take a latest backup delete the old and keep the latest in the disk.

Another good practice is to test the backup file by performing a physical restore on a test machine (weekly or monthly), this way you will be confident that you are good with the backups. Also, conduct some disaster drills and make sure that you meet the RTO and RPO.
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

It depends.

It depends on your point of view.

If you are a system administrator, then you are concerned with recovering the entire machine. Suggested best way to do this is with something like Symantec and the Agents, and just backup everything.

OTOH, if you are a DBA, and you may want to refresh test or whatever, then a full backup with transaction log backup (and diff backup if needed) is preferred.

I use Ola Hallengren's solution (http://ola.hallengren.com/), and schedule overnight full backup and hourly transaction log backup.

I like to keep as much on local disk as I can, which with Ola's solution defaults is 2 full backups - I often can't get more disk space than that.

More common than an entire machine going bust, is a colleague doing the wrong thing, and having to restore the database to 5 mins ago.

I agree with anujnb - practice!

HTH
  David
0
 
Anthony PerkinsConnect With a Mentor Commented:
Don't focus on your backup policy, it is irrelevant.

What you need to be asking yourself is:
What is your restore policy?
How frequently do you test your backups?
Can you restore them within the time frame required?
Do you need to do a point-in-time restore?  Can you do one?
What is your Recovery Model?  If you are using Full Recovery Model, what is the situation with your Transaction Logs?
0
 
Gerald ConnollyConnect With a Mentor Commented:
As Anthony said, the thing to concentrate on is Recovery.

You need to work out how much downtime your organisation can cope with if a disaster occurs and from that work out how you are going to achieve it. Work-up some disaster scenarios and see how you can cope - start with the easy ones like losing a disk, DB corruption up to a building fire etc etc
0
 
Anthony PerkinsCommented:
I am sorry we did not provide you Excellent help, unfortunately in order to do that we need your feedback to what you have to admit was a very broad question and you provided none whatsoever.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.