Solved

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

Posted on 2016-07-25
8
80 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
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:ScottPletcher
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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 46

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 46

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

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

939 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

9 Experts available now in Live!

Get 1:1 Help Now