Auto Backup + Upload

[AndrewR]

Hi All,

My aim goal is to write a batch file that will create a SQL backup of a database, and then upload it remotely without any user interaction.

I have created a batch file which will create the SQL backup and it will save it in the location that I have specified..

I then played around and Im able to move that file once created to any local destination within the computer, however when I try to automate this from the customers end (trying to upload from the customers local machine to our Company FTP folder) the batch file runs through, however no files are seen in the FTP folder..

A copy of the batch file..

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

http://pastebin.com/vKwrHCTD

[foxidrive]

xcopy /c /k /o /v /y "C:\FocusBackup\*.bak" "ftp://XXX.XX.XX.XXX/public/SQLBackup/Company/"

xcopy doesn't support FTP file transfers

You'll need to write an FTP script and use ftp.exe when using native tools.

[AndrewR]

xcopy /c /k /o /v /y "C:\FocusBackup\*.bak" "ftp://XXX.XX.XX.XXX/public/SQLBackup/Company/"

xcopy doesn't support FTP file transfers

You'll need to write an FTP script and use ftp.exe when using native tools.

Ill probably sound a bit silly, but im new to this could you point me in the right direction?

[foxidrive]

Here's a framework:

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]

We have a whole bunch of examples in our DosTips Library.
http://www.dostips.com/DtTipsFtpBatchScript.php