I use the Ola Hallengren’s maintenance solution for my SQL Servers instances.
It happened to run the maintenance solution so that it writes to a separate server using FTP protocol.
An ftp user and a network share was given to me to use it for the backup files to store on the server. The SQL agent service account doesn't have any privileges on the network share.
Running the backup command
EXECUTE [master].[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES',
@Directory = '\\xxx.xxx.xxx.xxx\backups\SQL_BACKUP_CLUSTER_F1',
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y'
produced the following error:
Msg 50000, Level 16, State 1, Server SERVER\INSTANCE, Procedure DatabaseBackup, Line 384
The directory \\xxx.xxx.xxx.xx\backups\SQL_BACKUP_CLUSTER_F1 does not exist.
Msg 50000, Level 16, State 1, Server SERVER\INSTANCE, Procedure DatabaseBackup, Line 611
The documentation is available at http://ola.hallengren.com/sql-server-backup.html.
After searching the net for the issue I found some links:
I found this link - http://serverfault.com/questions/635226/ola-hallengrens-sql-backup-to-network-location-not-working that is experiencing the same issue.
But, it’s not about
The network location that doesn't exist.
The privileges of the ftp user on the location that are not complete.
And this link - http://www.sqlservercentral.com/Forums/Topic1552887-2799-1.aspx with different thought about the same.
It’s not about the
Service account with a length of more than 20 characters.
I'm sure there could be some more reasons for the failure of the command.
Solution
As this is a situation when the sql server agent account doesn't have permission on the network share, the network location must be mapped to a drive. It could be done by using the XP_CMDSHELL command in SQL server.
Make sure XP_CMDSHELL is enabled on your SQL Server instance. You can do it with the following command in SSMS:
EXEC sys.sp_configure 'xp_cmdshell',1
reconfigure
Step 1. Map the network share to drive X (could be any letter):
EXEC XP_CMDSHELL 'net use X: \\ xxx.xxx.xxx.xxx \ backups\SQL_BACKUP_CLUSTER_F1 FtpPassword /user:FtpUser'
Step 2. Verify the drive mapping:
EXEC XP_CMDSHELL 'Dir X:'
Step 3. Run the Backup procedure
EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES',
@Directory = '\\xxx.xxx.xxx.xxx\backups\SQL_BACKUP_CLUSTER_F1',
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y'
Done.
