SQL Server Database Backup and Restore to/from Cloud(Azure)

This is also known as “SQL Server Backup to URL”.

This blog covers below topics as a reference :

  • Minimum requirement
  • Process to perform the Backup
  • Windows Azure Backup tool to configure backup to Azure
  • Restoring DB from local/Azure
  • Creating the SQL Maintenance Plan
  • Maintenance for Ongoing backup

1. Minimum requirement:

  • Manage your backups to Windows Azure: Using the same methods used to backup to DISK and TAPE,  now back up to Windows Azure storage by Specifying URL as the backup destination. Use this feature to manually backup or configure backup strategy like, for a local storage or other off-site options. This feature is also referred to as SQL Server Backup to URL. This feature is available in SQL Server 2012 SP1 CU2 or later.
  • Let SQL Server Manage backups to Windows Azure: Configure SQL Server to manage the backup strategy and schedule backups for a single database, or several databases, or set defaults at the instance level. This feature is referred to as SQL Server Managed Backup to Microsoft Azure. This feature is available in SQL Server 2014 or later.
  • Prior to SQL Server 2012:Use the add-in SQL Server Backup to Windows Azure Tool to quickly and easily create backups to Windows Azure storage.
  •  download available : https://www.microsoft.com/en-us/download/details.aspx?id=40740
  • The SQL Server instance can be on-premises or in a Windows Azure Virtual Machine.
  • The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.

 

2. Process to perform the Backup:

Step 1 :  Create Windows Azure Storage Objects: 

                      Create a Windows Azure storage account and then a blob container.

Step 2:  Create a SQL Server Credential

                       Create a Credential to store security information used to access the Windows Azure storage account.

Step 3:  Write a Full Database Backup to the Windows Azure Blob Storage Service

                       Issue a T-SQL statement or via SQL Server management Studio to write a backup of the sample database to the Windows Azure Blob storage service.

Step 4:  Perform a Restore From a Full Database Backup

                      Issue a T-SQL statement or via SQL Server management Studio to restore from the database backup you created in the previous step.

3. Windows Azure Backup tool

In case SQL Server Database is older than 2012 , use Windows Azure Tool to create Rules,

  1. Once installed, the tool should be appear on the Programs list.
  2. Click on the “Microsoft SQL Server Backup to Windows Azure…” to start the Wizard.
  3. On the Rules page, click Add to create a new rule.  NOTE: Only the Add button is enabled if you are using this tool for the first time. 
  4. Once created one or more rules, you can see the existing rules and the option to Modify or Delete the rule .
  5. Choose to apply the rule to all paths to the local machine or to one specific location.Specify the file name pattern that this rule should apply to.
  6.  For example, to apply this rule to all files with the extension .bak, specify *.bak in the File name pattern field.
  7. Specify the Windows Azure storage account information, so the backups pecified in Step 1 can be redirected to use the Windows Azure storage as the backup destination.
  8. For Windows Azure Storage, you must specify the name of the account, the storage access key, and the name of the container. 
  9. The storage name and access key are used to authenticate to the storage account, the container. Click Verify account to ensure that the information specified is valid and the tools is able to connect to the storage account.
  10. Enable or disable encryption or compression.  If  enable encryption,  specify a password. The password is used for decryption purposes.   click Finish to create the rule. 
  11. On clicking Finish, see the following page with the rule configuration.  Close the wizard once you have completed adding or modifying the rules.

 

to be continued….