Someone help turn this so it copys to my FTP folder?

[AndrewR]

Iv looked on here and various websites and im really struggling. :\

Code: Select all

@echo off
color 0A

@REM - Schedule this to run in Control Panel, Scheduled Tasks 
@REM - Auto number backup filename


SET Server=.\SQLInstance
SET DBName=SQL
SET BackupPath=C:\SQLBackup
SET BackupName=SQLBackup
SET Ext=bak
SET i=0


@REM - Create Backup Folder
IF NOT EXIST %BackupPath% MKDIR %BackupPath%


@REM - Test which files exist already
:BEGINLOOP
SET /a i=%i%+1
SET file=%BackupPath%\%BackupName%%i%.%Ext%
if exist "%file%" GOTO BEGINLOOP
:ENDLOOP

echo writing backup to %file%
@echo off
osql -E -S %Server% -Q "backup database %DBName% to disk = '%file%'"

echo.
echo.
echo.
echo.
echo Backup Process is complete
echo For querys please contact our support desk
echo T: 01234 567 899  E: support@company.co.uk
echo.
echo.
echo.
@ECHO OFF

:choice
set /P c=Do you want to backup to Company Cloud Storage?[Y/N]?
if /I "%c%" EQU "Y" goto :backup
if /I "%c%" EQU "N" goto :exit
goto :choice


:backup

echo "Files are backing up to the Datatime Cloud..."
xcopy /c /k /o /v /y "C:\FocusBackup\*.bak" "ftp://XXX.XX.XX.XXX/public/SQLBackup/Company/"
pause
exit

:exit

echo "Backup Process has finished, files not uploaded."
pause 
exit

[foxidrive]

Code that doesn't work is only helpful when there is a complete description of the problem, and what you need to do.

It looks like you were here earlier in the year and then didn't reply when you got suggestions:
viewtopic.php?f=3&t=5664

[AndrewR]

Yeah, and Im struggling to understand on how to go about it, I understand having to write the login credentials, but the ftp server name, is that just the ip address? or the path to where I want the file to go?

Code: Select all

@echo off
set "ftpScript=%temp%\%~nx0.ftp.tmp"
(
    echo open (ftp server name)
    echo (ftp user-name)
    echo (ftp password)
    echo prompt
    echo bin
    echo mput "C:\FocusBackup\*.bak"
    echo quit
) > "%ftpScript%"

ftp -s:"%ftpScript%"
del "%ftpScript%"

[Squashman]

Server name can be the domain name or the IP address.

If you want the file to go to a specific directory on the FTP server then you do a change directory to that path before the PUT statement.

CD MYDIR

[AndrewR]

Ok, so bear with me..

The FTP IP = 188.39.138.35
The username = Customer
The password = Test1
And the files I want to copy are located at C:\FocusBackup\
And the location I want the files to go are FTP://188.39.138.35/public/customer backups/

So if I understand it, it should look something like this..

Code: Select all

@echo off
set "ftpScript=%temp%\%~nx0.ftp.tmp"
(
    echo open 188.39.138.35
    echo Customer
    echo Test1
    echo prompt
    echo bin
    echo CD MYDIR "FTP://188.39.138.35/public/customer backups/"
    echo mput "C:\FocusBackup\*.bak"
    echo quit
) > "%ftpScript%"

ftp -s:"%ftpScript%"
del "%ftpScript%"

Apologies for being slow, Im new to this :\

[foxidrive]

Username and password are case sensitive

try this:

Code: Select all

@echo off
set "ftpScript=%temp%\%~nx0.ftp.tmp"
(
    echo open 188.39.138.35
    echo Customer
    echo Test1
    echo prompt
    echo bin
    echo CD "/public/customer backups/"
    echo mput "C:\FocusBackup\*.bak"
    echo quit
) > "%ftpScript%"

ftp -s:"%ftpScript%"
del "%ftpScript%"

[AndrewR]

Username and password are case sensitive

try this:

Code: Select all

@echo off
set "ftpScript=%temp%\%~nx0.ftp.tmp"
(
    echo open 188.39.138.35
    echo Customer
    echo Test1
    echo prompt
    echo bin
    echo CD "/public/customer backups/"
    echo mput "C:\FocusBackup\*.bak"
    echo quit
) > "%ftpScript%"

ftp -s:"%ftpScript%"
del "%ftpScript%"

Thanks Foxidrive, that works a treat!

Is it possible to transfer across only new files? for example everytime the backup runs it names it numerically e.g

Backup1
Backup2
Backup3

We aim to transfer the files once a week, and the backup process happens every day, so logically 7 backups a week will be transferred across..

My query is after the first week we will have backups 1-7 on the ftp, on the second week when you would be up to "Backup14" when we action the backup will it copy across all 14 backups or just the 7 new ones?

[foxidrive]

The files and storage is unclear, as well as what happens to all the backups on the local server.

Do all the files go into one folder?
Are the files named by date and time?
Do they just keep mounting up in the folder?

And you want to FTP the last 7 days worth.

Answer my questions if you can...

[AndrewR]

Do all the files go into one folder?

Yes all the files will goto the corresponding customer file

Are the files named by date and time?

No the backup files only have the number of which backup up it is, for example if we started this on 1st of Jan and ran it through till the end of the year we would have 365 backups e.g-

Backup 1
Backup 2
Backup 3

Do they just keep mounting up in the folder?

Yes

[Squashman]

So you want the Day of the Year as part of the Backup Folder name?
Example 20140717 = Backup 198

[AndrewR]

Sorry I may not have made myself clear enough..

The current backup system I have in place automates a backup of my sql database and it labels it "Backup 1" and everytime the backup takes place it finds the next available number which is fine..

So on Monday I backup and it saves as Backup 1
On Tuesday I backup and it saves as Backup 2
Wednesday Backup 3
Thursday Backup 4
Friday Backup 5
Saturday Backup 6
Sunday Backup 7

After the 7th backup has happened I will set the task scheduler to run the FTP batch file which will then pump across all 7 backups from my c:\backups folder and it will pump it across to the ftp location I have specified..

So now after the first week, we are on week 2 so the numbers will increase again..

Monday Backup 8
Tuesday Backup 9
ect ect

So when it comes to Sunday I will have 14 backups, and when the automatic ftp batch file runs, ill assume its going to pump across all 14 files (even tho the first 7 have already been transferred across?) how do I make it so that it only transfers across the files that are not already in the FTP folder (Backups 7 - 14)

[foxidrive]

My query is after the first week we will have backups 1-7 on the ftp, on the second week when you would be up to "Backup14" when we action the backup will it copy across all 14 backups or just the 7 new ones?

The answer to your query is that it will copy all 14 (*.bak) files shown in your FTP script.

The best solution depends on if the script is scheduled to run without fail weekly, the size of the files in total, etc.

An example is that if the script is set to FTP the latest 7 days files, and it is run a day late, then one day will be skipped.


A better, and very simple solution, is to have the FTP script batch file move the files that have been FTP'd to a holding directory,
say C:\FocusBackup\on-ftp-server

[Squashman]

You would be better off with a true FTP Sync program. Lots of free ones out there. They do a better job of syncing only new or changed files.

[foxidrive]

You would be better off with a true FTP Sync program. Lots of free ones out there. They do a better job of syncing only new or changed files.

That sounds more reliable. The standard FTP method could fail on a transfer, or a single file now and then, and you wouldn't know if you didn't log the transfer and read it.