UNC path and backups over the network

sql-lover

Hi,

I am having a very interesting debate with one of my IT resources. Due the backup storage solution he previously bought, I've been asked to drop backup files over the network.

Now, having offsite backups (and rotate or keep those in a removable media) is perfectly fine, but the backup pipe does not look enough to me. I am actually having some issues due SAN and network bandwidth. So, long story short, I am trying to re-design the existing backup jobs so I can use that share.

I've been doing some testing, and it looks like RedGate 7.0 "does not like" when I map the share drive, let's say using "Z" drive, and specify that in the backup job. Instead, if I use the UNC path, like \\server\whatever folder, the backup jobs seems to work without an issue.

Here are my two questions, or actually two questions and requesting one technical advice ;-)

1. Can I specify on RedGate, step #3 of scheduling backups, a UNC or regular Windows mapping, on the Folder field? Or a UNC path is mandatory>

2. If I can specify UNC or regular drive letter paths when using a folder, what could be the problem with the Windows mapping, as permissions look to be ok.

The technical advice or question would be ... should I use the "copy backup to network" option or schedule a total separate job for the offsite backups? I am trying to reduce network and IO stress on the SAN Cluster and network.

And should I stick to the UNC or create a Windows mapping? I know RedGate is Cluster aware, and assuming the UNC does not change, it should be more stable than a Windows mapping with a given letter, am I right about that?

Eddie D

Hi sql-lover,

Thank you for your post into the forum.

SQL Backup does not support mapped drives, so you will need to specify an UNC path to the network share.

As you may know, the SQL Backup processes are controlled by the SQL Backup Agent Service. The SQL Backup Agent service is a Windows service which is used to perform the SQL Backup backup and restore operations through the graphical user interface or the extended stored procedure.

When a Windows account runs as a service it does not behave in the same manner as a normal logged-in user account, with regards to mapped drives, in that it does not make use of mapped drives. Hence why SQL Backup does not support mapped drives.

So to answer your questions:

1. Can I specify on RedGate, step #3 of scheduling backups, a UNC or regular Windows mapping, on the Folder field? Or a UNC path is mandatory>

2. If I can specify UNC or regular drive letter paths when using a folder, what could be the problem with the Windows mapping, as permissions look to be ok.

You need to set an UNC path to the share and the account you give the SQL Backup Agent service will require security permissions to the share.

The technical advice or question would be ... should I use the "copy backup to network" option or schedule a total separate job for the offsite backups? I am trying to reduce network and IO stress on the SAN Cluster and network.

As you have a requirement to reduce network and IO stress, then my recommendation is yes to select the option to "copy backup to network", if you have sufficient free disk space to create the local copy. Although SQL Backup is capable of backing up directly to a network share, it will add an overhead to the process and it is not recommended practice. However there are many SQL Backup users who back directly across their networks to a share. I recommend that you evaluate both options in a test environment before applying to your production machines.

There is an unsupported stand-alone copy tool that can be made available to you. If you wish to evaluate the stand-alone copy tool, please e-mail the Red Gate Support team, support@red-gate.com and request the stand-alone SQL Backup Copy tool.

Many Thanks
Eddie

sql-lover

Sorry for my late reply.

I wanted to confirm I used UNC path. And appreciate the answer and follow up. Hope my thread helps someone in the future.

Thanks again!