Mount a Microsoft SQL Server database

This section describes how to mount captured Microsoft SQL Server:

Instances
Primary databases of an AlwaysOn Availability Group
Consistency groups
Individual members of a consistency group
System databases
User databases

Mount captured Microsoft SQL Server data

With a standard mount you can mount the Microsoft SQL Server data to another server where it can be picked up and used by another Microsoft SQL Server. To mount just the captured Microsoft SQL Server data, follow these steps:

Open the App Manager to the Applications List.
Right-click a SQL Server Server instance, user database, system database, cluster, or availability group and select Access. Use the filters to find the database you need.
On the runway, select the specific image to be mounted, then select Mount.

Note: You can use the calendar widget in the upper left corner to narrow the range of backup images.
Note: OnVault images can be mounted very efficiently from backup/recovery appliances. You may choose how the snapshot pool is used in this kind of mount, with options including storage optimized, balanced, performance optimized, and maximum performance. The first three options result in a mount that takes place quickly, and uses varying amount of storage in the snapshot pool to boost performance. The last option, maximum performance, copies all data to the snapshot pool before the mount is performed.
Optionally, enter a label that lets you to clearly identify this mounted data.
In the Application Options section of the Mount Image dialog, don't select Create New Virtual Application. To mount the Microsoft SQL Server data as a virtual database, see Mount a SQL Server database as a new virtual database.
Complete the Mapping Options and Item Selection fields if applicable.

Options presented vary according to the source that is selected. For example, databases on VMware VMs have a Map to all ESX hosts option. Clustered databases have the Map to Cluster Nodes option.
Click Submit and the mount job is submitted.
Once the mount operation is successful, log onto the database server and verify that the mounted image is available in the Active Mounts.

Mount a SQL Server database as a new virtual database

A virtual application mount operation mounts a captured database as a virtual application. It lets you bring a database online quickly without having to actually move the data and without having to manually configure a new instance of the database. A virtual application mount addresses the challenges of creating and managing copies of production databases without manual intervention by database, server, and storage administrators.

This section describes how to mount a captured Microsoft SQL Server database as a virtual application. You can mount the following for Microsoft SQL Server:

Instances
System databases
User databases
Consistency groups
Individual members of a consistency group
Databases in AlwaysOn Availability groups

Before mounting an image, ensure that iSCSI has been configured on the host where the images are mounted, or NFS datastores in VMware have been configured.

If you want to mount just the Microsoft SQL data files without adding any database to a SQL Server instance, see Mount captured Microsoft SQL data.

For corrupt or deleted databases, mounting a SQL Server database as a virtual application to its original server is an efficient alternative to performing a restore of the database.

To mount a captured Microsoft SQL Server database as a virtual application, follow these steps:

Open the management console to the App Manager > Applications list.
Right-click a SQL Server instance, user database, system database, cluster, or availability group, and select Access. Use the filters to find the database you need.
On the runway of images, select the image to be mounted. On the right side, select Mount. You can use the calendar widget in the upper left corner to narrow the range of backup images.

Note: OnVault images can be mounted very efficiently from backup/recovery appliances. You can choose how the snapshot pool is used in this kind of mount, with options including storage optimized, balanced, performance optimized, and maximum performance. The first three options result in a mount that takes place quickly, and uses varying amount of storage in the snapshot pool to boost performance. The last option, maximum performance, copies all data to the snapshot pool before the mount is performed.
In the Application Options section, optionally enter a label that lets you clearly identify this mounted data.
Select Create New Virtual Application.
If the database was captured along with its logs, the App Options dialog provides an option to roll the logs to a specific point in time based on when and how often logs were captured.
From the SQL Server Instance Name drop-down list, select the SQL Server instance that the new database should be added into. If the required instance name is not included in the drop-down, you can type the name manually in the space provided.
If you are mounting a single database, then enter a name for your new virtual database into the SQL Server Database Name field.

If you are mounting multiple databases, then enter a name for a new consistency group that contains your virtual databases into the Name of Consistency Group field.

Note: Valid characters include letters, numbers, @, #, -, _. Leading and trailing spaces are not allowed.
The virtual application mount is a new virtual database. To protect the new virtual databases, select Manage New Application, and select the template and profile to use.

The virtual application mount is a new database. The snapshots of the database are incremental.

The virtual application appears in the Application Manager list of applications.

If you don't select Manage New Application then it appears in the Application Manager as an unprotected application. It can be protected like any other application.

Virtual SQL Server databases mounted to a SQL Server instance are always protected separately from the instances' user and system databases.
Recover Database After Restore: Leave this selected—default—if you want to bring the database to an online state ready to process transactions. Deselect it to leave the database in a restoring state, so additional transaction log backups can be manually applied to roll the database forward to a specific point in time.
Recover User Logins: If you have enabled the policy option Backup SQL Server Logins, then selecting this option results in a restore of those logins to the target SQL Server instance. For domain accounts, the user accounts only are restored if the target instance is on a server in the same domain, or in a domain with a trust relationship to the source SQL server. SQL local accounts are always be restored. Use this option if you want to ensure all users who could access the original source database can access the new virtual database.
In the Username and Password fields enter a username and password only if needed. If the Backup and DR agent account does not have privileges to detach the database during an unmount operation, or to apply transaction logs, then enter credentials here for an account with those privileges. See Required SQL Server roles for the Windows user for details.
Select a Recovery Model if you want to change the recovery model of the new virtual databases. Default is to retain the original database's recovery model.
For Overwrite Existing Database, indicate when to overwrite a database on the target server or instance that has the same name as the new databases being mounted: Yes, No, or Only if it's Stale.
In the Mapping Options, you can enter a Mount Location. If an application has only one volume, you can only specify the mount location here. If an application has multiple volumes, you can:
- Enter a mount location. All volumes are automatically mounted at the specified mount location, using sequential drive letters or numbered directories at the specified mount point.
- Leave this space blank and in the Advanced Options, manually specify a mount location for each volume.
Click Submit and the job is submitted.
When the mount job is finished, log onto the database server and verify that mounted image is available in the Active Mounts.

Management of the active mounts

Once you have created a mount, track the image form the App Manager > Active Mounts. Don't leave any image mounted indefinitely. This is because the backup image that the mount was created from, cannot expire until all its mounts have been deleted. When you have finished using the mounted image then either:

Unmount the image. You can remount it if needed. You can then delete the image later when you are certain it is no longer needed.
Unmount and delete the image. This deletes the mounted image, not the backup that the mount is based on.
Migrate the image. This is only for Microsoft SQL Server Mounts.

Mount encrypted SQL Server data

Backup/recovery appliances capture encrypted SQL Server databases but don't capture their private keys, encryption certificates, or passwords.

This section describes the following:

Determining if SQL TDE is enabled
Troubleshooting SQL Server encryption
SQL Server master key, encryption certificate, and password procedures

If you are restoring an encrypted SQL Server database over an existing SQL Server database, the private key, encryption certificate, and password are already present on the SQL Server instance and once the restore operation finishes, the SQL Server database works as expected.

If you are performing a virtual application mount of an encrypted database, or a mount of just the encrypted SQL Server data, the SQL Server instance on which the encrypted database or data is mounted must have the following:

Transparent Data Encryption (TDE) enabled
A copy of the Private Key from the source SQL Server database
A copy of the encryption certificate from the source SQL Server database
Provide the password of the source SQL Server database

Procedures are in SQL Server master key, encryption certificate, and password procedures.

Determine if SQL Server TDE is enabled

To determine if TDE is enabled on a SQL Server instance, you can use Microsoft's SQL Server management studio's user interface (SSMS), or you can use a manual query to determine if encryption is enabled on a database. For example:

    SELECT
     DB_NAME(database_id)AS dbname,
     encryption_state,
     case encryption_state
       WHEN 0 THEN 'Unencrypted (no
    database encryption key present)'
       WHEN 1 THEN 'Unencrypted'
       WHEN 2 THEN 'Encryption in Progress'
       WHEN 3 THEN 'Encrypted'
       WHEN 4 THEN 'Key Change in Progress'
       WHEN 5 THEN 'Decryption in Progress'
       ELSE CAST(encryption_state AS
    varchar(20))
     END AS encryption_state,
     key_algorithm,
     key_length
     FROM sys.dm_database_encryption_keys

SSMS

To use SSMS to determine if encryption is enabled on a database, follow these steps:

From SSMS right-click on the database name.
From the drop-down menu select Properties and the database's properties are displayed.
Under Select a Page, click Options and the options for the database are displayed.
Under State, ensure that Encryption Enabled is set to True.

Troubleshoot SQL Server encryption

The following are two common errors:

SQL error 24583: This indicates a missing encryption certificate. The following 24583 SQL Server error indicates that you are trying to perform a mount to a SQL Server instance that does not have the encryption certificate of the source SQL Server instance.
SQL error 33117: The transparent data encryption not enabled. The following 33117 SQL Server error indicates that you are trying to perform a mount of an encrypted SQL Server database to a SQL Server instance that does not have Transparent Data Encryption enabled.

SQL Server master key, encryption certificate, and password procedures

Creating and copying master keys and encryption certificates are standard Microsoft SQL Server procedures that are not unique to backup/recovery appliances. They are provided here as a convenience:

Create a new master key
Create a new encryption certificate
Apply server master key and encryption certificate
Create a copy of the server master key, encryption certificate, and provide source password
Copy the encryption certificate, private key, and provide source password

For more information, see Microsoft's detailed information on security certificates and keys: https://msdn.microsoft.com/en-us/library/ff848768.aspx

Create a new master key

    use master;
    go
    create master key encryption by password = 'SMKSourcePassword';
    go

Create a new encryption certificate

    use master;
    go
    create certificate sourcedbcert with subject = 'Act Test Cert';
    go

Apply server master key and encryption certificate

    use DATABASENAME;
    go
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE sourcedbcert;
    go
    alter database DATABASENAME
    set encryption on;
    go

Create copy of server master key, encryption certificate, and provide source password

If a SQL Server database on one SQL Server instance is mounted to another SQL Server instance, you must manually copy the to-be-mounted database's server master key, encryption certificate, and password. Then copy the server master key, encryption certificate, and password to the other SQL Server instance.

To make a copy of a server master key, encryption certification, and password, complete the following:

    use master;
    go
    backup certificate sourcedbcert to file = 'E:\Enc\Sourcecert'
    with PRIVATE KEY (file='E:\Enc\Privatekey',
    ENCRYPTION BY PASSWORD='SecurePassword');
    go

Copy encryption certificate, private key, and provide source password

If an encryption-enabled SQL Server database or data is mounted to a new SQL Server instance, the new instance must have a copy of the source SQL Server instance's server master key, encryption certificate, and password. Manually copy the encryption certificate and password copies you made on the source SQL Server instance in the previous section to the new SQL Server instance. From the new SQL Server instance, complete the following:

    create certificate destinationdbcert
    FROM file = 'C:\Program Files\Backup and DR\sqlenc\Sourcecert'
    with private key (file = 'C:\Program Files\Backup and DR\sqlenc\Privatekey',
    decryption by password = 'SecurePassword')
    go

The Backup and DR SQL Server DBA guide

This page is one in a series of pages specific to protecting and recovering Microsoft SQL Server databases with Backup and DR. You can find additional information in the following pages: