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

Posted on 2016-07-25
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?
Question by:operationsIT
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
LVL 69

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.
LVL 69

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.

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:

Read this before getting started:

The scripts not only help you set up a backup plan, but they also check integrity and run some other maintenance tasks for you.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 45

Accepted Solution

Kent Olsen earned 500 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?


Author Comment

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?
LVL 49

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.

Author Comment

ID: 41751269
We have storage HA with SIOS Diskkeeper too.  What is best practice?
LVL 49

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.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL-Design Help 12 44
error in oracle form 11 27
Dump data from mysql to xls php 10 24
Database Mail Profiles 1 22
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

733 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