SQL Server – Best places to store backups

SQL Server – Best places to store backups

Everybody knows it is necessary to create SQL Server backups – we  have covered this topic extensively before (see Best SQL backup software). However the question of where to store the backups often goes unanswered.
I will try to compare some of the most popular options for  this task. For demonstration purposes we will use the options that SQLBackupAndFTP provides us when we select a destination for storing backups.

SQL backup to Local/Network Folder/External HDD

SQLBackupAndFTP Folder Settings form

folder

If you store backup on the same drive as your database – you won’t have it when the disk fails. If you store it on the same server, you may lose when your server goes down. If you store it on the network, you may lose the backup when the whole office goes down in flames. You may think the chance of it is slim, but these things happen quite often and a smart database administrator should be prepared for that.

So why would you ever choose storing backups on-site and not on the cloud?
Well, if your data backup is over 100GB, a network backup becomes a very attractive option. Because your network speed would always exceed your cloud speed, this is a perfect option for storing large backups, or less important backups, or duplicates of whatever you store in the cloud. And if you backup to external HDD, with proper policies in place you could take the HDD off-site. However I would prefer to exclude the human factor from the process.

SQL backup to FTP server

SQLBackupAndFTP FTP Settings form

ftp

You may be surprised, but old FTP is live and kicking. It is still a viable option for extra cheap storage. If you own an FTP server, the cost of space may be close to 0. The downside is that it is still on the web, so upload speeds will be slower than on your network. Also you should be sufficiently proficient to configure and manage it. And even if you do that, chances are that engineers at Amazon are still better than you in this.

My biggest problem in FTP is that it is terribly insecure. If you use FTP, anyone who eavesdrops  on communication can get your FTP passwords and everything that you send. To overcome this, a better software packages like SQLBackupAndFTP allow you to use SFTP or FTPS and encrypt your backup, making this type of communication highly secure again.

SQL backup to Amazon S3

SQLBackupAndFTP Amazon S3 Settings form

amazon

Frankly this is my favorite option. It is the most stable of any cloud solution I have tested. It is very reliable and fast. If you have the option of storing in Amazon S3 – just use it. Also, if you need ultra-low cost storage and do not mind that retrieving your backups may take several hours, your can use Amazon Glacier (Just open the AWS console and configure Lifecycle Role for a bucket to add actions Transition to Glacier and Expiration. Find more information in this blog)

SQL backup to Dropbox

SQLBackupAndFTP Dropbox Settings form

Dropbox

SQL Backup to Dropbox is my second favorite option after Amazon S3. Dropbox uses Amazon S3 internally, so you get similar quality of storage. For large backup sizes Amazon S3 is significantly cheaper though. But if you want to configure a backup for a small client’s database fast, nothing beats a free Dropbox backup plan (limited to 2GB). Your client would be very impressed if you set up the complete backup process in the cloud for practically free in a matter of a few minutes.

SQL backup to Google Drive

SQLBackupAndFTP Google Drive form

GoogleDrive

At this point the .NET library that Google Drive provides for software developers is in beta. It is not the most stable of products. If your backup size is big or your connection is slow, the Google will drop your upload in 1 hour no matter what. So I would strongly advise you to stay away from it, at least for now.

SQL backup to Box

SQLBackupAndFTP promises to bring this destination to their product. In the meantime the Box (formerly Box.NET) is one of the oldest and still a very strong contender among the cloud storage options.

SQL backup to SkyDrive

You can not avoid mentioning SkyDrive when talking about cloud storage. I can not comment on how stable SkyDrive is. However (I think because of Windows 8 push) Microsoft does not provide .NET API for pre-Windows 8 platforms, so developers of SQL Server software have no way to bring native backups to SkyDrive.

Conclusion

With simple tools like SQLBackupAndFTP and cheap storage options there never was a better time for storing your backups in the cloud. Keep your database backup on the network and on one of cloud storages. Use Amazon S3 by default and Dropbox for small databases (or budgets). If you already own and FTP server and can configure it to use through FTPS or SFTP – use it. Whatever you choose – there is no excuse not to have backups.

Submit a Comment

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