[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

What is Best Practice for backing up SQL - via itself or backup software and how often

Posted on 2016-07-25
8
Medium Priority
?
96 Views
Last Modified: 2016-08-20
Hello EE,

What is the best practice for backing up SQL?
Transaction logs should be backed up often, but hourly, 15 minutes?
What other logs or image levels and how often?
Are there any guidelines?
0
Comment
Question by:operationsIT
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41728348
How often tlogs should be backed up is a business q as much as it is a technical q.  In a truly disastrous situation, how much data from the db are you willing to lose?  That answer partly dictates how often tlogs should be backed up.  There's no single answer, and different dbs on the same server can definitely have different backup schedules (as the most obvious example, the model db tlog, even if in FULL mode, shouldn't need backed up often at all).  You might also back up the tlogs more frequently simply to be sure the log backups don't get too big, and that's solely a function of the transaction volume, which naturally can also vary db to db.

A full or differential db backup is usually done at least once every 24 hours, but archive/history dbs could be one exception, since they may change only once a week or even less often.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41728351
Don't overlook one other critical thing: the log backup should be on a separate RAID set from the main db files.  That way, even if you completely lose either RAID set, you can always still recover the db to a recent point in time.
0
 
LVL 5

Expert Comment

by:Eric C
ID: 41728352
It really depends on what applications you are storing data for, and how mission-critical your data is. To go all out, you can employ real-time replication.

For a small business, you can do nightly FULL backups, and in addition to that, back up the transaction logs every hour.  (Remember - the plan you implement is up to you! But at least put SOMETHING into place quick. Something is better than nothing.)

Here is a popular script that helps you set up all of the backup plans in SQL. You can tweak the settings as per your needs:

https://ola.hallengren.com/

Read this before getting started:
https://ola.hallengren.com/frequently-asked-questions.html

The scripts not only help you set up a backup plan, but they also check integrity and run some other maintenance tasks for you.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 41728353
Hi Operations,

Transaction log backup is tied directly to your tolerance for data loss.  If the logs are not on a raided device, consider backing them up more frequently.

There are a number of things that can impact your backup needs.  Are you in a true 24/7 environment?  Is the database being replicated to another server?

Kent
1
 

Author Comment

by:operationsIT
ID: 41729712
We have Windows and SQL clustering with SIOS Data Keeper so have HA and are an 8x5 shop.

I will ask the business but as other systems they want to know what I think.
I am thinking based on the HA we can do hourly, but wanted to get what others are doing as guidance?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41742688
I am thinking based on the HA we can do hourly, but wanted to get what others are doing as guidance?
Your HA is SQL cluster so storage is a single point of failure. Your business hours is 8 hours only during weekdays but how critical is it to loose one hour of data? Is that the question that you need to ask them.
0
 

Author Comment

by:operationsIT
ID: 41751269
We have storage HA with SIOS Diskkeeper too.  What is best practice?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41751932
Your question is about Backups and not High Availability (HA) so the storage type doesn't matter for this subject.
Above comments need to be answered so we can define a backup strategy for your case.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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