online and offline SQL backup basics

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
pma111Author Commented:
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?
John EastonDirectorCommented:
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).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

pma111Author Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
ste5anSenior DeveloperCommented:
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..
Scott PletcherSenior DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.