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
LVL 48

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).
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.


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 48

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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