Monday, June 19, 2017

Procedure to restore SharePoint 2013 access apps in the same farm from database without impacting other apps

In previous blog, I’ve provided the procedure to restore SharePoint 2013 access apps after deleted in the same farm from database backups. The approach is to overwrite all the four databases including site content, access app, app management service, and secure store services. The issue for that process is it will overwrite all the whole app management and secure store services database. As a result, any new or updated app might be impacted. For example, the newly added access will not be recover after this process.

In this article, I’ll provide a different way to restore SharePoint 2013 site with access apps. However, sicne this involves database insertion, this might not be supported by Microsoft SharePoint support contract. Please contact your Microsoft support before using the approach!!!


Before we explain the procedure to restore SharePoint 2013 site with access apps in the same farm, let’s create a team site with an access app. The site has the following url “http://spsbx15/sites/APP-DR” and the Access App is named “Harry-APP-Testing” as out of box Customers table. Before we delete the access app “Harry-APP-Testing” and start the restore, let's complete the preparation steps. We will need all this information during the restore.

1. Preparation step #1, we will need to get the access apps App Ids from the site collection. You could use the following Powershell.

         $appInstances = Get-SPappinstance -web $siteUrl

        if($appInstances -ne $null)
        { 
                foreach ($eachAppInstance in $appInstances)
                {        
                    $title = $eachAppInstance.Title
                    $version = $eachAppInstance.App.VersionString
                    $AppPrincipalId = $eachAppInstance.AppPrincipalId
                    $Id = $eachAppInstance.Id
                    $Status = $eachAppInstance.Status
                    $AppWebFullUrl = $eachAppInstance.AppWebFullUrl

                    Write-Output "$title; $version; $Id; $AppPrincipalId; $Status; $AppWebFullUrl"| Out-File $reportName -append 
                    Write-Output $eachAppInstance
    
                }
        } 

The result is listed below and please record the App Id.
  • Site Url                 http://spsbx15/sites/APP-DR
  • App Name            Harry-APP-Testing
  • App Id                  ddbe8814-5cdf-48a4-9322-a53cad392c17
  • App Type             Access App

2. Preparation step #2, we will need to get the access apps ReferenceIds, AppInstallationId from the site collection. You could use the following SQL query on the site content DB.

SELECT TOP 1000 [AppInstallationId]
      ,[DatabaseName]
      ,[ReferenceId]
      ,[TargetAppId]
      ,[AppSource]
      ,[SiteId]
  FROM [WSS_Content_APP-DR].[dbo].[AppDatabaseMetadata]

The result is listed below and please record the App ReferenceId. The AppInstallationId,  DatabaseName, and TargetAppId will also be used in other restore steps.
  • AppInstallationId  DDBE8814-5CDF-48A4-9322-A53CAD392C17
  • DatabaseName      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • ReferenceId           0C904934-D29E-48A2-893E-C37472CB07AD
  • TargetAppId          AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • AppSource            4
  • SiteId                        A44B0A1A-0FF0-4470-9D43-40DB0C6891CE

3. Preparation step #3, we will need to get the access apps secure store entries from the secure store service DB. You could use the following SQL query on the secure store service DB.

SELECT TOP 1000 [ApplicationId]
      ,[PartitionId]
      ,[ApplicationName]
      ,[FriendlyName]
      ,[ApplicationType]
      ,[TicketTimeout]
      ,[ContactEmail]
      ,[CredentialManagementUrl]
FROM [SVC_SecureStore].[dbo].[SSSApplication]
Where PartitionId = '0C904934-D29E-48A2-893E-C37472CB07AD'  

Please note the PartitionId is the ReferenceId form step #2. The result is listed below.

ApplicationId                                 C5FD28F7-41A9-4980-8F94-4EC1D4E478BF
PartitionId                                      0C37852B-34D0-418E-91C6-2AC25AF4BE5B
ApplicationName                           AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
FriendlyName                                Credentials for App instance 'ddbe8814-5cdf-48a4-9322-a53cad392c17'
ApplicationType                             5
TicketTimeout                                NULL
ContactEmail                                  admin@admin.com
CredentialManagementUrl             NULL

4. Preparation step #4, we will need to get the access apps different entries from the app management store service DB. You could use the following SQL query on the secure store service DB.

4.1 Get AM_AppPrincipalAppInstances entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppInstanceId]
      ,[ProductId]
      ,[AssetId]
      ,[ContentMarket]
      ,[AppSource]
 FROM [SVC_APPS].[dbo].[AM_AppPrincipalAppInstances]
 WHERE AppInstanceId ='DDBE8814-5CDF-48A4-9322-A53CAD392C17'

The AppInstanceId is from the step#2. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
AppInstanceId               DDBE8814-5CDF-48A4-9322-A53CAD392C17
ProductId                       C181CBB8-0474-41C3-8C3C-6C86962E497D
AssetId                          NULL
ContentMarket              NULL
AppSource                    4

4.2 Get AM_AppPrincipals entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[Title]
      ,[RedirectUrl]
      ,[Realm]
      ,[Flag]
      ,[AppInstanceId]
      ,[DeploymentData]
      ,[DelegateAppPartitionKey]
  FROM [SVC_APPS].[dbo].[AM_AppPrincipals]
 WHERE Title = 'Harry-APP-Testing'

Please note the Title is the "App Name" from step #1. Be aware the same app names in the farm. In this case, you need to compare the CompositePartitionKey with the value from step 4.1. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
Title                                             Harry-APP-Testing
RedirectUrl                                 access:AuthRequestComplete
Realm                                          NULL
Flag                                              0
AppInstanceId                           NULL
DeploymentData                      NULL
DelegateAppPartitionKey       NULL


4.3 Get AM_ProductOAuthRegistration entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppId]
      ,[AppInstanceId]
FROM [SVC_APPS].[dbo].[AM_ProductOAuthRegistration]
WHERE AppInstanceId = 'DDBE8814-5CDF-48A4-9322-A53CAD392C17'

Please note the AppInstanceId is the value from step #2. The result is listed below.

CompositePartitionKey  0x2B85370CD0348E4191C62AC25AF4BE5B01B8CB81C17404C3418C3C6C86962E497D
AppId                      i:0i.t|ms.sp.int|7277a9a2-40ba-40e8-b5b3-ce8e6f95972b@98e9ba89-e1a1-4e38-9007-8bdabc25de1d
AppInstanceId         DDBE8814-5CDF-48A4-9322-A53CAD392C17

Now, we can delete the access app and start to restore the access app along with the site collection. Some steps are similar to we discussed in previous article.

1. Restore the access app logins - see previous blog.
2. Restore the access database
3. Restore the site collection content DB
4. Insert the entry from step #3 to secure store service database
5. Insert multiple entries from step #4 to three app management service database

This way, you should have the access app restored along with site collection in the same farm. At meantime, since we are not overwriting the whole secure store or app management services, instead  inserting entries related to the specific access app, this approach will restore only the access app deleted without impacting other apps!

Since we are query SharePoint databases and insert data into both secure store and app management databases, this might violate Microsoft SharePoint support contract. Be aware NOT to sue approach before consulting with your Microsoft support team.



1 comment: