Managing SharePoint Content Databases with PowerShell

By:   |   Updated: 2012-02-08   |   Comments (11)   |   Related: > SharePoint


Problem

I'm a DBA and new to administering SharePoint databases.  In particular I'm focusing on the content databases and I want to get a handle on database administration for these databases.  In my shop there are quite a few of them, they're pretty big and growing rapidly.  Can you provide the details on what I need to get these databases under control?  Check out this tip to learn more.

Solution

The content database is probably the most important database in SharePoint.   It holds the majority of the data that users work with.  As you have stated there can be quite a few of them and without proper planning they can become very large.  In this tip I will go through the main points that a DBA should become familiar with in order to manage the content databases effectively.  While you can use SharePoint Central Administration for some of the things you need to do to manage content databases, I think PowerShell is the best tool for managing content databases so I'll use it throughout this tip.  

There are a number of PowerShell database cmdlets provided by SharePoint; in this tip I will cover the ones that deal with content databases.  The following is the outline for this tip:

If you choose to use SharePoint Central Administration to manage content databases, you'll use the Manage content databases option in the screenshot below.  I will cover using Central Administration to manage content databases in a future tip.

SharePoint 2010 Central Administrator

PowerShell 101

There are quite a few tips on using PowerShell to perform various SQL Server tasks; take a look at the PowerShell tips category on MSSQLTips.com for some great content.   For managing SharePoint content databases, you have two options available in Windows Server 2008:

  • SharePoint 2010 Management Shell
  • Windows PowerShell ISE

The SharePoint 2010 Management Shell is installed automatically by SharePoint; you can find it in the Microsoft SharePoint 2010 Products program group.  The user interface is pretty much like the Command Prompt; it is simple and effective.  The Windows PowerShell ISE is what I prefer; it is a GUI tool that's a little more polished than the SharePoint 2010 Management Shell.  A good SQL Server analogy would be SQLCMD versus SQL Server Management Studio.

Windows PowerShell ISE is not installed by default on your Windows 2008 server; follow these steps to install it:

  • Go to Server Manager, Add Features
  • Check Windows PowerShell Integrated Scripting Environment (ISE)

After installing Windows PowerShell ISE, you can launch it from the Accessories / Windows PowerShell program group.  There is one additional step that you need to perform in order to use it with SharePoint; you need to load the Microsoft.SharePoint.PowerShell snap-in.  There are two ways to do this:

  • Run the PowerShell command Add-PSSnapin Microsoft.SharePoint.PowerShell (every time you open PowerShell ISE)
  • Add the snap-in in the profile file; e.g. C:\Windows\System32\WindowsPowerShell\v1.0\profile.ps1 (one time)

The following is an example of the code you can put in the profile to add the snap-in:

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
  }

There is one more thing you need and that is proper permission to use PowerShell to update a content database and the farm configuration database; you need to be a member of the SharePoint_Shell_Access database role in the respective databases.  You will need a SharePoint farm admin to add you to this role using the Add-SPShellAdmin cmdlet.

Add a user to the SharePoint_Shell_Access role in the farm configuration database only:

Add-SPShellAdmin-UserName domain\username

Add a user to the SharePoint_Shell_Access role in the farm configuration database and content database specified:

Add-SPShellAdmin -UserName DOMAIN\USERNAME -Database DATABASENAME

Add a user to the SharePoint_Shell_Access role in the farm configuration database and every content database:

Get-SPContentDatabase | Add-SPShellAdmin -UserName DOMAIN\USERNAME

Introduction

Here are a couple of points before we get in to the details:

  • If you are new to SharePoint 2010, take a look at our earlier tip Introduction to SharePoint 2010 for DBAs.  In the sections that follow the concepts presented in the earlier tip are fundamental to your understanding.
  • A content database can either be attached to a web application or detached; where a cmdlet works with a content database that is detached you will see the -Name parameter; when a cmdlet works with a content database that is attached to a web application you will see the Identity parameter; the Identity parameter can be the database name, its Id (GUID or UNIQUEIDENTIFIER as returned by Get-SPContentDatabase), or a SPContentDatabase object in the PowerShell pipeline.
  • Along the same lines you don't specify the DatabaseServer parameter when a content database is attached to a web application; SharePoint knows where it is.
  • In the sample code when I use contentdb I mean the database name
  • I'm running the PowerShell cmdlets on a Windows 2008 server that is connected to a SharePoint farm; I'm not going in to running the cmdlets remotely.

Get-SPContentDatabase

Get-SPContentDatabase returns information about one or more content databases in the SharePoint farm.  The server where you run the command is connected to a particular SharePoint farm so the available databases include only those in the farm. 

To show information about all databases in the farm, run the Get-SPContentDatabase cmdlet without any parameters.  The output is (only one database shown):

Id : 2fb2c72c-e098-4405-9d39-fefd3686fb67
Name : WSS_Content
WebApplication : SPWebApplication Name=SharePoint - 80
Server : DEV-FARM
CurrentSiteCount : 2

Server is the name of the database server; the current site count is the number of site collections in the content database.  To get information about a particular database you can add the -Identity contentdb parameter.

New-SPContentDatabase

To create a new content database run the New-SPContentDatabase cmdlet.  I would contend that this is the most important of the database cmdlets as it allows you to create content databases that you can then use for specific site collections.  A typical command to create a new content database would be:

New-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname `
  -MaxSiteCount 1 -WarningSiteCount 0

The funny character at the end of the first line above is the one to the left of the number 1 on your keyboard; it is the PowerShell line continuation character (a very poor choice in my opinion).  Note that the content database is associated with or owned by a web application.  You will likely need to get the web application name from your SharePoint administrator.  An example web application name would be http://intranet.yourdomainname.com. 

You can (and probably should) specify the MaxSiteCount parameter to limit the number of site collections that can be stored in this content database; the default value is 15,000.  I prefer to set the MaxSiteCount to 1 so that someone using SharePoint Central Administration to create a new site collection doesn't inadvertently put in in this content database.  The MaxSiteCount can be changed with the Set-SPContentDatabase cmdlet (described later) or in Central Administration.

Creating a content database is the first step in proactively managing your content databases.  The next step is to specify which site collection(s) are to be stored in the content database.  Your SharePoint administrator should use the New-SPSite cmdlet along with the ContentDatabase parameter when creating a new site collection in order to specify the content database for the site collection.  If you are using Central Administration to create site collections you cannot directly specify the content database for the site collection; you can make sure that only one content database is available by setting the MaxSiteCount or Database Status (described later).

Dismount-SPContentDatabase

Dismount-SPContentDatabase detaches a content database from the web application.  You would do this if you wanted to move the content database to a different web application and/or a different database server.  The operation updates the farm configuration database but otherwise leaves the content database intact in the SQL Server instance.   

A typical command to detach a database would be:

Dismount-SPContentDatabase -Identity contentdb

Mount-SPContentDatabase

Mount-SPContentDatabase is the inverse of Dismount-SPContentDatabase.  In this case you are attaching a content database to a web application.  This would be the case if you ran Dismount-SPContentDatabase and are now ready to attach the content database to a different web application or you moved a detached content database from another database server. 

A typical command to attach a content database would be:

Mount-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname

Mount-SPContentDatabase can also be used when you upgrade a SharePoint 2007 database to SharePoint 2010; this is referred to on TechNet as the database attach upgrade method  The database will be upgraded to the SharePoint 2010 schema and attached to the web application.  You will get a list of any errors or warnings generated by the upgrade.  Typical errors have to do with customizations referenced in the content database that are not in the web application.  I will not cover those issues here as the DBA isn't usually (hopefully) the person who needs to resolve them.

Remove-SPContentDatabase

Remove-SPContentDatabase detaches a content database from the web application and drops it from the SQL Server instance.  As a general rule I don't use this one.  Even if I do want to drop the database I use Dismount-SPContentDatabase then go to SQL Server Management Studio and drop the database. 

A typical command to detach a content database and drop it would be:

Remove-SPContentDatabase -Identity contentdb

Set-SPContentDatabase

Set-SPContentDatabase is used to set the properties of the content database.  Typical properties to set are:

  • MaxSiteCount - the maximum number of site collections that can be stored in the content database
  • WarningSiteCount - the number of site collections when a warning notification is sent
  • Status - Disabled (no site collections can be added) or OnLine (site collections can be added as long as the MaxSiteCount hasn't been reached)

A typical command to disable new site collections from being added to the content database would be:

Set-SPContentDatabase -Identity contentdb -Status Disabled

A typical command to enable new site collections to be added to the content database would be:

Set-SPContentDatabase -Identity contentdb -Status OnLine

As an aside there is a little bit on inconsistency with the content database Status in Central Administration versus the Set-SPContentDatabase cmdlet.  If you select the Manage content databases option from Central administration, you will see a list of content databases with a Database Status column that has the values Started or Stopped.  When you select a database to view or modify its properties, you will see a Database Status dropdown with the options Ready and Offline.

Test-SPContentDatabase

Test-SPContentDatabase will check a content database against a web application to determine whether all of the customizations referenced in the content database are available in the web application.  This cmdlet can check a content database that is attached to a web application or one that is detached.  In either case the output of the command is a list of errors and warnings which hopefully will be resolved by someone other than the DBA.  This is the same list that Mount-SPContentDatabase generates when you are attaching a SharePoint 2007 content database to SharePoint 2010.

A typical command to test a content database that is attached to a web application would be:

Test-SPContentDatabase -Identity contentdb

A typical command to test a content database that is not attached to a web application would be:

Test-SPContentDatabase -Name contentdb -WebApplication webappname
Next Steps
  • If you need to manage content databases in SharePoint 2010, I would highly recommend that you consider doing it with the PowerShell cmdlets covered in this tip.  I think PowerShell is really the way to go.  You can't perform any of these functions in SQL Server Management Studio because the SharePoint configuration database is being updated when you add, attach, or detach a content database.
  • Stay tuned for future tips that cover database additional administration topics related to SharePoint databases.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-02-08

Comments For This Article




Monday, April 28, 2014 - 6:36:11 PM - Ray Barley Back To Top (30539)

If you're asking what query do I run then according to this KB article http://support.microsoft.com/kb/841057 you are not supposed to be querying any SharePoint database directly; e.g. open SQL Server Management Studio, connect to the database server and run a query.  See the "Read operations addendum" section of the KB article.

Take a look at this tip http://www.mssqltips.com/sqlservertip/2629/managing-sharepoint-content-databases-with-central-administration/ which goes in to some details on SharePoint Central Administration.  When you create a web application you also create a content dataase and specify which SQL Server to use.  From a web application you can drill in to the list of its content databases.  Not exactly what you asked but it may work.

Friday, April 25, 2014 - 7:31:16 PM - Javier Back To Top (30525)

 

If there any query to get the information regarding which server a content database is attach to? I have the database but I want to know to which sharepoint server is attach to that content database

Wednesday, August 7, 2013 - 10:41:46 AM - John Back To Top (26133)

You can also install the PowerShell ISE from PowerShell:

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

Tuesday, July 2, 2013 - 11:26:52 PM - parv Back To Top (25684)

Thanks Raymond for your reply I solved that issue.

Tuesday, July 2, 2013 - 10:18:30 AM - Raymond Barley Back To Top (25677)

Maybe it's something simple like you are using the wrong URL to try and access the list.

You will have to list all the steps that you have performed in order for someone to point out something you missed.

 

Tuesday, July 2, 2013 - 9:29:56 AM - parv Back To Top (25676)

I followed all the steps but after adding Content DB in sharepoint am not able to see existing list ad library .

can you please tell what am missing?

Monday, March 4, 2013 - 10:57:24 AM - Raymond Barley Back To Top (22550)

Take a look at this TechNet article:

http://technet.microsoft.com/en-us/library/cc512725(v=office.14).aspx

 

Sunday, March 3, 2013 - 11:16:56 PM - sathish Back To Top (22542)

Hi Barley,

 

Its realy an useful information which you have shared, could you please share information about moving sharepoint config databases from one server to other.

 

Thanks in Advance.

Wednesday, August 15, 2012 - 3:02:09 PM - Carl Grzywacz Back To Top (19053)

*Note - The 'Get-SPContentDatabase' cmdlet only returns Content DBs with a status of 'Started' if you don’t explicitly provide a DB name


Example:

Set all DBs to stopped.

Get-SPContentDatabase  | Set-SPContentDatabase -Status Disabled

If you then try to set the status back to Started, the script won't do anything because no DBs are returned.

Get-SPContentDatabase | Set-SPContentDatabase -Status Online

Wednesday, May 30, 2012 - 4:21:54 PM - Ray Barley Back To Top (17737)

No.  

 

Grasping at straws here but did you close the browser and open it again then navigate to central admin and check the content databases?  No reason you should have to do an IISRESET but in general it doesn't hurt anything in a development environment.

If you can see the newly created content database from Get-SPContentDatabase, try dismounting it then mounting it.  I'm wondering if some sort of error message may show up. 

Wednesday, May 30, 2012 - 1:58:46 PM - Michael Back To Top (17732)

Ray,

I created a content db with Powershell.  Get-SPContentDatabase returns all of my db's including the newly created one with a status of online however the PS created db is not visible in Central Admin (under any of my web apps).  Have you seen this behavior?

Thanks














get free sql tips
agree to terms