If you try to backup your SQL Server database to a shared folder on a network PC, you're in for a little bit of a surprise. The following command is a valid statement but SQL Server will throw you an ugly exception.
BACKUP DATABASE msdb TO DISK='\\NetworkServer\SharedFolder\msdb.sqlserver.bak'
Cannot open backup device '\\NetworkServer\SharedFolder\msdb.sqlserver.bak'. Operating system error 1326(Logon failure: unknown user name or bad password.). BACKUP DATABASE is terminating abnormally.
The reason for the error is because SQL Server runs under a less-privileged account that doesn't have access to your mapped network. If you look under your Administrative Tools > Services snap-in, you'll find the MSSQL service (or called SQL Server in 2005) running under a different credential such as LocalSystem in 2000 or NetworkService in 2005. It doesn't matter if you executed the command from an Administrator account and you have full access to mapped drives. SQL Server will still call the backup process from its running account.
There are a couple of solutions you can workaround the problem:
- Change the user account running the SQL service to an account that can access network share. Be wary of security issues and potential attacks on that account.
- Backup the database to a local folder or drive and run a separate job to move the files to your network machine.
- Purchase a 3rd party backup software like Red Gate SQL Backup or Lite Speed that knows to backup to a shared drive.
- Use the available API from SQL Server Objects (SMO) and scripts like PowerShell to backup and push data across network.
- Program your own virtual device that knows to read and write raw data from a backup command using the VDI API from Microsoft.
- Change the registry value on the target server adding the sharename you want to backup to. The target machine will not authenticate the connecting account. Remember to restart the target server service for changes to take effect. This will remove the security on the share exposing it to any attacker and is very dangerous for production data. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares
Whatever way you do, make sure you always backup your data to a remote machine or media manually or automatically. Don't trust your disk, people or software.