SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ola Hallengren’s solution on a network share using FTP

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:
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.

SQL Mighty

I'm a passionate SQL Server professional with 10+ years of experience with databases. Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.

Comments

Leave a comment on the original post [www.sqlmighty.com, opens in a new window]

Loading comments...