SQL Server 2014 has this new capability which allows us to take a backup to a URL. Infact, it is available in SQL 2012 SP1 CU2 itself – I lied to you in my first statement :). Anyways, I thought it is worth a mention and a blog for itself.
The concepts of accessing the keys have been mentioned also at: Viewing Windows Azure Storage using SSMS.
Copy the keys and we are ready.
Now that you have these key’s handy. We will take an encrypted backup as explained in SQL Server 2014: Encrypted Backups and upload it into Azure Blob storage rather than our conventional disk subsystem. So here are the steps to follow.
Create your credential
The create credential command has been there since SQL Server 2005 but I never found a better scenario as this to use the same. So here I am sort of creating my proxy name to access the blob storage account.
CREATE CREDENTIAL mycredential
WITH IDENTITY= ‘mtcvinod’
, SECRET = ‘<< INSERT YOUR KEY FROM PORTAL >>’
Backup your database to Azure
The next step is to backup your database. This command is ALMOST similar to a normal backup statement with the ONLY change where we point to a URL rather than a disk location. Otherwise everything is same.
TO URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’
WITH CREDENTIAL = ‘mycredential’,
NAME = N’mySecureDB – compressed, encrypted’,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCert
STATS = 10;
If you are playing around with this script again and again, make sure you delete the file that got uploaded the previous time. Else we will be presented with the below error 3271. Alternatively you can add the WITH FORMAT option too.
Msg 3271, Level 16, State 1, Line 7
A nonrecoverable I/O error occurred on file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak: The file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 7
BACKUP DATABASE is terminating abnormally.
Restore from URL
Just like we did a backup, now we can also do a restore using a similar command with the change being the URL instead of a Disk location.
RESTORE DATABASE [MySecureDB]
FROM URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’
WITH CREDENTIAL = ‘mycredential’
, STATS = 5
To Viewing Windows Azure Storage using SSMS, read that blogpost or feel free to use other Azure Blob tools like the one I have used here is Cloudberry or any others of your choice.
There are a number of other error messages which are self-explanatory which need a mention here. If you are going to restore from this backup on a database which is running currently, you might get the below error:
Msg 3159, Level 16, State 1, Line 21
The tail of the log for the database "MySecureDB" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
If the backup URL mentioned doesnt exist in the given location then we will get the blow error:
Msg 3271, Level 16, State 1, Line 21
A nonrecoverable I/O error occurred on file "http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage.
PS: Note, the above example uses encrypted backups – but the same can be achieved using normal backups too.
In my subsequent blogs, let me also bring out some of the other errors one might get by using Encrypted Backups.