SQL Server 2014 – Alternatives to Network Backup

backupcompressionola-hallengrensql serversql server 2014

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone.

We normally backup to a network share, but we have recently observed that as the databases are growing bigger the time taken is getting longer, which slows down the whole network.

Ola hallengren's script is being used with compression and also splitting the backup files. I am only performing daily "full" backups. The backups are going to the network share EMC isilon drive.

I am never comfortable with EMC DD Boost. The only alternative is to do a local backup and then copy to the same network share.

Is there an efficient way other than the above?

Best Answer

The alternative you mentioned seems to be the best choice.

What you can do is a 2 step process :

  • Take native sql server backups with compression using Ola's backup solution locally.
  • Use Robocopy to do the transfers to a network share. This is decoupled and can run as a Windows scheduled task.

This way, your backups are local and they will be fast. You will need more disk space and obviously redundancy (what if the backup disk fails - you don't want to lose all your backups).

Alternatively, as recommended by Max Vernon, do the Robocopy as a step in the backup job to ensure the robocopy only occurs if the backup is successfully completed, and as soon as possible after the backup is complete. The backup is at the same risk as the data, as long as it stays local.

Also, regularly test your restores since if you cannot restore a backup - what purpose does it serve!

Also, refer to my answer to SQL Backup tuning large databases