4 SQL Server 2014 Cloud Enhancements (1) : Backup to Windows Azure

Prerequisites

• Create the Azure Storage: http://www.sqlpanda.com/2014/10/azure-storage-azure-storage-explorer.html
• Create the certificate for encryption: http://www.sqlpanda.com/2014/10/sql-server-2014-backup-encryption.html

---- Create Master key

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word'

GO

--- Crete Certificate

CREATE CERTIFICATE BackupCert

   WITH SUBJECT = 'BackupCert';

GO

select * from sys.certificates

GO

Use [master]

GO

--- Backup certificate

BACKUP CERTIFICATE BackupCert 

    TO FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

    WITH PRIVATE KEY ( 

        FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 

    ENCRYPTION BY PASSWORD = 'Pass@word');

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey'

GO

backup to Azure Storage

In the Azure portal , get the URL from PRIMARY BLOB SERVICE ENDPOINT

The append the folder name after the URL, the name is case sensitive, in my example, the folder name is backup, if I use Backup , it will get the below error.

Msg 3271, Level 16, State 1, Line 38
A nonrecoverable I/O error occurred on file "https://sqlpanda001.blob.core.windows.net/Backup/AzureDB.bak:"
Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..
Msg 3013, Level 16, State 1, Line 38
BACKUP DATABASE is terminating abnormally.

--- Create Credntial 

CREATE CREDENTIAL sqlpandaAzureCred 

WITH IDENTITY= 'sqlpanda001'

, SECRET = 'Q7BGnWJGR1obq4ON697wiIunzGpLxpldpvS7iXH1YkNLh02FpVEpGDBXAVX92+fS7FRsrl2Qkr19bjIeOeVcAA==' 

GO

Select * from sys.credentials

GO

--- Backup to Azure

BACKUP DATABASE [AzureDB]

TO URL ='https://sqlpanda001.blob.core.windows.net/backup/AzureDB.bak'

WITH

  CREDENTIAL = 'sqlpandaAzureCred', 

  init,format,

  NAME = 'AzureDB',

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  COMPRESSION,

  STATS = 10

GO

Restore from Azure Storage

• Create certificate from the source server certificate backup
• Create credential as source server

--- Restore from Azure Storage with compression and encryption to another Server instance 

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word'

GO

CREATE CERTIFICATE BackupCert

    FROM FILE = '\\sgc4\C$\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

    WITH PRIVATE KEY ( FILE = '\\sgc4\C$\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 

    DECRYPTION BY PASSWORD = 'Pass@word' );

GO

CREATE CREDENTIAL sqlpandaAzureCred 

WITH IDENTITY= 'sqlpanda001'

, SECRET = 'Q7BGnWJGR1obq4ON697wiIunzGpLxpldpvS7iXH1YkNLh02FpVEpGDBXAVX92+fS7FRsrl2Qkr19bjIeOeVcAA==' 

GO

Restore filelistonly from disk='\\sgc4\C$\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\AzureDB.bak'   

GO

USE [master]

RESTORE DATABASE [AzureDB]

FROM URL ='https://sqlpanda001.blob.core.windows.net/backup/AzureDB.bak'

WITH CREDENTIAL = 'sqlpandaAzureCred', 

move 'AzureDB' to 'C:\Database\MSSQL12.SGC5SQL2014\MSSQL\DATA\AzureDB.mdf',

move   'AzureDB_log' to 'C:\Database\MSSQL12.SGC5SQL2014\MSSQL\DATA\AzureDB.ldf',

NOUNLOAD, REPLACE,

STATS = 10

GO

Reference

http://msdn.microsoft.com/en-us/library/dn449488.aspx