Read time: 5 minutes
The SharePoint content database is a Microsoft SQL Server database that contains user content, documents, lists, metadata, and web part properties of a SharePoint site collection. Scroll down to learn about different SharePoint databases and how to restore a content database.
Database types in SharePoint Server
There are three types of databases in SharePoint Server , which save different kinds of content based on their functionalities
- Configuration Database: This database stores information about various aspects, including Internet Information Services (IIS) web sites, Web applications, Trusted solutions, Web part packages, Site templates, and Distributed cache configuration objects. This database needs to be co-located within the premises of the Central Administration database.
- Central Administration content database: The Central Administration content database is also a configuration database that stores configuration data for the Central Administration site collection. It needs to be located on the same database engine instance as the configuration database. The database is backed up when you save a backup of the SharePoint farm configuration and content.
- Content Database: The content databases store the entire site collection data, including document libraries, web part properties, audit logs, list information, sandboxed solutions, along with user names and rights. A content database can be linked to more than one site collection.
The given table shows the list of SQL Server versions supported for the SharePoint Server to host the database.
| SharePoint Server versions | Hosted SQL Server versions |
|---|---|
| SharePoint Server Subscription Edition | SQL Server 2022 and SQL Server 2019 CU5 |
| SharePoint Server 2019 | SQL Server 2016, SQL Server 2017, and SQL Server 2019 |
| SharePoint Server 2016 | SQL Server 2017, or SQL Server 2019, SQL Server 2016 and SQL Server 2014 Service Pack 1 (SP1) |
| SharePoint Server 2013 | SQL Server 2008 R2 Service Pack 1 (SP1) and SQL Server 2012, and SQL Server 2014 |
Methods to Restore SharePoint Content Database
The content database saves the majority of your SharePoint data. If you lose the database due to any corruption or unintentional deletion, then you can recover the data using different methods:
Restore SharePoint content database using Central Administration
To carry out this task, the account must hold membership in the SharePoint Farm Administrators Group. Once this condition is confirmed, proceed with the following steps:
- Open Central Administration and go to the Backup and Restore section. Here, click Restore from a Backup.
- Choose Backup to Restore page. Select the backup from the list of backups that has the content database. Click Next.
- Choose the Component to Restore page and click the box for the content database. Click Next.
- Select Restore Options from ‘Type of Restore’ and click Same Configuration. A confirm page asks you to validate your selection. Click OK.
- Click Click Start Restore..
Restore the SharePoint content database using PowerShell
To perform this action, you should be a member of the security admin fixed server role group and db_owner fixed database role group, and the Administrators Group. After ensuring this, use the steps below:
Open the SharePoint Management Shell and run the command:
It accesses the backup file and restores the database from it.
Restore and overwrite SharePoint content database using SQL server tools
Then, you can perform the database restore using the following steps:
- Make sure that your SharePoint account is a member of the sysadmin fixed server role.
- Stop the SharePoint Timer Service and wait for the current running stored procedures to finish.
- Connect to the database server with SQL Server Management Studio.
- Expand Databases, right-click the database for the restoration, and follow Tasks >> Restore >> Database.
- A Restore Database dialog box appears. Enter the destination and source accounts and select the backup set or sets.
- Click ‘Options’ in the ‘Select a page’ pane.
- Choose ‘Overwrite the existing database’ at the Restore options.
- At the ‘Recovery state’ section:
- Select ‘RECOVER WITH RECOVERY,’ if included all the transaction logs you need to restore.
- Select ‘RECOVER WITH NORECOVERY,’ to restore additional transaction logs.
- Click OK to complete the restore operation. Now, you can restart the SharePoint Timer service.
Limitations of native solutions to restore SharePoint content database
Here is why you shouldn’t solely rely on the manual solutions to restore content databases in SharePoint server:
- Once the data retention period expires, you cannot restore content databases without any external backup.
- No support to roll back individual files, list items, and other granular items without manual effort.
- Using native tools can show administrative errors and incompatible database versions.
- These methods can interrupt user productivity, as it involves often effect the access to SharePoint content.
Final Words: When Standard Recovery Methods Fall Short
With the solutions provided by Microsoft, you can restore SharePoint Server content database. However, if there’s database corruption and no corresponding backup is available, and you do not have technical expertise, these methods may prove ineffective. We suggest using Kernel for SharePoint Server Recovery in such situations to avoid further damage to the database or permanent loss of data. It will help you repair corrupt MDF database files using the two recovery modes. After the SharePoint database recovery, you can save the recovered file to a live SQL Server.
Common user queries
A. No, restoring a SharePoint content database is different from restoring a SharePoint farm.
SharePoint Content database: When you restore a database, it includes the site collection, lists, documents, and other user data within a database.
SharePoint farm: Restoring a SharePoint farm, it restores the entire environment. It includes Central admin settings, the configuration database, installed solutions, and all related content databases.
A. There are three native solutions provided by Microsoft to restore databases, which involve using SQL Server tools, Central Administration, and PowerShell.
A. 1. Stop the SharePoint Timer Service and wait until the active stored procedures are completed.
2. Take a quick safety backup of your current databases before starting the restore.
3. Halt the SharePoint Timer Service before beginning manual work in SQL Server Management Studio.
4. Make sure the account that is restoring has the necessary permissions without any extra administrative rights.
