Backup a SQL Server database to Windows Azure Storage
Starting from CU2 of SQL Server 2012 SP1, Microsoft has introduced an interesting feature letting you to backup a local database to Windows Azure storage.
Now you can specify the option "TO URL" when you are performing a database backup task: in order to complete the task you need to have a Windows Azure subscription activated and a storage account.
Windows Azure Storage Account creation
You can create a storage account in Windows Azure using the Management Portal. After that, you have to create a container for storing backup files and copy an access key used by SQL Server for performing backup tasks.
Backup and restore withTSQL
CREATE CREDENTIAL AzureRemoteBackup
WITH IDENTITY='francedstoragetodelete',
SECRET='myStorageKey';
BACKUP DATABASE AdventureWorksDW2012 TO
URL='https://francedstoragetodelete.blob.core.windows.net/backups/adworks.bak'
WITH CREDENTIAL='AzureRemoteBackup' , COMPRESSION, STATS = 5;
RESTORE DATABASE AdventureWorksDW2012 FROM
URL='https://francedstoragetodelete.blob.core.windows.net/backups/adworks.bak'
WITH CREDENTIAL='AzureRemoteBackup',
STATS = 5,
BLOCKSIZE = 65536,
REPLACE
Using SMO to backup and restore a database
Using SMO, you can backup or restore a database programmatically.
In the next example, I have created a simple console application using C# that performs database backup and restore operations.
The application is based on two classes: Program and clsBackup.
The first one, Program, reads parameters from App.Config file while clsBackup creates a credential object, if it doesn't exist, and performs the backup task using doBackup method.
In the same way, restore tasks are performed using the doRestore method.
Backup
Restore
Shown below you can find the source code of the application or you can download it from the following Azure blob storage: https://datacontent.blob.core.windows.net/pub/SQLBackupToAzure.zip
In Visual Studio, remember to import the right references pointing to the updated SDK files located into the folder C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies
-- Program
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//FD
using System.Configuration;
namespace SQLBackupToWindowsAzure
{
class Program
{
private static void printMenu()
{
Console.Clear();
Console.WriteLine("1 - backup \r\n2 - restore \r\n0 - exit");
}
static void Main(string[] args)
{
string ssServer = ConfigurationManager.AppSettings["ServerName"];
string ssStorageName = ConfigurationManager.AppSettings["StorageName"];
string ssStorageKey = ConfigurationManager.AppSettings["StorageKey"];
string ssDatabaseName = ConfigurationManager.AppSettings["DatabaseName"];
string ssCredential = ConfigurationManager.AppSettings["Credential"];
string ssContainer = ConfigurationManager.AppSettings["Container"];
string ssDatabaseBlobFile = ConfigurationManager.AppSettings["DatabaseBlobFile"];
clsBackup myclsBackup = new clsBackup(ssServer, ssDatabaseName, ssStorageName,
ssStorageKey, ssCredential, ssContainer, ssDatabaseBlobFile);
short intOptions = -1;
int intReturn = 0;
printMenu();
string sMessage;
while (intOptions != 0)
{
if (short.TryParse(Console.ReadLine(), out intOptions))
{
switch (intOptions)
{
case 1:
{
Console.WriteLine("Performing Backup on Windows Azure, please wait");
intReturn = myclsBackup.doBackup(out sMessage);
if (intReturn == 0)
Console.WriteLine("Backup Completed: " + sMessage);
else
Console.WriteLine("Error");
Console.ReadLine();
printMenu();
break;
}
case 2:
{
Console.WriteLine("Restoring a database from Windows Azure, please wait");
intReturn = myclsBackup.doRestore();
if (intReturn == 0)
Console.WriteLine("Restore Completed from " + ssDatabaseBlobFile +
" to " + ssDatabaseName);
else
Console.WriteLine("Error");
Console.ReadLine();
printMenu();
break;
}
}
}
else
{
printMenu();
}
}
}
}
}
-- clsBackup
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace SQLBackupToWindowsAzure
{
class clsBackup
{
#region private vars
private string strDatabase;
private string strStorageName;
private string strStorageKey;
private string strServer;
private string strCredential;
private string strContainer;
private string strDatabaseBlobFile;
private Server myLocalServer;
private Credential myCredential;
#endregion
private void myInitialize()
{
}
#region constructors
public clsBackup(string sServer, string sDatabase,string sStorageName, string sStorageKey,
string sCredential, string sContainer, string sDatabaseBlobFile)
{
strServer = sServer;
strDatabase = sDatabase;
strStorageName = sStorageName;
strStorageKey = sStorageKey;
strCredential = sCredential;
strContainer = sContainer;
strDatabaseBlobFile = sDatabaseBlobFile;
try
{
myLocalServer = new Server(strServer);
myCredential = new Credential(myLocalServer, strCredential);
if (!(myLocalServer.Credentials.Contains(strCredential)))
myCredential.Create(strStorageName, strStorageKey);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
#endregion
public int doBackup(out string sBackupFileName)
{
/*
0 = noError
1 = Error
*/
int intReturn = 0;
string desturl = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}_{3}.bak",
strStorageName,
strContainer,
strDatabase,
DateTime.Now.ToString());
sBackupFileName = desturl;
try
{
Backup mybackup = new Backup();
mybackup.CredentialName = strCredential;
mybackup.Database = strDatabase;
mybackup.CompressionOption = BackupCompressionOptions.On;
mybackup.Devices.AddDevice(desturl, DeviceType.Url);
mybackup.SqlBackup(myLocalServer);
}
catch (Exception)
{
intReturn = 1;
}
return intReturn;
}
public int doRestore()
{
/*
0 = noError
1 = Error
*/
int intReturn = 0;
try
{
Restore myrestore = new Restore();
myrestore.CredentialName = strCredential;
myrestore.Database = strDatabase;
myrestore.ReplaceDatabase = true;
myrestore.BlockSize = 65536;
myrestore.Devices.AddDevice(strDatabaseBlobFile, DeviceType.Url);
myrestore.SqlRestore(myLocalServer);
}
catch (Exception)
{
intReturn = 1;
}
return intReturn;
}
}
}
Some considerations on the current version
- The maximum backup size supported is 1 TB.
- At the moment, you can't perform backup or restore tasks by using SQL Server Management Studio.
- Appending to existing backup is not supported so you have to choose if backup to an existing file or create a new one with a different name.
Using Powershell to backup and restore a database to Windows Azure
Starting from SQL Server 2012 SP1 CU4, you can create a backup on Windows Azure also using Powershell.
You use the new options provided by the cmdlet "Backup-SQLDatabase" :
$url = "https://francedstoragetodelete.blob.core.windows.net/
backups/Advworks_powershell.bak"
$credential = "mycredential"
$server = "SQLSERVER:\SQL\[computer]\DEFAULT"
CD $server
Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $url
-SqlCredential $credential -CompressionOption On
In the next days I am going to update this post with more details about this last method.
Francesco
Comments
- Anonymous
February 05, 2014
The comment has been removed