Rename or move service application databases (SharePoint Server 2010)

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes how to rename or move Microsoft SharePoint Server 2010 service application databases. Renaming service application databases is typically done to remove the GUID from the database name after the databases have been created by the SharePoint Products Configuration Wizard, or to bring the databases into alignment with your organization's naming standards. Moving service application databases can be done to load-balance a system.

The general process for renaming or moving service application databases is as follows:

1. Rename or move the database by using SQL Server tools.

2. Point the service application to the renamed or moved database.

   Pointing a service application to a renamed or moved service application database can be a complex process, especially if you must rename or move the databases for multiple service applications. This is because different types of service applications require different methods of pointing to the new database name or location. Based on the service application database that you are renaming or moving, you will use one of the following methods to point the service application to the renamed or moved database:

   • Delete the service application, re-create the service application, and then point the service application to the existing renamed or moved database.

     You can use this method for most service application databases, but this method can be complex for some service applications, such as Search.

   • Use Windows PowerShell to point the service application to the renamed or moved changed database.

     You can also use Central Administration for a subset of the service application databases that you can use Windows PowerShell to point to. When possible, we recommend that you use Central Administration to rename or move databases because that is the easiest way to perform the operation. Windows PowerShell is available for only some service application databases, and Central Administration is available for a subset of those service applications.

The following table shows the methods that can be used to point each service application to a renamed or moved database.

Methods available for pointing to renamed or moved service application databases

1It may appear that you can rename the Business Data Connectivity database in Central Administration or by using the Windows PowerShell cmdlet Set-SPBusinessDataCatalogServiceApplication. However, using either Central Administration or Set-SPBusinessDataCatalogServiceApplication creates a new database. It does not rename the existing database.

2Because there is no Windows PowerShell cmdlet to create the Application Registry service application, it cannot be renamed. It can be moved by using Microsoft SharePoint Server 2010 backup and recovery.

3It may appear that you can rename the Secure Store database by using the Windows PowerShell cmdlet Set-SPSecureStoreServiceApplication. However, using Set-SPSecureStoreServiceApplication creates a new database. It does not rename the existing database.

In this article:

• Renaming or moving service application databases by using SQL Server

  This method includes the following procedures:

  • To stop a service application

  • To rename a database by using SQL Server Management Studio

  • Move a database by using SQL Server Management Studio and Windows

• General method for pointing a service application to a renamed or moved database

  This method includes the following procedures:

  • To document service application settings

  • To delete a service application

  • To re-create a service application

  • To restart a service application

  Note

  This method does not apply to all service application databases. Refer to the guidance for the specific service application to determine which method to use.

• Service application-specific guidance for renaming or moving databases

  • Pointing the Usage and Health Data Collection service application to a renamed or moved database

  • Pointing the Search service application to renamed or moved databases

  • Pointing the Web Analytics service application to renamed or moved databases

  • Pointing the State service application to a renamed or moved database

  • Pointing the Managed Metadata service application to a renamed or moved database

  • Pointing the Word Automation service application to a renamed or moved database

  • Pointing the Secure Store service application to a renamed or moved database

  • Pointing the Business Data Connectivity service application to a renamed or moved database

  • Pointing the Application Registry service application to a renamed or moved database

  • Pointing the User Profile service application to renamed or moved databases

  • Pointing the PerformancePoint service application to a renamed or moved database

  • Pointing the Subscription Settings service application to a renamed or moved database

Renaming or moving service application databases by using SQL Server

To rename a service application database, you must use SQL Server. To move a service application database, you must use SQL Server and Windows Explorer. We recommend that you stop the services related to the service application before you rename or move the related database. In general, we expect that you will either rename or move a database. That is, you will typically not perform both actions.

The instructions in this article assume that you have installed SQL Server Management Studio on the database server. If this is not the case, you can download and install Management Studio at Microsoft SQL Server 2008 Management Studio Express (https://go.microsoft.com/fwlink/p/?LinkID=186132&clcid=0x409).

To stop a service application

1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

2. For each service application that you plan to change the database for, you must stop the service or disable the service application by following the appropriate procedure for the service application that you are working with. For more information, see Manage service applications (SharePoint Server 2010).

To rename a database by using SQL Server Management Studio

1. In SQL Server Management Studio, connect to the source SQL Server instance, and then expand the Databases node.

2. Right-click the database that you want to rename, click Rename, and then type the new name. Repeat this step for each database that you want to rename.

Move a database by using SQL Server Management Studio and Windows Explorer

Moving a database requires detaching the database from SQL Server, moving the files to the new location by using Windows Explorer, and then attaching the database to the new instance of SQL Server.

To detach a database from SQL Server

1. In SQL Server Management Studio, open the source SQL Server instance, and then expand the Databases node.

2. Right-click the database, point to Tasks, and then click Detach. Repeat this step for each database that you want to move.

To move database files to a new location by using Windows Explorer

1. In Windows Explorer, locate the .mdf, .ndf, and .ldf files for the service application databases.

2. Select the .mdf, .ndf, and .ldf files for the databases that you want to move, and then either copy or move them to the destination directory.

To attach a database to a new instance of SQL Server

1. In Management Studio, open the destination SQL Server instance.

2. Right-click the Databases node, point to Tasks, and then click Attach.

3. In the Attach Database dialog box, browse to where you transferred the .mdf, .ndf, and .ldf files, select the .mdf file for the database that you want to attach, and then click OK.

4. Repeat for each database that you are moving.

General method for pointing a service application to a renamed or moved database

The method for pointing a service application to a renamed or moved database that works for most service applications is to delete the service application and then re-create the service application. When you re-create the service application, use the new name or new location.

This is the only method that can be used to point to moved or renamed databases for the following service applications:

• Business Data Connectivity

• User Profile

• PerformancePoint

This method includes the following steps:

1. To document service application settings

2. To delete a service application

3. To re-create a service application

4. To restart a service application

To document service application settings

• Before you delete and re-create a service application, document the settings for the service application. To do this, use the recommended Windows PowerShell cmdlets that are described in the article Document farm configuration settings in SharePoint Server 2010.

To delete a service application

• To delete a service application, follow the procedure in Delete a service application (SharePoint Server 2010), but do not delete the service application database.

To re-create a service application

• Re-create the service application, pointing it to the renamed database, and reusing all the other initial configuration settings. For more information, see the following articles:

  • Configure usage and health data collection (SharePoint Server 2010)

  • Enabling Multi Tenant Support in SharePoint 2010 (https://go.microsoft.com/fwlink/p/?LinkId=197114)

  • Business Data Connectivity service administration overview (SharePoint Server 2010)

  • Managed metadata administration (SharePoint Server 2010)

  • Configure the Secure Store Service (SharePoint Server 2010)

    Note

    As you configure the service application, you must use the same passphrase that you used when you first configured the service application.

  • Manage the State Service (SharePoint Server 2010)

  • Manage farm-level search settings (SharePoint Server 2010) and Manage search topology (SharePoint Server 2010)

  • Configure PerformancePoint Services

  • Create, edit, or delete a User Profile service application (SharePoint Server 2010)

• Depending on the service application, you may have to manually start the related service.

To restart a service application

1. In Central Administration, under System Settings, click Manage services on server.

2. In the Service list, next to the appropriate service or services that support the service application that you are restarting, click Start.

Service application-specific guidance for renaming or moving databases

This section describes specific guidance for each kind of service application.

Pointing the Usage and Health Data Collection service application to a renamed or moved database

You can use either Central Administration or Windows PowerShell to point the Usage and Health Data Collection service application to a renamed or moved database.

To point the Usage and Health Data Collection service application to a renamed or moved database by using Central Administration

1. Verify that the user account that is performing the procedure is a member of the Farm Administrators SharePoint group.

2. In Central Administration, click Monitoring, and then on the Monitoring page, click Configure usage and health data collection.

   The Configure web analytics and health data collection page appears.

3. Clear the Enable health data collection check box, and then click OK.

   You return to the Monitoring page.

4. Click Configure usage and health data collection to return to the Configure web analytics and health data collection page.

5. Select the Enable health data collection check box

6. In the Logging Database Server section, specify the updated database server and database name, and then click OK.

To point the Usage and Health data collection service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following command:

   Set-SPUsageApplication -Identity "<ServiceApplicationName>" -DatabaseName "<DbName>" -DatabaseServer "<SQLServerName>"
   

   Where:

   • <ServiceApplicationName>is the name of the usage and health data collection service application.

   • <DbName> is the name of the database.

   • <SQLServerName> is the name of the database server.

For more information, see Set-SPUsageApplication.

Pointing the Search service application to renamed or moved databases

You can point the Search service application to Crawl and Property databases that have been renamed by using Central Administration or by using Windows PowerShell 2.0. You must use Windows PowerShell 2.0 to point to a renamed Search Administration database.

To point the Search service application to renamed or moved Crawl and Property databases by using Central Administration

1. Verify that the user account that is performing the procedure is a member of the Farm Administrators SharePoint group.

2. In Central Administration, click Application Management.

3. On the Application Management page, click Manage service applications.

4. Click the name of the Search Service Application.

5. On the Search Administration page, click Modify.

6. On the Manage Search Topology page, the following three databases are listed: Administration, Crawl, and Property. You can point to renamed or moved Crawl or Property databases by using this procedure.

   Note

   You cannot point to a renamed or moved Search Administration database by using Central Administration.

7. Click the database that you want to change, and then click Edit Properties.

8. In the Database Server text box, type the new server location if there is one; in the Database Name text box, type the new name for the database; and then click OK.

9. On the Manage Search Topology page, click Apply Topology Changes.

   It might take several minutes for the changes to take effect.

To point the Search service application to renamed or moved Crawl, Property, and Administration databases by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following commands:

   • Point the Search Administration database to its new name or location.

     $searchapp | Set-SPEnterpriseSearchServiceApplication -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
     

     Where:

     • <NewDbName> is the name of the renamed database.

     • <NewServerName> is the new database location.

   • Monitor whether the search instances have finished re-provisioning.

     Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
     

   • Set the crawl database to its new name or location.

     $CrawlDatabase0 | Set-SPEnterpriseSearchCrawlDatabase -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
     

     Where:

     • <NewDbName> is the name of the renamed database.

     • <NewServerName> is the new database location.

   • Monitor whether the search instances have finished re-provisioning.

     Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
     

   • Set the property database to its new name or location.

     $PropertyDatabase0 | Set-SPEnterpriseSearchPropertyDatabase -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
     

     Where:

     • <NewDbName> is the name of the renamed database.

     • <NewServerName> is the new database location.

   • Monitor whether the search instances have finished re-provisioning.

     Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
     

   • Restart the search service instance.

     get-SPEnterpriseSearchServiceInstance | start-SPEnterpriseSearchServiceInstance
     

   • Resume running the search service application.

     $searchapp.Resume()
     

Pointing the Web Analytics service application to renamed or moved databases

You can use either Central Administration or Windows PowerShell to point the Web Analytics service application to renamed or moved databases. You can also delete and re-create the service application.

To point to renamed or moved Web Analytics databases by using Central Administration

1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

2. In Central Administration, click Application Management.

3. On the Application Management page, click Manage service applications.

4. Click the Web Analytics Service Application. The ribbon becomes active.

5. Click Properties on the ribbon.

   The Edit Web Analytics Service Application wizard opens.

6. Click Next on the first page. On the Edit Web Analytics Service Application Topology page, point to the database name, click Edit Properties, and then specify the new database server or database name. Repeat this step for each database.

7. Click OK to dismiss the Edit Database dialog box, and then click Next.

   You should see a message that the Web Analytics service application was successfully edited.

8. Click OK.

9. In Central Administration, under System Settings, click Manage services on server.

10. In the Service list, next to the Web Analytics Data Processing Service and Web Analytics Web Service, click Start.

To point the Web Analytics service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following command:

   Set-SPWebAnalyticsServiceApplication -Identity "<ServiceApplicationName>" [-ListOfReportingDatabases <ReportingServerAndDatabases>] [-ListOfStagingDatabases <StagingServerAndDatabases>]
   

   Where:

   • <ServiceApplicationName> is the name of the service application.

   • <ReportingServerAndDatabase> is an XML string that contains the server name and the database name for the Reporting database.

     Note

     Despite the name of the option, only one Reporting database is supported per Web Analytics service application.

   • <StagingServerAndDatabases> is an XML string that contains the server name and the database names for the staging databases.

   The following example shows how to use the Set-SPWebAnalyticsServiceApplication cmdlet:

   Set-SPWebAnalyticsServiceApplication -Identity WebAnalyticsServiceApplication1 -ListOfReportingDatabases "<ReportingDatabases><ReportingDatabase ServerName='SharePointReporting' DatabaseName='WAReporting'/></ReportingDatabases>" -ListOfStagingDatabases "<StagingDatabases><StagingDatabaseServerName=''SharePointReporting' ' DatabaseName='WAStaging1'/> <StagingDatabase ServerName='''SharePointReporting' DatabaseName = 'WAStaging2'/></StagingDatabases>" 
   

For more information, see Set-SPWebAnalyticsServiceApplication.

Pointing the State Service service application to a renamed or moved database

The State Service database stores temporary data. You can use Windows PowerShell to point the State Service service application to a renamed or moved database by performing one of the following procedures:

• Add a new database in the new location, or create a database with a new name. Then add the new database to the service application, and delete the old database. For details, see To add a new database to the State service application, and remove an old database by using Windows PowerShell.

• Dismount the old database, rename or move it by using SQL Server, and then remount the State Service database. For details, see To point the State service application to a renamed or moved database by using Windows PowerShell.

  The following procedures have the following steps integrated into them. Therefore, they do not require that the steps have already been performed:

  • Stopping a service application

  • Renaming a database in SQL Server Management Studio

  • Moving a database by using SQL Server Management Studio and Windows

To add a new database to the State Service service application and remove an old database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following command to create a new database:

   New-SPStateServiceDatabase -Name "<NewDatabaseName>" 
   

   Then type the following command to remove the old database:

   Remove-SPStateServiceDatabase -Name "<OldDatabaseName>"
   

   Where:

   • <NewDatabaseName> is the name of the new database that you want to create.

   • <OldDatabaseName> is the name of the old database that you want to disassociate with the State service and detach from SQL Server.

For more information, see New-SPStateServiceDatabase and Remove-SPStateServiceDatabase.

To point the State Service service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. Record the State Service service application ID and the database ID that you will configure.

3. On the Start menu, click All Programs.

4. Click Microsoft SharePoint 2010 Products.

5. Click SharePoint 2010 Management Shell.

6. At the Windows PowerShell command prompt, type the following command to dismount the database:

   Dismount-SPStateServiceDatabase -Identity <DatabaseID>
   

   Where:

   • <DatabaseID> is the State Service database to remove from the service application.The type must be a valid GUID in the form 12345678-90ab-cdef-1234-567890bcdefgh, a valid name of a state database, or an instance of a valid SPStateServiceDatabase object.

   For more information, see Dismount-SPStateServiceDatabase.

7. Rename or move the database. For details, see To rename a database by using SQL Server Management Studio or Moving a database by using SQL Server Management Studio and Windows .

8. At the Windows PowerShell command prompt, type the following command to mount the renamed or moved database:

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

   Where:

   • <DatabaseName> is the name of the database to associate with the State service.

   • <ServerName> is the name of the SQL Server that hosts the State service database.

   For more information, see Mount-SPStateServiceDatabase.

Pointing the Managed Metadata service application to a renamed or moved database

You can point the Managed Metadata service application to a renamed or moved database by using either Central Administration or Windows PowerShell. For information, see Create, update, publish, or delete a managed metadata service application (SharePoint Server 2010).

Pointing the Word Automation service application to a renamed or moved database

You can point the Word Automation service application to a renamed or moved database by using Central Administration or Windows PowerShell.

This procedure has the following steps integrated into them, and do not require that they have already been performed:

1. Stopping a service application

2. Renaming a database in SQL Server Management Studio

3. Moving a database by using SQL Server Management Studio and Windows Explorer

To point the Word Automation service application to a renamed or moved database by using Central Administration

1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

2. In Central Administration, under System Settings, click Manage services on server.

3. In the Service list, next to the Word Automation service, click Stop.

4. In the Quick Launch, click Application Management.

5. On the Application Management page, click Manage service applications.

6. Click the Word Automation service application. The ribbon becomes active.

7. Click Properties on the ribbon.

   The Edit Word Automation Service Application dialog box opens.

8. Change the database server or database name, and then click OK.

9. In the Quick Launch, click System Settings.

10. On the System Settings page, under Servers, click Manage services on server.

11. In the Service list, next to the Word Automation service, click Start.

To point the Word Automation service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. Record the Word Automation service application name and the database name that you plan to configure.

3. On the Start menu, click All Programs.

4. Click Microsoft SharePoint 2010 Products.

5. Click SharePoint 2010 Management Shell.

6. At the Windows PowerShell command prompt, type the following command:

   $app = Get-SPServiceApplication -Name "<ServiceApplicationName>"
   Set-SPWordConversionServiceApplication -Identity $app -DatabaseName "<DatabaseName>" -DatabaseServer "<DatabaseServer>"
   

   Where:

   • <ServiceApplicationName> is the name of the Word Automation service application.

   • <DatabaseName> is the name of the renamed or moved database.

   • <DatabaseServer> is the location of the renamed or moved database. Do not include this parameter if you are pointing to a renamed database in the same location.

   For more information, see Set-SPWordConversionServiceApplication.

Pointing the Secure Store service application to a renamed or moved database

You can point the Secure Store service application to a renamed or moved database by using Central Administration. Alternatively, you can rename or move the database, and then delete or re-create the service application. If you choose to delete and re-create the service application, you must use the same passphrase that you used when you initially configured the service application. For more information, see General method for pointing a service application to a renamed or moved database.

To point the Secure Store Service service application to a renamed or moved database by using Central Administration

1. Verify that the user account that is performing this task is a member of the Farm Administrators SharePoint group.

2. In Central Administration, under System Settings, click Manage services on server.

3. In the Service list, next to the Secure Store service, click Stop.

4. In the Quick Launch, click Application Management.

5. On the Application Management page, click Manage service applications.

6. Click the Secure Store Service service application. The ribbon becomes active.

7. Click Properties on the ribbon.

   The Edit Secure Store Service Application page opens.

8. Change the database server or database name, and then click OK.

   Note

   If you use an incorrect name, SharePoint Server 2010 creates a new database.

9. In the Quick Launch, click System Settings.

10. On the System Settings page, under Servers, click Manage services on server.

11. In the Service list, next to the Secure Store service, click Start.

Pointing the Business Data Connectivity service application to a renamed or moved database

You must delete the Business Data Connectivity service application, rename or move the database, and then re-create the service application to point to a renamed or moved database. For information, see General method for pointing a service application to a renamed or moved database.

Pointing the Application Registry service application to a renamed or moved database

Because there is no Windows PowerShell cmdlet to create the Application Registry service application, the associated database cannot be renamed. You can move the database by using SharePoint Server 2010 backup and recovery. For more information, see Back up a service application in SharePoint Server 2010 and Restore a service application in SharePoint 2010 Products.

Pointing the User Profile service application to renamed or moved databases

You must delete the User Profile service application, rename or move the databases, and then re-create the service application to point to the renamed or moved databases. For information, see General method for pointing a service application to a renamed or moved database.

Pointing the PerformancePoint service application to a renamed or moved database

You can point the PerformancePoint service application to a renamed or moved database by using Windows PowerShell.

To point the PerformancePoint service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. Record the PerformancePoint service application name and the database name that you plan to configure.

3. On the Start menu, click All Programs.

4. Click Microsoft SharePoint 2010 Products.

5. Click SharePoint 2010 Management Shell.

6. At the Windows PowerShell command prompt, type the following command:

   Set-SPPerformancePointServiceApplication -Identity "<ServiceApplicationName>" -SettingsDatabase "<OptionalServerName\DatabaseName>"
   

   Where:

   • <ServiceApplicationName> is the name of the PerformancePoint service application.

   • <OptionalServerName\DatabaseName> is the location of and the name of the renamed or moved database. Do not include the location if you are just renaming the database.

   For more information, see Set-SPPerformancePointServiceApplication.

Pointing the Subscription Settings service application to a renamed or moved database

You can point the Subscription Settings service application to a renamed or moved database by using Windows PowerShell.

To point the Subscription Settings service application to a renamed or moved database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. Record the Subscription Settings service application name and the database name that you plan to configure.

3. On the Start menu, click All Programs.

4. Click Microsoft SharePoint 2010 Products.

5. Click SharePoint 2010 Management Shell.

6. At the Windows PowerShell command prompt, type the following command:

   Set-SPSubscriptionSettingsServiceApplication -Identity "<ServiceApplicationName>" -DatabaseName "<DatabaseName>" -DatabaseServer "<DatabaseServer>"
   

   Where:

   • <ServiceApplicationName> is the name of the Subscription Settings service application.

   • <DatabaseName> is the name of the renamed or moved database.

   • <DatabaseServer> is the location of the renamed or moved database. Do not include this parameter if you are just renaming the database in the same location.

   For more information, see Set-SPSubscriptionSettingsServiceApplication.

See Also

Concepts

Deploy by using DBA-created databases (SharePoint Server 2010)
Database types and descriptions (SharePoint Server 2010)
Manage service applications (SharePoint Server 2010)

Other Resources

Resource Center: SQL Server and SharePoint Server 2010 Databases