SQL SERVER – SQL Backup and FTP – A Quick and Handy Tool

Scroll down at the end of this post to win my SQL Wait Stats Book.

I have used this tool extensively since 2009 at numerous occasion and found it to be very impressive. What separates it from the crowd the most – it is it’s apparent simplicity and speed. When I install SQLBackupAndFTP and configure backups – all in 1 or 2 minutes, my clients are always impressed.

To put it simply, SQLBackupAndFTP is MS SQL Server backup software that performs these tasks:

  • Backup SQL Server Database
  • Zip the backups
  • Encrypt the backups
  • FTP the backups to remote FTP server
  • Move file to local area network
  • Send final status of tasks in email.
  • Allows to monitor the history of jobs through the web

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see this version comparison chart, but even Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

But enough theory, let us now see how we can take backup and move it to the desired folder.

Image 1: You start from the main form and  all the settings are very easy as described in the following image. From database selection to job scheduling, you can configure everything on one screen.

On this form you would usually do the following:

  1. Press “Connect to SQL Server” button to connect to your SQL Server instance
  2. Check databases in the list to select databases to backup
  3. If you need to store backups in a Local/Network folder – fill out the path to this folder
  4. If you need to send backups to FTP – fill out your FTP server settings
  5. Enter you email to receive job notification e-mails
  6. Fill out the time when a scheduled daily backup job should run
  7. Press “Run Now” button to test your backups

Note that the above covers just the basic scenario when you need full backups once a day. If you need Differential or Transaction Log backups or a schedule more complicated than once per day – press “Advanced Schedule”.

Image 2: Advanced Backup Schedule. Again, what I love about this program – it is how simply it allows you to resolve even quite complicated tasks. Your can click the sample scenario link that matches you needs the most – like “Daily 4 times” and everything is set – you’ll get one full and 3 Differential backups per day. Or select any other scenario that suits you.

Image 3: Advanced Settings screen is optional and allows you set encryption, compression levels, backup file name, temporary folders, if you want to use web logs (more about it below) and more

Image 4: If you checked “Use web log” in Advanced Settings, the log of your jobs is being saved on the web on sqlbackupandftp.com site (see weblog demo) and you can see the history of these jobs (even collected from multiple servers) in a single page – very interesting feature that can be very handy for large organizations

A detailed configuration tutorial is available here if you are interested.

Now, a question that might baffle your mind is – how this tool is better than maintenance tasks available in SQL Server. Well, here goes the answer. Maintenance tasks are easy to set up for backups; however, SQLBackupAndFTP have integrated solution for encryption, FTP and email which make it superior to maintenance tasks in every aspect.

I would like to present my unbiased view about this tool. At the same time, I want to give my readers a broad perspective about backup tools in general.  On comparing this tool with other backup tools I have experienced that it is not the fanciest tool to manage backups or check their health. This is one tool that smoothly performs the job in a very few steps compared to native SQL Server backup, which takes numerous steps to execute the similar job.

Image 5: Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

While testing SQLBackupAndFTP I noticed some limitations. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

SQLBackupAndFTP is rapidly evolving since I first started using it two years ago. I spoke to developers and it seems that their immediate goal is to include cloud storage support – users will be able to backup directly to Microsoft SkyDrive, Amazon S3, DropBox, Box.net and others – very exciting development.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article. I will also give print book of my SQL Server Wait Stats book to two individual who share their feedback about this product. The book will be sent to any country where Amazon delivers the book.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – SQL Backup and FTP – A Quick and Handy Tool

  1. Pinal, thank you for the great review of our product. To show our appreciation let me offer your blog readers an exclusive promo code that gives 10% off all of our SQL products valid till 12/01/2011. It is Pinal2011 (click on “enter promo code” link next to total price to enter it)

    Like

  2. The tools seems to be neat looking at what you have described, especially with the coming ability to backup data into the cloud. how would this compare with Redgate backup software which is an other popular tool which is being used.
    Thanks for Sharing

    Like

  3. Hi all,

    From this blog, i got more information about this SQL Backup and FTP tool, Configure the steps are simple. Also, it will be useful for FTP and email notifications for backups and jobs. Its the good thing to share this HandyTool with blog readers.

    Like

  4. Hi,

    I used this tool. It’s make easy a DBA tasks. DBAs most import task is take backup of database on regular bases. It’s a lengthy and sometime boring task. But with this tool, it’s quite easy. The great thing about this tool is, backup + encrypt + FTP + Notification (E-mail). That’s means, everyone is updated with status.

    Panel thanks for sharing a great tool !!!

    Vinay Kumar

    Like

  5. http://sqlservermct.wordpress.com/2011/10/04/tool-review-%e2%80%93-sql-backup-and-ftp/ – neat tool for simple jobs. Unfortunatelly my colleague and I are monitoring 200+ instances, some of them on the terrabyte side, so we need a slightly more complex backup strategy. But if you are a developer or an accidental DBA at a small IT shop (roughly 80-85% of the world falls into this category, as a personal opinion), great tool that does a great job.
    As for the rest of us, we’re still waiting for a miracle to happen :-)

    Like

  6. I tried it today for the first time and it was very easy to follow. A year ago I wrote a program in VB6 to stop the DB, backup (mdf+log), zip,move to a network file and start db. I used timer to do one by one and the winzip to zip it. The problem I faced, when the zipping took too long my VB would go for the next script thus would cause problem. I really liked this backup program you mentioned specially the free version does all the basics. thanks to the maker of the software and to pinal.

    Like

  7. Hi Pinal,

    I have tried to test the tool on our fail-over cluster. No matter on which machine I try to install it, it always says connecting to remote instance and schedules backup using sql scripts which takes much longer time than usual and in Beta mode yet. Any suggestions to run it like a local server ?

    Thanks.

    Chandan

    Like

  8. Hi Pinal,

    really nice to use however My db’s are pretty big e.g. 26GB. Its quicker for me to native backup with compression rather than backup and zip.

    Thanks

    MJ

    Like

  9. Nice tool for multiple database backup. But I could not find a job for restoring the full+diff+transaction logs since one click restore only serves to restore full backup.can some one help me in this regards.

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  11. I install the free version of SQLBackupandFtp. I have only one database I want to do scheluled backup.

    I want to ask what is Add folder – field – what I have to Place there? (in your example is Music ….). Can it be example the same folder where is the backuping .MDF -database?

    I have also problem with schelude – I get the Error: Object reference not set to an instance of an object.
    I think the reason is perhaps the rightness (Windows 7). I try to give CMD: ICACLS Tasks /grant user:F but it didn´t help.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s