Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2008 R2 Backup Best Practices

Posted on 2014-03-12
5
Medium Priority
?
579 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 375 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 375 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 375 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 17

Assisted Solution

by:Gerald Connolly
Gerald Connolly earned 375 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Suggested Courses

581 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