Scheduled ftp task on server

Question

I need to write a batch file that will run every day (Win server 2003) and will upload a sql-server backup file to a ftp account (backup)?

I have the host, username and password and the path to the folder that contains the file the file name is new every day so I don't know it.

The folder contains just one file.

Answer 1

You will need to get the SQL job to create a fixed name file, we can rename it later.

Step 1 Machine A runs the SQL backup job, scheduled with a SQL job.

Step 2 Once the SQL backup has completed Machine A runs a scheduled task that executes FTP_upload.bat to upload the SQL .bak file to FTP service B.

Contents of FTP_upload.bat

ftp -s:ftptransfer.cfg

Contents of ftptransfer.cfg

Open <ipAddress of FTP service>
<username>
<password>
Binary
Put <full path and name of .bak file> <remote path at location>
quit

Modify the scheduled task times accordingly to fit with the backup and transfer times for your machines / file sizes

Step 3 Once the transfer has completed, create another scheduled task on Machine A to execute rename_backup.bat

Contents of rename_backup.bat

@echo off

FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET DATE=%%B
SET DATE=%DATE:/=%
FOR /F "TOKENS=*" %%A IN ('TIME/T') DO SET TIME=%%A
SET TIME=%TIME::=%
set TODAY=%date%
echo %TODAY%"
rename ".\SQL.bak" "SQL_%today%.bak" 
@echo on