SharePoint 2010: Rename the SharePoint State Service Database

Continuing on a quest to rename everything in SharePoint. You can find the first article here.

Rename the SharePoint State Service Database

The State Service in SharePoint 2010 is a service application that manages user state across browser requests. The SharePoint State Service is required by InfoPath Forms Services, the Chart Web Part, and certain Visio 2010 scenarios. You can find the State Service in Central Admin under Application Management – Manage Service Applications. There will be one or more State Service applications and associated proxies.

As usual, if the state service was created by the SharePoint Configuration Wizard, or you create it without editing the database name, the database will be created with a GUID on the end, such as:

StateService_5536604e93df4369a916cf408e0c3866  (ew!)

So let’s check out our state service in PowerShell. Open the SharePoint 2010 Management Shell (Start –> All Programs –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Management Shell). To start, try a simple:

Get-SPDatabase | Select Name

This will list the SharePoint databases, and you should easily spot your State Service database. If for some reason you don’t know the name of the State Service Database, you can list all state service databases with this command:

Get-SPDatabase | Where-Object { $_.Type -eq "Microsoft.Office.Server.Administration.StateDatabase" }

Every SharePoint object has an ID (a GUID), and the easiest way to get a reference to a SharePoint object is by using that ID. You can get the ID for databases with this command:

Get-SPDatabase | SELECT Name, ID

Once you find the ID for your State Service Database, you can get a handle on it with this command:

$db=Get-SPDatabase -id {guid}

Next (and this is a common step with SharePoint Service databases; the trick is always figuring out how) you have to dismount the database:

Dismount-SPStateServiceDatabase -Identity <DatabaseID>

For “DatabaseID” you can either paste in the GUID we copied earlier, or you can use the $db variable as a hook to the database itself. $db.Id won’t work unless you cast it to a string first.

You’ll be asked to verify that you want to do this:

Are you sure you want to perform this action?
Performing operation "Dismount-SPStateServiceDatabase" on Target "SP_Dev_StateService_DB".
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "Y"):

A simple “Y” and enter will dismount the database. Now you can go to SSMS to rename the database — right-click on the database and select “Rename” or in T-SQL:


After you’ve renamed the database, then mount it again with the command

Mount-SPStateServiceDatabase -Name "<DatabaseName>" -DatabaseServer "<ServerName>"

And you’re all set.

If you completely screw up the database somehow, or feel it’s corrupted, there’s no critical data in the database – simply delete it and create a new one with the command:

New-SPStateServiceDatabase -Name "<StateServiceDatabase>" -ServiceApplication $serviceApp

Which will create a state service database and associate it with the State Service.

SharePoint 2010: Rename Content Databases and the Configuration Database

SharePoint 2010 Rename Content Database and the Configuration Database: This is part 2 of an attempt to create an omnibus resource for renaming things in SharePoint. Part 1 (and the index) can be found here:

How to Rename SharePoint 2010… Everything!

Configuration Database

Don’t rename the configuration database. If you think you want to rename it, then don’t. There are a few articles that discuss detaching the database to rename it. Personally I’ve tried this three times and failed three times; and the price of failure is rebuilding your farm from scratch.

(Note that there can be more to this than just the farm name – the farm also encrypts data based on the farm password, and I believe changing the name of the Configuration Database may affect this. Since renaming the configuration database isn’t supported by Microsoft, it’s pretty tough to dig out information on this)

Content Databases

Next on the list are the content databases. Again let me reiterate – back up your farm before embarking on these changes…

To rename a content database, we’re going to remove the database in SharePoint, then detach the database in SQL Server, and reattach it with the new name. Finally, we’ll reattach the database.

Take the Content Database offline

  1. Open Central Admin
  2. Application Management –> Databases / Manage content databases
  3. Make sure you have your web application selected in the top right, then click on the content database.
  4. In the Content Database Settings page, change the database status from “Ready” to “Offline” then click “OK”
  5. Go back to the Content Database Settings page again, and check the box for “Remove content database” – note this means to remove the association from SharePoint; it doesn’t delete the database.

Rename the Database in SQL Server

  1. Open SQL Server Management Studio and find the database.
  2. Right-click on the database, select “Tasks” then “Detach…”
  3. In the Detach Database dialog, check “Drop Connections” but do not check “Update Statistics.” Then click “OK”
  4. Open Explorer and find the mdf and ldf files for the database.
  5. Rename the two files to matching names complying with your naming standard (in my case I changed WSS_Content_{GUID}.mdf to WSS_Content_01.mdf)
  6. Right-click on the “Databases” folder in SSMS, and click “Attach”
  7. Click the “Add…” button, then select the (new) mdf file, then click “OK”
  8. You’ll have to change the “Attach As,” Data, and Log file paths:
  9. Click “OK”
  10. Your database should now show up in SSMS – refresh the Object Explorer pane if it doesn’t.

Add the Content Database Back to SharePoint 2010

  1. Now back to Central Admin, and “Manage Content Databases.”
  2. Click “Add a content database”
  3. Enter the Database Name you created, then click “OK”
  4. Once that’s complete, you’ll see the database listed, and if you run “get-spdatabase” in the SharePoint 2010 Management Shell, we should have one GUID less to deal with!

Renaming Central Admin Content Database

So now you may have two questions:

1) What was all that talk about using PowerShell? We did this in Central Admin!

2) And hey – how do I rename the Central Administration content database?

This section should address both of those concerns – we’ll cover renaming the Central Administration content database in PowerShell, which will also show you how to rename any other content database in PowerShell. (Which gets important when you have a large number of databases to rename…)

What we’re going to do is create a new content database with the name we want, then move the sites from the existing content database into the new one, and finally delete the old content database.

Create a new Content Database

This is simply one PowerShell cmdlet:

New-SPContentDatabase -Name SharePoint_CA
     -WebApplication http://server:CentralAdminPort

This command provisions a database in the Farm’s SQL Server, then mounts it as a content database in SharePoint for the Central Admin web application. Now we need to move the Central Admin site from the existing content database to the one we just created. To do this we need the IDs (GUIDs, not the names) of the respective databases.

You can see the databases with a Get-SPDatabase, but if you try Get-SPContentDatabase then you’ll see other content databases in the farm, but you won’t see the Central Admin content databases. Central Admin content databases are excluded from listing in the Get-SPContentDatabase cmdlet as a security measure.

However, you can use the –webapplication parameter to explicitly get the content databases for the specific (Central Admin) web application. So:

Get-SPContentDatabase –WebApplication http://[server]:[port]

Will return the existing content database and the one we just created, so we can run:

Get-SPSite –ContentDatabase {GUID of old database} |
    Move-SPSite -DestinationDatabase {GUID of new database}

The first cmdlet selects all the SharePoint sites which are in the content database with the “evil” name. The pipe sends those sites as the input for the Move-SPSite cmdlet, with the GUID of the new database. Once you’ve moved the sites to the new database and verified them, then you can take the old content database offline, delete it from SharePoint, and delete it in SQL Server.

How to rename SharePoint 2010 … Everything!

This is the first in a series of articles to pull together all the various guides around the web to rename SharePoint 2010 databases, servers, services, and other objects. Over the coming weeks I’ll publish these articles:

  • Introduction & Windows PowerShell (this article)
  • Rename the Configuration and Content Databases
  • Rename the State Service Database
  • Rename Alternate Access Mappings
  • Rename Servers
  • Rename SharePoint Site Groups and Sites and IIS Sites
  • Rename IIS Application Pools

Why Rename SharePoint 2010 Objects?

As SharePoint 2010 installations grow in size, admins and DBAs are facing increasing frustration in dealing with Microsoft’s propensity to guarantee unique names by gluing GUIDs on the back of every name. This has gotten to be such an issue that half the reason the AutoSPInstaller script was created was to eliminate the GUIDs where possible. (Note that aside from simply being unattractive, names with GUIDs are a royal pain when you have to write scripts)

A standard SharePoint 2010 installation leaves nasty database names.

However, you may still end up with awkward names for various reasons. There are instructions on how to rename these artifacts scattered around the web; this is my attempt to gather everything into one place.

Windows PowerShell to Rename Content Databases and other SharePoint Objects

If you’re working with SharePoint 2010 and you’re not working with Windows PowerShell, it’s time to start. Virtually all the guidance here is PowerShell-based. I’ll be working with the SharePoint PowerShell provider, which has the SharePoint extensions enabled as well as the path to the 14 hive defined – it makes a great command prompt for SharePoint as well.

Some Windows PowerShell tips before we proceed. You will definitely want to understand piping. The short, simple explanation is that when you use the pipe character ( | ) between commands, it redirects the output of the first command into the second command.

So this script:

Get-SPDatabase | SELECT Name, ID

Returns all the SharePoint databases (Services Databases, Configuration Databases, Content Databases) from the current farm. Think of this as a collection of objects. This collection is then sent to the SELECT command, which will select out the Name and ID properties and list those for all the objects piped to it. Understanding the “way of pipes” will make a huge difference in your productivity with Windows PowerShell.

The other little tidbit about PowerShell and SharePoint objects are how to get the one you want. When you type Get-SPDatabase in the SharePoint Management Shell, you’ll get something looking like this:


Using the SharePoint 2010 Management shell.

So how do you get the one you want? The most popular way to pick out a specific object is to use the SELECT-OBJECT cmdlet, like this:

Get-SPDatabase | WHERE-OBJECT { $_.Name -eq "SomeDatabase" }

This line takes that collection of SPDatabase objects output by the Get-SPDatabase cmdlet, and pipes it to the WHERE-OBJECT cmdlet. That takes the collection of objects and uses the statement in curly braces to filter out the database we’re looking for. (“$_” is shorthand for “the current object”)

[amazon_enhanced asin=”1118021983″ container=”AmazonTable” container_class=”alignleft” price=”New” background_color=”FFFFFF” link_color=”000000″ text_color=”0000FF” /]

The problem, of course, is that we have these nasty GUID names to deal with, so we’re going to be copying and pasting from the list of databases. If we have to copy and paste anyway, then we can cut to the chase with this command:

Get-SPDatabase -ID c3c72c94-513b-4ff1-9346-3646f1bdc7ff

This gets us one of the Content Databases – in this case the one for the Central Admin site. The ID is a GUID, and easily selectable to copy and paste from the list. This is the second way to return a specific database, and until we get the names trimmed down, it’s the most straightforward. In addition, the –ID parameter is available for virtually every SharePoint object type, and will obviously always be unique.

With that under your belt, next time we’ll tackle renaming the SharePoint Content Databases, and I’ll address why you should never rename a SharePoint Farm Configuration Database.