Automate Your Website File and MySQL Backups

Part 4 – Retrieving your backups

If you haven’t already, at this point you will need to download WinSCP and install it on your local Windows PC. Make a note of the path where WinSCP was installed to. It will probably be C:\Program Files (x86)\WinSCP on a 64 bit PC or C:\Program Files\WinSCP on a 32 bit PC. This example will assume using a 64 bit PC.

You will need to create 2 new folders on your PC. One for storing the retrieved database backups and one for storing the retrieved file backups. I’m going to keep it simple here and create the 2 folders as follows:
C:\backups\databases
and
C:\backups\files
If you want to store your backups somewhere else, just be sure to change the paths as appropriate in the next steps.

Navigate to the C:\backups folder you just created and create a new text file. Open the file and copy/paste the following code into the text file:

option batch on
option confirm off
open sftp://SFTPUsername:SFTPPassword@YourDomain.com:SFTPport
get -delete /home/CpanelUsername/backups/databases/CpanelUsername-databases-????-??-??.tar.gz "C:\backups\databases\"
get -delete /home/CpanelUsername/backups/files/CpanelUsername-files-????-??-??.tar.gz "C:\backups\files\"
exit

Be sure and change SFTPUsername, SFTPPassword, YourDomain.com, SFTPport and CpanelUsername for your situation.
Rename the text file to “WinSCP-backup.script” and save it in C:\backups.

Create another text file in C:\backups. Copy and paste the following code into this new text file:

"C:\Program Files (x86)\WinSCP\winscp.com" /script="C:\backups\WinSCP-backup.script"
@echo.
@echo.
@echo Check for any errors then press any key to exit.
pause
"exit"

If you have a 32 bit version of Windows, you will need to change “C:\Program Files (x86)\WinSCP\winscp.com” to “C:\Program Files\WinSCP\winscp.com” or wherever the correct path to WinSCP is on your computer. Rename this file to WinSCP-backup.bat and save it in C:\backups.

Before the WinSCP script can connect to your ftp server, WinSCP will first need to save your host key. The following steps will do just that.
Open a command prompt in windows.
Copy the following code and paste it into the command prompt window.

"C:\Program Files (x86)\WinSCP\winscp.com" /command "open ""SFTPUsername:SFTPPassword@YourDomain.com:SFTPport"

Edit SFTPUsername, SFTPPassword, YourDomain.com, and SFTPport for your situation.
If you are using a 32 bit version of Windows, you will also need to edit the path to WinSCP by removing (x86) and the space that precedes it. Once you’ve finished editing the line, hit enter to execute the command.
When asked if you want to add the host key to the cache, type “y” and hit enter.
Type “exit” and hit enter to exit the WinSCP console.
Type “exit” again and hit enter to exit the command prompt.

Launching WinSCP-backup.bat will execute the script in WinSCP-backup.script. This script will connect to your ftp server, retrieve the database and file backups you created earlier, and then remove those backups from the web server after a successful transfer. When the script finishes, the command prompt will remain open so you can check for any errors. Press any key to close it. Check your “C:\backups\databases” and “C:\backups\databases” folders to make sure your backups were transferred successfully.

The final step is to create a windows scheduled task to run C:\backups\WinSCP-backup.bat once a day. This should be scheduled at a time when your PC will most likely be up and running. I have mine scheduled for 12:30 so that I can confirm the backup was successful as soon as I come back from lunch and because I know my PC is usually left on during lunch. In any case it should be scheduled so that it does not conflict with the cron jobs created earlier.

Don’t worry if you miss a day or two because your computer was not on. You can either run WinSCP-backup.bat manually, or just wait till the next day. Multiple days worth of backups will be retrieved if any previous days were missed.

That’s it. Now it’s time to sit back and let your automated backups do their job. If you have any questions, comments, or issues, leave a reply below and I will do my best to respond.

This entry was posted in Website Administration, Website Backups and tagged , , , , . Bookmark the permalink.

11 Responses to Automate Your Website File and MySQL Backups

  1. Ryann Lalata says:

    I am glad to finally learn this. Thanks!

  2. Sounds very intersting.This helps me to learn something new.Thanks.

  3. Gena Nett says:

    Terrific, this information is exactly what I was searching for.

  4. Your Article is Very HelpFull , Thank you very much

  5. Pretty nice post. I just stumbled upon your blog and wished to say that I’ve truly enjoyed browsing your blog posts. In any case I will be subscribing to your rss feed and I hope you write again soon!

  6. I’ll be sharing this with my network. Very pleased with your style, thanks! I will come here again.

  7. Helpful information here. I added your site on my favorite, so visit again.

  8. I have to tell, that this article is great! Thanks for providing the such a wonderful article, you have assembled a tremendous amount of useful data here. So thanks again.

  9. Bobbi Carey says:

    What a great post

  10. Simply wanna input that you have a very nice site, I love the pattern it really stands out.

  11. humanizer says:

    There are a few excellent suggestions here. I need to renovate my own blog site a while. My goal is to begin from the start these times I think.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Proudly powered by WordPress.
preloaded image preloaded image preloaded image preloaded image preloaded image