Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.

T-SQL to find backup location

Here is a T-SQL query to find the location of the backup files.

SELECT database_name AS DBName
    ,physical_device_name AS BackupLocation
    ,CASE WHEN [TYPE]='D' THEN 'FULL'  
    WHEN [TYPE]='I' THEN 'DIFFERENTIAL' 
    WHEN [TYPE]='L' THEN 'LOG'
    WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
    WHEN [TYPE]='G'  THEN 'DIFFERENTIAL FILE'
    WHEN [TYPE]='P' THEN 'PARTIAL'
    WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
  END AS BackupType
    ,backup_finish_date AS BackupFinishDate
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
ORDER BY backup_finish_date DESC

T-SQL to find the SQL Server Installation date

Here is an handy T-SQL to find the installation date of SQL Server.

DECLARE @date AS VARCHAR(50), @ServerName AS VARCHAR(100)

SELECT @ServerName = CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR)

SELECT @date = convert(varchar(50),create_date,107) 
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

PRINT 'This SQL Server Instance "' + @ServerName + '" was insalled on '+ @date 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

When trying to load an excel spread sheet into SQL Server you may be presented with the below error.

This occurs because the Office System Driver and Data Connectivity Components are not installed on the server and SQL Server is unable to interact with the MS Office document. For the SQL Server to interact, open and load the data from excel spreadsheet, it requires the Office System Driver and Data Connectivity Components to be installed.

To fix this issue, download and install the Office System Driver and Data Connectivity Components from the below link.
http://www.microsoft.com/en-us/download/details.aspx?id=23734

This will install a set of components that can be used to facilitate transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Do you have a desire to learn SQL?

Many of us have users that show a desire to learn SQL, and that would be awesome, but who has the time to train them?

Well I have a solution I think you’ll like.  I recently ran across a site for beginners, EssentialSQL, that provides easy to understand SQL lessons in plain English.  The site is targeted towards the beginner, though I suspect a couple of developers could use their guidance as well!

All the examples are easy to follow.  The site recommends using SQLite, since it is a snap to install.  The good news is SQLite is SQL-92 compliant, so the training applies to basic SQL Server DML.

I really liked that the training comes with videos, so if you don’t get what Kris, essential SQL’s creator, has written, you can always watch him “live.”

In additional to the training posts, the site has several posts on database concepts.  Some are basic, such as explaining ACID; where as others, delve into the details of a B-Tree indexes.  Kris mentioned he plans on digging into other topic such as Hash Indexes and Normalization.  No doubt, everyone can use a refresher with these topics.

If you know of anyone having a desire to learn SQL, I would recommend they visit this site.

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.