Restore UPA database in SharePoint 2010

Use SQL Server tools to restore the databases associated with the User Profile Service service application in SharePoint

You cannot restore the complete service application or service application proxy by using SQL Server tools. However, you can use SQL Server tools to restore the databases that are associated with the service application. To restore the complete service application, use either Windows PowerShell or Central Administration.

Please Note:

If you are restoring the User Profile database (by default, named User Profile Service_ProfileDB_), you must also restore the Social database (by default, named User Profile Service_SocialDB_). Failing to do so can cause inaccuracies in the User Profile data that might be difficult to detect and fix.

To restore the databases associated with the User Profile Service service application by using SQL Server tools

1. Verify that the user account you are using to restore the databases is a member of the SQL Server sysadmin fixed server role on the database server where each database is stored.
2. In Central Administration, in the System Settings section, click Manage services on server.
3. On the Services on Server page, find User Profile Service. If the service is started, click Stop to stop the service.
4. Before you restore the User Profile Service service application databases, you must import the Microsoft Identity Integration Server (MIIS) encryption key that you exported before backing up the databases. You only have to do this one time for the restore process. To do this, on the server to which you are restoring the service application, type the following at the command prompt, and then press ENTER:

5.    miiskmu.exe /i exported.key {0E19E162-827E-4077-82D4-E6ABD531636E}

Where is the identifier of the key.

6. Open SQL Server Management Studio and connect to the database server.
7. In Object Explorer, expand Databases.
8. Right-click the database that you want to restore, point to Tasks, and then click Restore Database.
9. In the Restore Database dialog box, on the Options page, select the kind of recovery that you want to perform from the Recovery state list.

For more information about which recovery type to use, see Overview of Recovery Models (http://go.microsoft.com/fwlink/p/?LinkId=114396) in SQL Server 2008 with SP1 and Cumulative Update 2 Books Online.

10. On the General page, in the Destination for restore section, select the database from the To database list.
11. In the Source for restore section, select the backup source from the From database list.
12. Alternatively, if you have moved the backup files to another computer, select the From device option. If the correct backup is not listed in the Select the backup sets to restore box, browse to the file by clicking the ellipsis button.
13. Select the backup to restore from the Select the backup sets to restore box, and then click OK.
14. Click OK to restore the database.
15. Repeat steps 5-11 for the following databases associated with the User Profile Service service application (the names listed are the default names):
• User Profile Service_ProfileDB_
• User Profile Service_SocialDB_
• User Profile Service_SyncDB_
16. In Central Administration, in the System Settings section, click Manage services on server.
17. On the Services on Server page, find User Profile Service. If the service is stopped, click Start to start the service.