Thursday, October 9, 2014

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

Prerequisites

---- 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

image

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.

image

--- 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
            

image

image

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

image

Reference

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

1 comments:

  1. Backing up your system is very important all the time. This will ensure that no matter what happen your work is safe and can be retained. Just like how students via Resumeedge.com review do their cloud enhancement task on a daily basis.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates