Solved

SQL Server 2008 R2 Backup Best Practices

Posted on 2014-03-12
5
527 Views
Last Modified: 2014-05-14
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?
0
Comment
Question by:AFRITSupport
5 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 125 total points
ID: 39925670
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 125 total points
ID: 39927768
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 39930768
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
 
LVL 16

Assisted Solution

by:Gerald Connolly
Gerald Connolly earned 125 total points
ID: 39930823
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40065120
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now