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 (https://blog.sqlauthority.com)