Solved

SQL Server 2008 R2 Backup Best Practices

Posted on 2014-03-12
5
543 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to fix error ""Failed to validate the vCentre certificate. Either install or verify the certificate by using the vSphere Data Protection Configuration utility" when you are trying to connect to VDP instance from Vcenter.
By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

832 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