SharePoint 2013: How to Restore Content Databases

Introduction

This posting walks you through the process of restoring one or more content databases to a SharePoint Server 2013 farm web application.  This procedure can be used to restore a production farm web application or even to "refresh" a 2013 development environment with the latest content from the production site, via just the production site content databases or a backup of these databases.  It assumes a farm having a single SQL Server database.

Procedure

1. Identify the web application that you want to restore.
2. Identify the content database(s) of this web application and their location.
3. Logon as administrator (not the farm administrator) to the SQL Server of the target farm.
4. Create a local folder, and copy to this folder the full backups of the content databases that you want to restore.
5. Logout.
6. Login as the Farm Setup User Administrator account (eg, spAdmin) to a SharePoint server of the target farm.
7. Launch Central Administration
8. Navigate to Application Management > Databases > Manage content databases.
9. Select the appropriate web application from the Web Application dropdown.
10. Click on the title of a content database.
11. Enable the option, Remove content database, and then click OK at the popup prompt.
12. Click OK again.
13. Logout.
14. Login to the target farm's SQL Server as administrator.
15. Launch SQL Server Management Studio with elevated privileges (right-click).
16. In Object Explorer, expand the tree to find the content databases.
17. Right-click, point to Tasks and then click Detach...
18. Select the database to be detached.
19. Enable the Drop Connections option, and then click OK.
20. Repeat for each content database associated with this web application.
21. Open Windows Explorer, and then navigate to the folder containing the SQL Server databases.  This will usually be of the form:

    [Drive]:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

22. Rename the content database(s) and associated transaction log files.
23. In SQL Server Management Studio, in Object Explorer, right-click Database, and then click Restore Database...
24. Select Device, and then click the ellipses "..."
25. Click the Add button.
26. Navigate to the folder where you placed the content database(s) that you want to restore.
27. Select a content database.
28. Click OK.
29. Click OK again.
30. Wait until the restore operation is completed.
31. In Object Explorer, right-click Databases, and then click Refresh.
32. Verify that the newly restored database is listed.
33. Repeat steps 23-32 for each content database.
34. Exit SQL Server Management Studio.
35. Logout of the SQL Server host.
36. Login, as the farm Setup User Administrator account to any SharePoint server in the target farm.
37. Launch an instance of the SharePoint 2013 Management Shell with elevated privileges (right-click).
38. Execute the following script for each content database you need to restore:

    Mount-SPContentDatabase "[content database name]" -DatabaseServer "[database server name or alias]" -WebApplication [URL]

    NOTE: use Mount-SPContentDatabase; not Restore-SPFarm.  You are "restoring" a content database, not a site.

39. After completing these mount operations, the restored web application should become available immediately.

References

• Restore content databases in SharePoint 2013
• Mount-SPContentDatabase
• Restore-SPFarm

Notes

• The terminology here is understandably confusing.  The procedure presented in this posting effectively accomplishes a restore operation.  However, technically speaking, you are really only performing unmount and mount operations on content databases. 
• Also, Attach and Detach operations performed on SQL Server accomplish different things  than performing these operations through Central Administration.  A Detach operation performed through Central Administration detaches the content database from the SharePoint farm; it does not detach it from SQL Server.
• Don't worry if your content databases contain multiple site collections distributed among multiple content databases that these won't be properly integrated back into one web application: just mount them all up and SharePoint Server will figure out the rest.
• While you can also mount the content databases through Central Administration, I have found from experience that after doing it this way, not all my site collections will become visible and I may have to unmount and mount a content database several times this way before things finally work out. Why this is, I don't know.