2

We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need to take backup tapes home. We would like to be able to restore back up to 28 days. It seems that all of the online backup companies will only do sql full backup or differential backups not incremental.

My problem is, if differential backups will look at the last full backup date and only send over changes since then, our on site backup will mess this up.E.g If i do a full online backup on Sunday. Then we do an onsite backup at 6pm every day, at 9pm on Thursday when we do the online backup, it will only find changes from 6pm on Thu -9pm Thu. I want it to find changes from the last full online backup i.e. sunday up to 9pm on Thursday.

Does anyone have any solutions or recommendations, this must be a common issue.

  • Microsoft SQL server 2008 R2 – user79385 Oct 29 '15 at 21:53
1

Assuming you are doing native backups to disk, and then Veritas backs up to tape:

  1. Do your onsite backups to disk. Setup blob storage on Azure (or S3 with Amazon) and then script copying that file to that storage. You would then have Veritas backup that file as well from the local disk to tape.
  2. If you happen to be on SQL Server 2012 or higher you can backup directly to Azure storage. The Azure storage, you can select geo-redundant service and this will allow your backup to be duplicated between Microsoft data centers. You could consider actually dropping doing backups to Veritas with this option, but an extra copy doesn't hurt to have locally.

Response to your comment

We do backup to external disk. We no longer use tapes. I want to do a full onsite backup each day so if something went wrong then i could restore the full database back quickly. I just also want an offsite copy. Is this possible? We are using sql server 2008 R2. Does azure support incremental/differential backups with sql 2014 or will it only keep one copy which is the latest?

Think of Azure storage as an external disk, it just sits in Microsoft Data Centers. Copying your backups to Azure provides you the ability if your building gets wiped away, bring up another server with Internet access and retrieve the last backup you copied up.

Azure storage (blog storage) allows any file type, the only limit is one single file cannot exceed 1TB in size. If in the future your backups grow to that size, you will just need to split the backup into multiple files to keep it under that limit.

SQL Server 2012 and higher allow backing directly to Azure via BackupToURL. You can utilize Microsoft® SQL Server® Backup to Microsoft Azure®Tool to do similar action across any version of SQL Server. There is a write up done for using this tool here.

Azure does not delete or clear out old backup files for you, so in your design of the backup solution using this service you will need to also deal with cleaning files out if needed.

  • 1
    We do backup to external disk. We no longer use tapes. I want to do a full onsite backup each day so if something went wrong then i could restore the full database back quickly. I just also want an offsite copy. Is this possible? We are using sql server 2008 R2. Does azure support incremental/differential backups with sql 2014 or will it only keep one copy which is the latest? – user79385 Oct 29 '15 at 23:06
  • "... if your building gets wiped away, bring up another server..." I think it's funny (in general, not this answer specifically) that discussions of geo-replication never seems to take into account that if you require a geo-replicated copy of data there's a really good chance that someone else will be doing the restore! (At least for on-prem solutions) – Dave Oct 30 '15 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.