I have this below SQL Code which can download .bak file from FTP but sometimes it got failed due to connection problem. I want to add a logic if the connection got failed then retry again until download and then go to the next step
The SQL job has the following steps..
exec xp_cmdshell 'cd C:\Program Files (x86)\WinSCP && winscp.com /command "open ftpes://Secure%xxx.FTPS:email@example.com -hostkey=*" "cd /database" "lcd U:\MSSQLI2" "get *.bak"';
@recipients = 'firstname.lastname@example.org',
@body = 'Refresh will be start after 5 mins, please complete your current work and disconnect, Thanks!!!',
@subject = 'IND Database Refresh daily'
WAITFOR DELAY '00:01:00'
declare @sqlstr nvarchar(1024)
declare @dt char(8)
select @dt=convert( char(8),getdate(),112)
select @sqlstr =
'restore headeronly from disk = ''U:\MSSQLI2\IND_157GIC034_backup_'+@dt+'.bak'';'