Create an Encrypted Backup in SQL Server 2014

Encryption for Backups is a new feature introduced in SQL Server 2014 and the benefits of this option are

1. Encrypting the database backups helps secure the data.
2. Encryption can also be used for databases that are encrypted using TDE.
3. Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups.
4. This feature supports multiple encryption algorithms including AES 128, AES 192, AES 256, and Triple DES
5. You can integrate encryption keys with Extended Key Management (EKM) providers. 

The following are pre-requisites for encrypting a backup:

1. Create a Database Master Key for the master database.
   

   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepass@word123';
   GO
   

2. Create a certificate or asymmetric Key to use for backup encryption.
   

   Use Master
   GO
   CREATE CERTIFICATE CertforBackupEncryption
      WITH SUBJECT = 'Certificate for Backup Encryption ';
   GO
   

Backup the database with encryption:

BACKUP DATABASE [SansSQL]
TO DISK = N'C:\Backup\SansSQL.bak'
WITH
  INIT,
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = CertforBackupEncryption
   ),
  STATS = 10
GO

Restoring the encrypted backup:
SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance.

Reference: http://msdn.microsoft.com/en-us/library/dn449489(v=sql.120).aspx

Page Level Restoration now has GUI

In one of my previous posts "Page Level Restoration", I had explained how to perform a page level restoration for a database. The whole procedure was using the T-SQL commands.
From SQL Server 2012 onward, we have an GUI to perform Page level restoration which makes life easy.

Backup a database to Windows Azure storage and restore a database from Windows Azure storage

The evaluation of SQL 2014 continues and here is what's new in Backup and Restore of SQL Server 2014.

In SQL Server 2014, we will be able to backup the database to Windows Azure storage and restore the database backup directly from the Windows Azure storage. 

To backup a database to Windows Azure storage, Choose the "Back up To:" to "URL" instead of "Disk"

To restore a database from Windows Azure storage, Choose the "Backup media type:" to "URL" instead of "File"

And here is where you connect to the Windows Azure storage during restore operation

Re-assign F5 key to refresh action in SQL Server 2014 Management Studio

During my evaluation of SQL Server 2014, I happened to face a problem, when I hit the F5 key in the Object Explorer, the SSMS was starting the Debug action instead of Refresh.
Usually most of them will be happy for F5=Refresh and F5=Query Execution.

Here is the procedure to Re-assign F5 key to refresh action in SQL Server Management Studio.

1. Open Management Studio
2. Go to Tools >> Options
   
3. Expand Environment >> Keyboard >> Keyboard
   
4. In the "Show commands containing:", type "View.Refresh" and here you can observe that "Shortcuts for selected command:" will be grayed out
   
5. Now choose "Global" in the "Use new shortcut in:" and press F5 button in "Press shortcut keys:"
   
6. Click Assign
7. Click Ok

From now on, when you press F5 in the Object explorer, the window will be refreshed instead of initiating debug option.

T-SQL query to find the timezone offset of the SQL server

Here is a short T-SQL query to find the timezone offset of the SQL server.

SELECT 'UTC'+RIGHT(SYSDATETIMEOFFSET(),6) AS 'TimeZone Offset'