FTP script from one SQL server to SQL another

Question

FTP script from one SQL server to SQL another

Henry-309942

SSC Eights!

Points: 822
More actions
February 20, 2008 at 4:46 pm

#154142

Can someone help me with a way to FTP backup file and transaction logs files from sql server 2005 to another sql server 2005. This is the only option that can be used for transfering the data for restore on the secondary server due to some issues.
A sql script will be help or a script that uses ssis FTP process.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 1

You touched on the solution yourself, create an SSIS package with an FTP task that accomplishes what you want (grabbing .bak and .trn files from Server A and FTP-ing them to Server B). Then schedule that SSIS package as a SQL Agent job.

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Henry-309942 SSC Eights! Points: 822 More actions · Answer 2

Thanks for your reply. I have not used ssis before and not sure if the tool will build the script for the FTP part if not can you help. Thanks in advance, an example of a script that can accomplish the task will help.

Gaurav Gosain-395499 Right there with Babe Points: 770 More actions · Answer 3

Hi,

Create the SP with below script and schedule it to run after every backup:

-- Create the table that has the FTP commands to be ran in batch

CREATE TABLE ##tempCmdTbName (Command varchar(255) NULL)

-- vr

INSERT into ##tempCmdTbName VALUES('open )

INSERT into ##tempCmdTbName VALUES('user ')

-- Using Binary mode

INSERT into ##tempCmdTbName VALUES('bin')

-- Local DIR from where you need to copy

INSERT into ##tempCmdTbName VALUES('lcd ')

-- to create destination directory

INSERT into ##tempCmdTbName VALUES('cd ')

INSERT Into ##tempCmdTbName Values('mkdir ')

-- Change the local directory - destination directory

INSERT into ##tempCmdTbName VALUES('cd ')

-- Retrieving the file using the GET command

INSERT into ##tempCmdTbName VALUES('Put ')

INSERT into ##tempCmdTbName VALUES('Bye')

select * from ##tempCmdTbName

--Prepare to BCP the commands into a text file

SELECT @sql = 'bcp ##tempCmdTbName out C:\FTPCmdFile.txt -c -SMaui'

CREATE TABLE #tmpOutput (CommandOutput varchar(1000) NULL)

Insert #tmpOutput -- capture the output so that it would not be picked up by the calling routing

EXEC master..xp_cmdshell @sql

-- FTP for the file list

SELECT @sql = 'FTP -n -v -s:C:\FTPCmdFile.txt'

Insert #tmpOutput -- capture the output so that it would not be picked up by the calling routing

EXEC master..xp_cmdshell @sql

Try this out and let me know if you face any issue. This is the simplest way to do this.

Regards

Gaurav

Thanks,
GG;-)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

Excellent Guarave!

That looks like a strategy to avoid SSIS, and its FTP task inability (as near as I can tell) to work with sources/destinations (e.g. IBM mainframes) that do not use a slash in the path name.

For downloading, I currently do about the same thing from VBA in Access (build a FTP command file and then execute ftp) but have been stuck trying to migrate that activity to SSIS. (A case of If your only tool is a hammer....)

I hope it helps the original poster also.

Henry-309942 SSC Eights! Points: 822 More actions · Answer 5

I hope I am nothing asking a stupid question. But where in the script do I put the source and destination directory of the files to be copied

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

My guess is that the actual paths are to follow the lcd and cd commands, while the file name would follow the put command. (Note that there are some optional commands, like mkdir, that you may want to remove. I'd inspect the generated FTP script file before I ran it.)

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 7

Henry, I believe your FTP commands would be in the .txt file referenced here in the script:

[font="System"]-- FTP for the file list

SELECT @sql = 'FTP -n -v -s:C:\FTPCmdFile.txt' [/font]

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford