Backup Locally and then Copy to the Network Share
The preferred and most direct way to accomplish this task is simply to create a local backup of a database and then copy the respective backup file to a network share. You can do this by creating a batch script which looks like this:
This script does the following (line by line):
Sets a variable to the local SQL backup directory. Creates a SQL backup of MyDB (using Windows Authentication) to the local SQL backup directory. Copies the local backup file to a network share. Deletes the local backup file.
Again, this is the preferred method because it works out of the box and likelihood of a backup failure is minimal since the backup is created on a local disk. However, if you do not have a enough disk space to store local copies of backup files this action will fail. In this event, you will need to add additional disk space or backup directly to a network share.
Backup Directly to a Network Share
Typically, when you try to create a backup directly to a network share using a command such as:
You will mostly likely get an error along the lines of:
This error occurs despite the fact that you ran the SQL backup command using Windows Authentication (the -E switch) and the Windows account as the ability to access and copy files to the share through Windows Explorer.
The reason this action fails is because the SQL command is executed within the bounds of the account the SQL Server service is running as. When you view the Services list on your computer, most likely you will see the SQL Server service running as (the Log On As column) either Local System or Network Service which are system accounts which have no network access.
On our system the backup to a network share command fails because we have the SQL Server service running as Local System which, again, cannot get to any network resources.
In order to allow SQL to backup directly to a network share, we have to run the SQL Server service as a local account which does have access to network resources.
Edit the properties of the SQL Server service and on the Log On tab, configure the service to run as an alternate account which has network access rights.
When you click OK, you will get a prompt that the settings will not take effect until the service is restarted.
Restart the service.
The services list should now show the SQL Server service is running as the account you configured.
Now when you run the command to backup directly to a network share:
You should see a success message:
With the backup file now in the network share directory:
Network Share Considerations
It is important to note that the backup command expects to be able to connect directly to the network share without being prompted for credentials. The account you have configured the SQL Server service to run as must have a trusted connection with the network share where the respective credentials allow access, otherwise an error like this may occur:
This error indicates that the account’s user name and password were not accepted by the network share and the command failed.
Another issue to keep in mind is the backup is performed directly to a network resource, so any hiccups in the network connection could cause your backup to fail. For this reason, you should only backup to network locations which are stable (i.e. probably not a VPN).
Security Implications
As mentioned earlier, using the method where you backup locally and then copy to a network share is preferred as it allows you to run the SQL Service as an account with local system access only.
By running the service as an alternate account you open the door to potential security issues. For example, a malicious SQL script could execute under the alternate account and attack network resources. Additionally, any changes to respective account (password changes/expirations or deletion/disabling of the account) will cause the SQL Server service to fail to start.
It is important to keep these points in mind if you do run your SQL Server instance using an alternate account. While these are not show stoppers if proper precautions are taken, you should consider adding additional hard drive space and then implement the local backup and copy so you can run the SQL service using a local account.
