Azure SQL database backup used to be a difficult task. Not any more. With SQLBackupAndFTP with Azure it became trivial. Here’s what you basically need to do:
Once SQLBackupAndFTP with Azure is installed, click at “Connect to SQL Server / Azure” button and specify connection properties for your Azure SQL Databases:
Then click “Run Now” to backup your Azure SQL Database(s):
Scheduling backups is also very simple – just check “Schedule this job on the main form” to run once daily or go to Advanced Settings for more options
Sounds simple? There are just a couple more things you need for this to work: you should know your Azure Server Name and Azure should allow your IP to connect. Let me explain how to do it.
How to find your Azure SQL Databases Server Name/Connection String
If you do not know a server name of Azure SQL Database, you can find this out on Windows Azure Management site. Sign in with your Microsoft account and click the “SQL DATABASES” menu item, then click your database name
Then click “Show connection strings” link in the bottom right corner:
You will see the connection strings for many platforms. Just copy to clipboard “Server” property value of “ADO.NET” connection like in the screenshot below:
And paste it into the “Server Name” field of the “Connect to SQL Server / Azure” window in SQLBackupAndFTP:
Allow your IP address to connect to your Azure SQL Databases server
For SQLBackupAndFTP to connect to Azure SQL Database, you need to configure Azure’s firewall. Otherwise you will be getting the error: Cannot open [server] requested by the login. Client with IP address [ip-address] is not allowed to access to the server…
Login to Windows Azure Management site with your Microsoft account, click “SQL DATABASES” menu item, then click your database:
Then click “Manage allowed IP addresses” link in the bottom right corner::
You will see a page where you can specify an allowed IP addresses for your databases:
On this page you can configure your firewall to allow the machine where SQLBackupAndFTP is running access to Azure. After adding the proper IP you should have no problem connecting to Azure.
Summary
With SQLBackupAndFTP with Azureit is very simple to take backups of Azure SQL databases as well as of regular SQL Server.
I often receive this question from two kinds of users – i) who are panicking as they are not able to restore database ii) developers who have full backup but not sure how to restore it and use it.
In industry everywhere you go, everybody is taking backup but I noticed hardly anybody try to restore it. When users have not restored the backup for a long time, they have either no expertise to restore or have no idea if their backup have an issue while restoring.
Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!
Let us see how DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.
Here is the basic script for Backup and Restore
Note: name of my database server is touch and I am connecting it with windows authentication.
Backup
BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'
Restore
RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'
Here is the basic script for Backup and Restore using SQLCMD
Backup
C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"
Restore
C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"
Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.
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:
Whenever I am suggesting something which changes how database works or the existing status of the database, my suggestion along with it is to take the database backup before making such changes. If the changes are in configurations, that can be easily revert but if the changes are such that it will impact the data, I always suggest to take backup. The nature of this blog is such that we have readership from readers with different expertise, some are experts and some are novice.
One of the questions I keep on receiving comments are whenever I give suggestion to take a full database backup is – how to take database backup. It is not possible to explain in each and every blog regarding how to take backup of the database as it will be of no interest to the users who are very much familiar with this concept. To help the beginners who have yet to learn regarding how to take database backup, here is the SQL in Sixty Series episode which explains how to take database backup.
There are three types of the backup – 1) Full Backup 2) Differential Backup and 3) Log Backup. We can take the differential backup as well log backup from the same screen where we take full backup from. I have explained the same also in later parts of the video.
If you are using SQL Server Management Studio – here is the path which will bring you to the screen where you can take various kinds of backup.
Right Click on Database >> Tasks >> Back up…
Let us see how to take backup in this SQL in Sixty Seconds:
Please note that if your database is large, it will take long time to take backup. We will focus on the various steps to initiate the backup in this video.
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
“How do I restore my backup?”
