• Adam Thurgar

Network backups and restores

I like having a local backup of my databases. It doesn't matter if this is physical disks or a SAN. I just like having them local. I find that backups and restores are quicker.

This becomes very important if you have strict Recovery Time Objectives (RTO) and Maximum Allowable Outages (MAO).

When I see SQL Server databases being backed up to NAS devices or other network storage devices, I wonder how quickly can the backup be recovered. Remember the extra time it may take to restore across the network or even just copy the backup to local storage for a restore.

Backing up and restoring across a network can be slower if you are just using the same NIC that is being used for all other network communication on your SQL Server. How many people have a dedicated backup network to take this load off the general network? Could your backups be impacting your application performance (or general SQL Server performance) when they run?

I dread looking at the SQL Server error log and finding something like the following

BackupIoRequest::ReportIoError: write failure on backup device

Operating system error 2(failed to retrieve text for this error. Reason: 15100).

Error: 3041, Severity: 16, State: 1.

Is my backup still good? Only one way to tell, you need to do a test restore, do you have the disk space to do this restore?

If you need your backups on a network storage device, you could always have a copy step and the end of your backup job.

I still like having a local backup of my databases.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t