Solved

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

Posted on 2016-07-25
8
84 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: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 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.
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 45

Accepted Solution

by:
Kdo 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?

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 47

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 47

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

786 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