online and offline SQL backup basics

Posted on 2014-11-17
Medium Priority
Last Modified: 2014-12-04
I am after an independent view of the following scenarios. There are a number of database (SQL Server) backups being written to the same drives as their live production database. Some of these servers are virtual (SAN storage) and others are physical (local storage).

Our DBA made the following comments:

This is more of a risk on physical servers (local storage) than virtual servers (SAN storage) – can anyone explain why?

They also claimed – if they are backing up the data to tape each night, what’s the real risk? I.e. if the drive housing both the data and backups did corrupt or did fail, as long as they have a backup on tape to which they can restore the backup of the data after the hardware/corruption fault has been rectified – is there really any risk?

What’s your view?
Question by:pma111
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
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40446886
This is more of a risk on physical servers (local storage) than virtual servers (SAN storage) – can anyone explain why?
If you loose your physical server you may loose your backups as well. The golden rule for backups is for not store the backups in the same place that your databases are.
For example, where do you store the copy of your house keys? If you let them inside of your house would be useless if you lost the original keys, right?

Author Comment

ID: 40446902
But if the data is first backed up to local drives, and then backed up to tape - whats the risk? As even if the drives with the live database and backups fail, then you can restore the data from tape?
LVL 11

Assisted Solution

by:John Easton
John Easton earned 500 total points
ID: 40446905
Backups can serve several purposes.  Backups to disk are useful if the SQL Server fails (i.e. not the hardware but software - for example database corruption etc.)

However, this is not suitable for disaster recovery.  Backup to tape does address this, but you have to ask how safe you want the data.  If tapes are kept on site then a fire would destroy both the server and backup.  If you want more security then consider have a remote backup site (or use third party offsite backups).
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 40446910
So all in all if you backup to tape, even if the same drive is housing both databases and online backups, there isnt much risk as you can restore from tape?
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40446912
But if the data is first backed up to local drives, and then backed up to tape - whats the risk?
The bigger risk is if during that gap (while backups aren't transferred to tape) something bad happens with the local drives.

As even if the drives with the live database and backups fail, then you can restore the data from tape?
If they already on tape, yes.
LVL 35

Assisted Solution

ste5an earned 500 total points
ID: 40446928
The short answer: He's right, the risk is lower. But is it significantly lower?

You SAN needs enough stand-by drives. It also needs  fail-over/mirror partner in a different fire protection area. Depending on the importance you also need a mirror in a different seismic zone.
That's the reason why tape backups are still used: They are not so fragile in the event of fire or earthquakes.

Making backups to the same drive/volume where your database files are stored are not worth to be called backups. When the drive/volume fails, you also loose your backups. I've seen people having there databases on one volume and the backup on a second volumn in the SAN/NAS environment. But both volumes were on the same spindles..
LVL 69

Accepted Solution

Scott Pletcher earned 500 total points
ID: 40450827
Let's backup one step, then go forward again, and we'll have a solution that hopefully satisfies both sides (recoverability with an easier backup process).

One absolute rule for recoverability is that the data files and log file for any single, given db must be on separate physical drives (drive sets).  Otherwise, a single drive set failure loses everything, and the data is only recoverable to the last external backup.

Given that, you can store db backup files on the log drive set, but not on the data drive set.  With that approach, you can still fully recover from a failure of either drive set.  The odds of losing two reasonably managed drive sets at the same time is virtually nil barring a true physical disaster.  As it turns out, this is rather convenient anyway, because the log drives are typically RAID10 (or RAID 1) (rather than RAID5, often used for data-only), which is much better for backups.

Note that, if you need to, you can store data and log files, and thus backup files, together on the same drive set, as long as for any single, given db the data files and log/backup files are on different drive sets.  This is not commonly accepted or done, but it can actually help balance I/O and is fine as long as the primary rule is followed.

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

800 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