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
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.
ReplyDeleteI have recently found an excellent Salesforce Training in India whose faculty is exceptional and you can also try this Salesforce Training and Certification in Jaipur whose syllabus is state of art. Here at Salesforce Training in Mumbai instructors are perfect to teach salesforce crm. My advice for you is to join demo at Salesforce training in Pune | Course Cost and in weekends try this best Salesforce Training in Noida | Course Cost who is providing great teaching services on Salesforce Training in Delhi and Fee Details.
ReplyDeleteNice informative blog, it shares more interesting information. This blog is useful to me.
ReplyDeleteGerman Classes in Tambaram
German Classes in Anna Nagar
German Classes in Velachery
German Classes in T Nagar
German Classes in Porur
German Classes in OMR
This blog is very interesting to read, this contains more useful information, Keep sharing more blogs.
ReplyDeleteArray in python
oops in python
Python frameworks
goto statement in python
Selenium interview questions and answers