Procedure to restore SharePoint 2013 site with access apps in the same farm from database backups

In March 2017, Microsoft announced that they will no longer recommend Access Services for new apps and web databases. When Access Services in SharePoint is first introduced in SharePoint 2013, the mission was to enable both information workers and developers to quickly create data eccentric web applications with little or no programming. Over the last several years it has become clear that the needs of our customers have grown beyond the scope of what Access Services can offer. One of the major issue we are facing is one of our  Access Service App is bigger that could not be packaged as app package. We are not be able to make any changes that could be saved or packaged. The worst case is there is no disaster recover procedure we could  recover the Access Service App along with the site collection.

Although Microsoft is recommending Microsoft PowerApps as alternative that offers a comprehensive set of application building tools, connection to custom web APIs, and a wide array of database options including SharePoint lists, SQL Azure databases, Common Data Service and third-party data sources, there is no clear way to convert existing Access Service App. While we are migrating existing Access Service Apps, we still need to support the current Access Service Apps in production. In this article, I’ll describe the way how you could restore the Access Service Apps from database backups along with the site collections using Microsoft way. You could use app package as access app backup and restore if the app is small enough to be saved as .app package. In different article, I’ll also provide a way that might not be supported by Microsoft but for your reference.

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. The disaster recover methods are different different scenarios of the site with access app restore.

Here is the summery of the site collection and farm information needed for restore. I'll explain the details how to get this information in the following sessions.

• Site collection  URL                  http://spsbx15/sites/APP-DR
• Site collection DB                  WSS_Content_APP-DR
• Access App                            Harry-APP-Testing
• Access App DB                      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
• App manager service DB        SVC_APPS
• Security Store service DB       SVC_SecureStore
• Access App logins                    db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo 

                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom
                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWriter

All the SharePoint components related to access app for the site collection and the relationships are illustrated in the below diagram.

1. The first scenario is the site collection content corrupted or deleted, we want to recover the site along with Access App. 

In order to test this scenario, we delete the site collection “http://spsbx15/sites/APP-DR”. Here is the way to restore.

• Get the site content database WSS_Content_APP-DR from backup
• Restore the content database to SharePoint SQL database
• Mount the database using the Powershell

         Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 -WebApplication        

         http://spsbx15

If the site collection had been just deleted within few hours and the SharePoint daily graduate site deletion timer job has not started (~6AM), you can quickly restore the site using the following steps.

        # Get the site GUID using Powershell. Record the site GUID

        Get-spdeletedsite -webapplication http://spsbx15

        # Restore the site using Powershell by passing the site GUID

        Restore-SPDeletedSite -Identity 610857cb-8414-4a89-8bf3-ad3628f6c86c

2. The second scenario Access App content corrupted, we want to restore the Access App to the same site. Here are the steps.

         Get the Access App database from backup. Please note the naming conversion to the Access App DB

         Restore the Access App database like “db_ddbe8814_5cdf_48a4_9322_a53cad392c17”

3. The third scenario is Access App deleted, we want to restore the Access App to the same site. Here are the steps before and after the Access App deletion.

As daily SharePoint back up process, please back up the following databases and logins.

• Back up the Access App logins daily using the script provided by Microsoft.
• Back up the site collection databases
• Back up the access app databases
• Back up the app manager service database
• Back up security store service database

Whence we need to restore the site collection with access app, here is the procedure.

• Restore the access logins using the script provided by Microsoft. The logins will be recreated with same SID and passwords. The logins are prefixed as "db_" and then access app ID. db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWrite

• Restore the access app database db_ddbe8814_5cdf_48a4_9322_a53cad392c17
• Restore the app manager service database SVC_APPS. This can be done when SharePoint is running.
• Restore the security store database SVC_SecureStore. You need to stop IIS  by IISRESET /stop before the database restore.
• Restore the site collection database WSS_Content_APP-DR and mount the site again

The last step to restore the site collection is tricky since the database cannot be restored if the current database is still be used. Here is the way to do that.

• Delete the site collection from CA
• Remove the deleted site from pool using Powershell

           get-spdeletedsite -webapplication http://spsbx15 | Remove-SPDeletedSite 

• Run “Gradual Site Deletion” immediately to delete the sites that were marked for deletion 
• Close any browser hitting the site, close all SQL query using the database
• Conduct a IISRESET
• Delete the site collection content database from SQL - You may still need to try few time until all connections dropped to the database
• Restore the previous site collection content database WSS_Content_APP-DR 
• Mount the database to SharePoint using Powershell

       Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 

       -WebApplication http://spsbx15

Now you have the site collection along with working access app from previous back up!

Please note there are potential issues for the third scenario. The restore will overwrite the entire app manager service and security store service!  What will be impacted? Here are the potential risks you need to manage.

1. If new access apps have been added to some site during the time, these will NOT be recovered. These access app will be orphan on the sites!
2. If new security store entry has been added or existing security entry has been modified, these will NOT be recovered. You will have previous values!

Since we will disable access app creation, we can eliminate the risk #1. We control the security store entry update and have Powershell script to retrieve the entries, we can eliminate the risk #2. However, if you like my access restore procedure, please be aware the risks.

At this time, you might ask, is there anyway we could recover the site along with access without overwrite the entire app management service and security store service?  The answer is YES! I'll provide the details in another blog. However, that approach will involve SQL database insert to both app management service and security store service databases. This seems to be unsupported by Microsoft.

I'm working with Microsoft to see if there is anyway we can restore app and site without overwrite the app management service and security store service.