Solved

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

Posted on 2016-07-25
8
76 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
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
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.

 

Author Comment

by:operationsIT
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
We have storage HA with SIOS Diskkeeper too.  What is best practice?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now