Procedure to refresh SharePoint 2013 site collections with Access Apps and other SharePoint Apps to a different farm

The procedure to refresh all the site content along with SharePoint Apps, Access Apps, Security token, and Managed metadata from one SharePoint farm to a different one is almost identical to backup and restore on the same farm as we discussed in previous blogs. 

However, since the services should be already running on the target SharePoint farm, we could simplify the service refresh process by pointing the destination services to restored source databases instead of the original ones. In additional, since the destination SharePoint farm database might have different owner, you should modify the database owner to the destination owner after restore the databases.

 Here are the high level steps that is similar to previous blog.

1. Refresh App Management Service
•  Backup SharePoint 2013 App Management Service database in source SQL server
•  Restore the backed up SharePoint 2013 App Management Service database in destination SQL server
•  Change the database owner to the correct destination SQL server owner
•  Recreate App Management Service with the restored database in in destination SharePoint farm (Point the DB to restored DB does not seem to work like App Management Service)
2. Refresh App Catalog site
•  Backup SharePoint 2013 App Catalog site database in source SQL server
•  Mount App Catalog site to the correct WebApp in destination SQL server
•  Register the App Catalog site with App Management Service in destination SharePoint farm
3. Refresh Security Store Service - you do not need this is security store not used on the site
•  Backup SharePoint 2013 Security Store Service database in source SQL server
•  Restore SharePoint 2013 Security Store Service database in destination SQL server
•  Change the database owner to the correct destination SQL server owner
•  Point Security Store Service to the restored service database
4.  Refresh Management Metadata Service
•  Backup SharePoint 2013 Management Metadata Service database in source SQL server
•  Restore SharePoint 2013 Management Metadata Service database in destination SQL server
•  Change the database owner to the correct destination SQL server owner
•  Recreate Management Metadata Service with the restored service database (Point the DB to restored DB does not seem to work like App Management Service)
5. Restore Site collections
•  Backup SharePoint 2013 site collection content database in source SQL server
•  Restore SharePoint 2013 site collection content database in destination SQL server
•  Change the database owner to the correct destination SQL server owner
•  Add the new app to the restored site collection when adding the Access app
6. Restore Access Apps
•  Save the each Access App from the site collection as .app file for new App
•  Add the new app to the restored site collection when adding the Access app

You can use sp_changedbowner or alternaltely use ALTER AUTHORIZATION statement. Please not sp_changedbowner is deprecated from SQL Server 2012.

Exec sb_changeowner ‘NewOwner’

ALTER AUTHORIZATION ON DATABASE::MyDatabaseName TO NewOwner;

GO

Now the key is to identify where are all the Access Apps so we could package from the source SharePoint farm. I've publish the database query to identify on each content DB.

SELECT DISTINCT TOP 1000

      AIP.[Value]

      ,(SELECT [WSS_Content_P2].[dbo].[AppInstallationProperty].[Value]

        FROM [WSS_Content_P2].[dbo].[AppInstallationProperty]

        WHERE  [WSS_Content_P2].[dbo].[AppInstallationProperty].[ValueKey] = 'DbName'

        and [WSS_Content_P2].[dbo].[AppInstallationProperty].[InstallationId] = AIP.[InstallationId] ) as DbName

      ,AP.[LaunchUrl]

      ,AIP.[SiteId]

      ,AIP.[InstallationId]

        

  FROM [WSS_Content_P2].[dbo].[AppInstallationProperty] as AIP 

  INNER JOIN [WSS_Content_P2].[dbo].[AppInstallations] as AP

  on AIP.[InstallationId] = AP.[Id]

  and AIP.[ValueKey] = 'containerWebName'

 You could check other detailed steps and screenshot in previous blog.

Procedure to backup/restore SharePoint 2013 site collections with Access Apps and other SharePoint Apps in the same farm - Details

As we described in previous blog, in order to recover all the site content along with SharePoint Apps, Access Apps, Security token, and Managed metadata on the site from the same farm, we will need to restore the following services.

1. Restore App Management Service
•  Backup SharePoint 2013 App Management Service database in SQL server
•  Restore SharePoint 2013 App Management Service database in SQL server
•  Create a new SharePoint 2013 App Management Service using the restored service database
2. Restore App Catalog site
•  Restore SharePoint 2013 App Catalog site database in SQL server
•  Mount App Catalog site to the correct WebApp
•  Register the App Catalog site with App Management Service
3. Restore Security Store Service - you do not need this is security store not used on the site
•  Backup SharePoint 2013 Security Store Service database in SQL server
•  Restore SharePoint 2013 Security Store Service database in SQL server
•  Create a new SharePoint 2013 Security Store Service using the restored service database
•  Recover existing information using the SharePoint Security Store passphrase
4. Restore Management Metadata Service
•  Backup SharePoint 2013 Management Metadata Service database in SQL server
•  Restore SharePoint 2013 Management Metadata Service database in SQL server
•  Create a new SharePoint 2013 Management Metadata Service using the restored service database
5. Restore the site collections
• Backup SharePoint 2013 site collection database in source SQL server 
• Restore SharePoint 2013 site collection database in destination SQL server
• Change the database owner to the correct destination SQL server owner
• Mount-SPContentdatabase to the correct Webapp 
6. Restore Access Apps
• Save the each Access App from the site collection as .app file for new App
• Add the new app to the restored site collection when adding the Access app

If you do not need to know how to setup the test site and test cases to simulate service or sites are removed from the farm that need to be restored, you could jump to step ten directly.

In order to test the backup and restore process, the first step we will set up the test cases as in the following details as described in Microsoft technical articles.

1. Create App Management service

2. Create and configure Access services

3. Create Security Store services

4. Create management metadata services 

The second step, we will create a App catalog site and add some approved apps using the powershell commands.

New-SPContentDatabase SVC_AppCatalog_SP  -WebApplication http://sp2013srv/

$template = Get-SPWebTemplate “APPCATALOG#0”

New-SPSite -Url “http://sp2013srv/sites/AppCatalog” -OwnerAlias “AD2012\SPFarm” -Name "SharePoint App Catalog" -Template $template –ContentDatabase SVC_AppCatalog_SP

Update-SPAppCatalogConfiguration -site “http://sp2013srv/sites/AppCatalog”

 

The third step we create test site with SharePoint Apps and managed metadata. Add one Project Management Apps that is an Access App from SharePoint store

$template1 = Get-SPWebTemplate “STS#0”

New-SPContentDatabase WSS_Content_AccessApp  -WebApplication http://sp2013srv/

New-SPSite -Url “http://sp2013srv/sites/AccessApp1” -OwnerAlias “AD2012\SPFarm” -Name "Access App Site" -Template $template1 –ContentDatabase WSS_Content_AccessApp

 

The forth step we create one custom Access App named "AccessExample" on the test site. Add one SharePoint App named "Shreyan Pool App" and "Project Management" app that is a Access App.

After all the configuration, you will see the App Management service will contain the following added app licenses through Central Administration->Apps->Manage App Licenses.

App Management service has the following DNS configured.

The App catalog will have one App request pending approval.

The site has three Apps installed and one is the Access App as in the following screen shots.

 

 

We will have the following databases that will be used in the backup and restore steps.

• App_Management_DB - App management service
• MetadataDB1 - Management Metadata service
• Secure_Store_Service_DB - security store service
• SVC_AppCatalog_SP - App catalog site content DB
• WSS_Content_AccessApp - Test site content DB
• db_21c95a10_5e9c_45bb_b4a0_732f806ce873 - Custom Access App database
• db_f10f131b_c12_485d_b358_551d829325bc - Project Management Access App from SharePoint store

The fifth step we will backup all the databases listed above except the two Access databases. You could right click the database ->Tasks->Backup. You should have FIVE database backup files.

The sixth step we will save the two Access Apps as packages from Access client. Open the Access Apps from Access client -> Settings->Customize in Access->Open->Save As ->Save As Package. Please to enter the same package name when you save the package. The package name will be the access app named restored. You will have TWO.app packages.

The seventh step is to delete the THREE service proxy and services including App Management service, Security Store service, Management Metadata service. You could keeo the database when you remove the services.

The eighth step is to remove the database related to the THREE services list above. Right click the database and delete the database. Now we have simulated the services and databases associated with them are gone.

The ninth step is to delete the test site collection and App catalog site and remove the databases from central admin. Then remove the two databases from database.

You need to run the following command to remove the deleted site from graduate delete queue. Then run  “Gradual Site Deletion” immediately to delete the sites that were marked for deletion.

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

Now we have simulated the test site and app catalog site are gone.

The following steps from step tenth to fifteenth are restore steps. 

The tenth step is to backup all the FIVE databases backed up from step five. We will have all the service database with original data.

The eleventh step is to recreate the services and proxies using the restored database. I've listed the powershell examples.

# Create App Management Service and Proxy from previous database

$account = Get-SPManagedAccount AD2012\Administrator

$appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account

 

$appAppSvc = New-SPAppManagementServiceApplication -ApplicationPool $appPoolAppSvc -Name “App Management Service App” -DatabaseName App_Management_DB

$proxyAppSvc = New-SPAppManagementServiceApplicationProxy -Name "App Management Service App Proxy" -ServiceApplication $appAppSvc -DefaultProxyGroup

#Start the App Management Servic

Get-SPServiceInstance | ? TypeName -EQ 'App Management Service App' | Start-SPServiceInstance

# Create Security Store Service and Proxy from previous database

$account = Get-SPManagedAccount AD2012\Administrator

$appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account

 

$SSSvc = New-SPSecureStoreServiceApplication -ApplicationPool $appPoolAppSvc -Name “Secure Store Service” -DatabaseName Secure_Store_Service_DB -AuditingEnabled

$SSProxy = New-SPSecureStoreServiceApplicationProxy -Name "Secure Store Service Proxy" -ServiceApplication $SSSvc -DefaultProxyGroup

# Restore the passphrase for Security Store service application

Update-SPSecurieStoreApplicationServerKey -Passphrase 'Pass@word1' -ServiceApplicationProxy $SSProxy

#Start the Security Store service

Get-SPServiceInstance | ? TypeName -EQ 'Secure Store Service' | Start-SPServiceInstance

# Create Managed Metadata Service and Proxy from previous database

$account = Get-SPManagedAccount AD2012\Administrator

$appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account

 

$MMSvc = New-SPMetadataServiceApplication -ApplicationPool $appPoolAppSvc -Name “MetadataServiceApp1” -DatabaseName metadataDB1 -AdministratorAccount 'AD2012\Administrator' -FullAccessAccount 'AD2012\SPFarm, AD2012\SPServiceAppPool"

$SSProxy = New-SPMetadataServiceApplicationProxy -Name "MetadataServiceApp1 Proxy" -ServiceApplication “MetadataServiceApp1” -DefaultProxyGroup

#Start the Management Metadata service

Get-SPServiceInstance | ? TypeName -EQ 'MetadataServiceApp1' | Start-SPServiceInstance

Please note it's critical to add the installer SharePoint system account in the -FullAccessAccount. Otherwise, you will receive the following error when you try to open the managed metadata service.

“The managed Metadata Service or Connection is currently not available. The Application Pool or Managed Metadata Web Service may not have been started. Please Contact your Administrator.”

Step twelve is to associate the new created services to the webapp from central admin. 

Step thirteen is to back up the app catalog site collection and test site collection using the following powershell commands.

Mount-SPContentdatabase -name WSS_Content_AccessApp -Webapplication http://sp2013srv/
Mount-SPContentdatabase -name SVC_AppCatalog_SP -Webapplication http://sp2013srv/

Step fourteen is to update catalog configuration so app catalog site can be seen from central admin.
Update-SPAppCatalogConfiguration -site “http://sp2013srv/sites/AppCatalog”

Step fifteen is to recreate the Access App by adding the access app and upload the .app package. Site Settings->Add anapp->Access App->Upload an Access .app package.

At this point, we have restored all services from backup databases, app catalog site, and the test site collection. Everything is still working as expected as before with the following exceptions.

1. The Project Management Access App icon changed from original icon from SharePoint Store to default Access App icon as in the following screenshot.
2. The two Access Apps have been redeployed to the test site collection. As a result, the Access database ID changes and you could observe from database side. 

We are still working with Microsoft to verify if we could have better way to restore Access Apps directly from database instead of redeploying them from .app packages. We will keep you posted any progress.