Move content databases (Office SharePoint Server 2007)
Updated: July 15, 2008
Applies To: Office SharePoint Server 2007
You can move content databases between instances of Microsoft SQL Server 2005, from one server running Microsoft Office SharePoint Server 2007 to any other server running Office SharePoint Server 2007, from one Web application to another, from Windows Internal Database to an instance of SQL Server 2005, or from Microsoft SQL Server 2005 Express Edition to an instance of SQL Server 2005. You can also move one or all content databases for any Web application to another Web application.
Windows Internal Database uses SQL Server technology as a relational data store for Windows roles and features only, such as Windows SharePoint Services, Active Directory Rights Management Services, UDDI Services, Windows Server Update Services, and Windows System Resources Manager.
You might move content databases as part of an upgrade or migration process, as part of a backup and recovery process, or as part of the process of moving your sites from a development environment to a production environment.
This article covers moving only content databases and does not provide information for moving other databases that are associated with Office SharePoint Server 2007.
You can perform this task by using either the SharePoint Central Administration Web site or by using the Stsadm command-line tool.
If you detach and reattach a content database, be aware that the next time the content within that content database is crawled a full crawl will occur even if an incremental crawl has been requested. Because a full crawl re-crawls all content that the crawler encounters, regardless of whether that content has been previously crawled, full crawls can take significantly more time to complete than incremental crawls.
If you are running the Infrastructure Update for Microsoft Office Servers, the identifier (ID) of each content database is retained when you restore or reattach the database by using built-in tools. Default change log retention behavior when using built-in tools is as follows
The change logs for all databases are retained when you restore a farm.
The change log for a content database is retained when you reattach the database.
The change log for a content database is NOT retained when you restore just the content database.
When a database ID and change log are retained, Search continues crawling based on the regular schedule defined by crawl rules. When a change log is not retained, Search performs a full crawl during the next scheduled crawl.
For more information, see Protecting and restoring a farm (Office SharePoint Server 2007).
The following are required to perform the procedures for this task:
You must be a member of the Administrators group on the source server computer and the destination server computer.
You must have at least the following roles in SQL Server 2005:
The db_owner fixed database role for the database on the source server running SQL Server 2005 or SQL Server instance, to detach the database.
If you are restoring to a different farm you must make the database access account a member of the Administrators group on the database server during the restore process. This is required for the account to replicate the security setting for the databases. This access level can be removed after the restore process is complete.
The dbcreator fixed server role on the destination server running SQL Server 2005 or SQL Server instance, to attach the database.
To move content databases, you can perform the procedure that corresponds to your environment:
ConceptsDatabase maintenance (Office SharePoint Server 2007)
Database maintenance for Office SharePoint Server 2007 (white paper)
Planning and Monitoring SQL Server Storage for Office SharePoint Server: Performance Recommendations and Best Practices (white paper)
Add, split, and merge content databases (Office SharePoint Server 2007)
Move all databases (Office SharePoint Server 2007)