Mirrored Databases disconnect and goes in-recovery state after a server restart

Recently, I had a situation where a production server was restarted and soon after the restart we found that the database mirroring was disconnected.
And as a bonus, one of the mirrored database was hung in recovery state.

When I investigated further, I found the below message in the SQL Server Logs.

Bypassing recovery for database '<DB Name>' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

This clearly says the connection between the principal and  the mirror is not established properly and the combination of a Mirrored Database with a big log file and a server restart caused the "In Recovery State" of the mirrored database.
To fix this error, I had to Restart the Database Mirroring Endpoint.

--To Stop the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STOPPED

--To Start the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STARTED

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.