SQL SERVER – Best Practices to Store the SQL Server Backups

Nobody doubts the necessity to create SQL Server backups – I have covered this topic extensively before. The question of where to store the backups however 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 with, when we select a destination for storing backups.

SQLBackupAndFTP form to select a backup destination

SQL backup to Local/Network Folder/External HDD

SQLBackupAndFTP Folder Settings form


If you store the 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 in 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 the 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


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 with 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 package 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


Frankly this is my favorite option. It is the most stable of any cloud solution I’ve 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


SQL Backup to Dropbox is my second favorite option after Amazon S3. Dropbox uses Amazon S3 internally, so you get the 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


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.

Let us see how to take backup in very quick video:

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

5 thoughts on “SQL SERVER – Best Practices to Store the SQL Server Backups

  1. Hi,
    I think a combination is the best option: backup on a local dedicated disk for speed of backup and restore. Copy that backup to an off-site storage for availability in case of disaster.
    That’s what I do. Most of the times you can rely on your local backup and the restore will be fast. But in case of a disaster you still have the remote copy of the backup.
    I do this with the dayly full backup and the TL backups throughout the day.

    Like

  2. Good information
    Can u help me to understand how much diskspace is required to backup daily full backup of 100 GB database on local(internal) disk drive using SQL maintenace for 1 day ?. It will be backedup to tape after that.
    Thanks in advance

    Like

  3. Hello Pinal,

    As part of our work routine we get production db backups and restore them into QA. We dont have sufficient space in QA environment and management dont increase the diskspace too. When getting production db .bak files, .bak contains only 20-30GB space, but when we are restoring it occupies almost 200 + GB drive space. I am thinking that this bacause of intial size mentioned in the properties of Production Database. Is there any chance of alter /update .bak files before restoring the data?

    [email]

    Thanks
    Phani

    Like

  4. Hi Pinal!
    I´m glad to have found your site. I know a ton of people whom could very positively benifit from this and especially me too. After I took part in the SQL Server Workshop I want to get more and more informations about SQL. And your blog is a good source! Thanks for that.

    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