Backing up SQL databases regularly is must. We have already covered ways to can easily backup all your SQL server databases to a local hard drive, but this does not protect against drive and/or system failure. As an extra layer of protection against this type of disaster, you can copy or directly create your backups on a network share.
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:
SET LocalFolder=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup
SqlCmd -E -Q "Backup Database MyDB To Disk='%LocalFolder%MyDB.bak'"
XCopy "%LocalFolder%MyDB.bak" "\192.168.16.55BackupDatabases" /Z /V
DEL "%LocalFolder%MyDB.bak"
This script does the following (line by line):
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:
SqlCmd -E -Q "Backup Database MyDB To Disk='\192.168.16.55BackupDatabasesMyDB.bak'"
You will mostly likely get an error along the lines of:
Msg 3201, Level 16, State 1, Server JF, Line 1
Cannot open backup device '\192.168.16.55BackupDatabasesMyDB.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server JF, Line 1
BACKUP DATABASE is terminating abnormally.
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.
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:
SqlCmd -E -Q "Backup Database MyDB To Disk='\192.168.16.55BackupDatabasesMyDB.bak'"
You should see a success message:
Processed 152 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 2 pages for database 'MyDB', file 'MyDB_log' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.503 seconds (2.493 MB/sec).
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:
Msg 3201, Level 16, State 1, Server JF, Line 1
Cannot open backup device '\192.168.16.55BackupDatabasesMyDB.bak'. Operating system error 1326(Logon failure: unknown user name or bad password.).
Msg 3013, Level 16, State 1, Server JF, Line 1
BACKUP DATABASE is terminating abnormally.
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.