online and offline SQL backup basics

Posted on 2014-11-17
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 49

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 10

Assisted Solution

JEaston earned 125 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).
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 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 33

Assisted Solution

ste5an earned 125 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 125 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

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.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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