Solved

online and offline SQL backup basics

Posted on 2014-11-17
7
187 Views
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?
0
Comment
Question by:pma111
7 Comments
 
LVL 45

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?
0
 
LVL 3

Author Comment

by:pma111
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?
0
 
LVL 10

Assisted Solution

by:JEaston
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).
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 3

Author Comment

by:pma111
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?
0
 
LVL 45

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.
0
 
LVL 32

Assisted Solution

by:ste5an
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..
0
 
LVL 69

Accepted Solution

by:
ScottPletcher 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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

12 Experts available now in Live!

Get 1:1 Help Now