Powershell script to FTP recent files.

EdlenIT
EdlenIT used Ask the Experts™
on
Hello all,

I am extremely new to Powershell so any help would be greatly appreciated!  

Our SQL DB server is hosted.  I need to copy the .bak files for the SQL weekly backup, differential backup and transaction log backups to another offsite location via FTP.

The source folder is:

\\servername\c$\database backups\daily backups\
\\servername\c$\database backups\differtials\
\\servername\c$\database backups\translogs\

Because the full backups happen every Friday evening and are pruned every 3 weeks, I only want to copy the most recent Friday's backup.  I'd like the backup to run Saturday morning.

Because the diffs and the trn logs backups run constantly I'd like to also copy only the previous date's files.  I'd like to run these every morning between 12am and 2am.

Can someone please assist?

Thank you all in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
OK, well since nobody has offered you a Powershell solution since Monday I'll go ahead and offer a non-powershell answer.  

1) Download Robo-FTP from www.robo-ftp.com and install the program.  

2) Create the following file in the Robo-FTP installation folder and and name it send_backups.s  You'll need to modify the server address, username, password, and folder paths as needed.
BEGINFUNCTIONS
FUNCTION UploadFilesFromYesterday pattern
Set yesterday = %date
DATESUB yesterday 1
:yesterday_loop
GETFILE pattern
IFERROR!= $ERROR_SUCCESS GOTO yesterday_done
IFDATE!= %nextfiledate yesterday GOTO yesterday_loop
SENDFILE %nextfile
GOTO yesterday_loop
:yesterday_done
RETURN 
ENDFUNCTION
ENDFUNCTIONS

:start
WORKINGDIR %installdir
CRON
FTPLOGON "ftp.domain.com" /user="UserID" /pw="secret"
IFSTRCMPI %nextcmd "weekly" GOTO weekly
WORKINGDIR "\\servername\c$\database backups\daily backups\"
FTPCD "/remote/destination/for/daily/backups"
UploadFilesFromYesterday "*.bak"

WORKINGDIR "\\servername\c$\database backups\differtials\"
FTPCD "/remote/folder/for/differential/backups"
UploadFilesFromYesterday "*.bak"

WORKINGDIR "\\servername\c$\database backups\translogs\"
FTPCD "/remote/folder/for/translog/backups"
UploadFilesFromYesterday "*.bak"

FTPLOGOFF
GOTO start

:weekly
WORKINGDIR "\\servername\c$\database backups\weekly backups\"
FTPCD "/remote/folder/for/weekly backups/backups"
GETFILE "*.bak" /newest
SENDFILE %nextfile
FTPLOGOFF
GOTO start

Open in new window

3) Create the following file in the Robo-FPT installation folder and name it crontab.txt  This schedule is for running the daily every day at 1am and the weekly on Saturdays at 3am.
0	1	*	*	*		daily
0	3	*	*	6		weekly

Open in new window


4) Launch the Robo-FTP Service Installer from the Start menu and configure a new service that runs your send_backups.s script file created above in step 2  This will create a Windows Service that uses your script file.  It will automatically restart whenever the computer is rebooted.

5) Have a beer, you are done.




Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial