Solved

SQL Server 2008 R2 Backup Best Practices

Posted on 2014-03-12
5
550 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 17

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
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…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

738 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