Feeds:
Posts
Comments

Archive for the ‘SQL Backup and Restore’ Category

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 Azure it is very simple to take backups of Azure SQL databases as well as of regular SQL Server.

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

About these ads

Read Full Post »

“How do I restore my backup?”

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.

Earlier I have created a SQL in Sixty Seconds Video on How to Take Backup of the database, in this video we learn how we can restore the same backup.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

Read Full Post »

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.

Watch a 60 second video on this subject

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

Read Full Post »

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)

Read Full Post »

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.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

Read Full Post »

Older Posts »