Merge MOSS (Sharepoint) 2007 Content DBs Using STSADM

STSADM rocks my world sometimes. MERGECONTENTDBS is no different.

Permits a site collection to be moved from one content database to another when the sourcedatabasename and destinationdatabasename parameters are specified.

The source and destination databases must be in the same Microsoft SQL Server instance and must be attached to the same Web application. For optimal performance, move no more than 250 site collections at a time. Moving more site collections at a time can greatly diminish performance.

Important:
Before you perform the Stsadm mergecontentdbs operation, you must have available free space that is at least three times the size of the source site collection.

Use the Enumsites: Stsadm operation (Windows SharePoint Services) [ http://technet.microsoft.com/en-us/library/cc288252.aspx ] to determine the size of the site collection.


Important:
In order to move a site collection from one database to another, you must be a member of both the Farm Administrators group and the Local Administrators group, and must have the Full Control permission granted for any site collection that needs to be moved. To grant this permission, in Central Administration, click Application management, and then Application Security, and then Policy for Web application. The account that you use to perform this procedure must be a member of the db_owner fixed database role in SQL Server.

After the site has been moved successfully, remove or change your account permission level by using the Policy for Web Application page. If your account is used for other services, reset it to the original permission level.

If you do not have the correct permissions to perform the operation, you will receive the following error message: “Moving sites... Another site already exists at /sites/test. Delete this site before attempting to create a new site with the same URL, choose a new URL, or create a new inclusion at the path you originally specified.”


After the move is complete, you must run the iisreset /noforce command on each of the front-end Web servers in your farm. If a large amount of data has been moved, you may want to shrink the Microsoft SQL databases and transaction logs. For further information on shrinking databases in SQL, see How to: Shrink a Database (SQL Server Management Studio) [ http://go.microsoft.com/fwlink/?LinkId=102959&clcid=0x409 ] (http://go.microsoft.com/fwlink/?LinkId=102959&clcid=0x409).

Note:
The mergecontentdbs operation was first introduced in Windows SharePoint Services 3.0 Service Pack 1.


Syntax
stsadm -o mergecontentdbs

-url

-sourcedatabasename

-destinationdatabasename

[-operation] {1-3}

1 - Analyze (default)

2 - Full database merge

3 - Read from file

[-filename]

Parameters
Parameter name
Value
Required?
Description

url
A valid URL, such as "http://server_name"
Yes
URL of the Web application whose databases that you want to merge.

sourcedatabasename
A valid database name, such as "WSS_Content_1
Yes
The database name from which site collections will be moved.

destinationdatabasename
A valid database name, such as "WSS_Content_2"
Yes
The name of the database to which the site collections will be moved.

operation
Any one of the following values:

1 - Analyze

2 - Full database merge

3 - Read from file
No
1 - Analyze: Indicates that the command window displays the current number of site collections, the database size, the maximum number of site collections, and the number of site collections that can be added before reaching the maximum number of site collections for each content database.

Also, a recommendation regarding which content database should be used for the source and which content database should be used for the destination—assuming that all site collections in the database will be moved—is displayed in the command window. This recommendation is based on which content database contains less data and therefore would be faster to move.

This is the default.

2 - Full database merge: Merges the entire content database from one database to another. Once the operation is complete, the source content database still exists in Microsoft SQL Server and is still attached to the Web application. It simply has no site collections in it.

3 - Read from file: Moves only a subset of site collections from the source database to the destination database. The site collections that are to be moved should be listed in a file that is specified with the filename parameter.

filename
A valid file name, such as "sites.xml"
No
Specifies a specific site collection in the source content database you want moved to the destination database

This information is obtained using the databasename parameter of the Enumsites [ http://technet.microsoft.com/en-us/library/cc262492.aspx ] operation.

Note: The databasename parameter was first introduced in Windows SharePoint Services 3.0 with Service Pack 1.

Note: You can use the redirection operator ">" to capture the XML output of the enumsites operation into a text file.


Remarks
Before the mergecontentdbs operation became available, the only way to move a site collection from one content database to another was to complete these steps:

Lock a site collection using the Setsitelock [ http://technet.microsoft.com/en-us/library/cc287893.aspx ] operation.

Back up the site collection using the Backup [ http://technet.microsoft.com/en-us/library/cc287967.aspx ] operation.

Delete the site collection using the deletesite operation. For more information, see Command-Line Operations [ http://go.microsoft.com/fwlink/?LinkId=102963&clcid=0x409 ] (http://go.microsoft.com/fwlink/?LinkId=102963&clcid=0x409).

Create a new site collection with the same site collection URL using the Createsiteinnewdb [ http://technet.microsoft.com/en-us/library/cc288051.aspx ] operation.

Restore it using the Restore [ http://technet.microsoft.com/en-us/library/cc288929.aspx ] operation.

Unlock it using the Setsitelock [ http://technet.microsoft.com/en-us/library/cc287893.aspx ] operation.

Examples
Merge Content Databases using the filename parameter
For this example, WSS_Content_1 and WSS_Content_2 are the content database names.

To create a list of sites in the sites.xml file using the redirector operation ">", use the following syntax:

stsadm -o enumsites -url http:// –databasename WSS_Content_1 > sites.xml

Note:
The redirector operator ">" redirects the output to a text file named sites.xml. You can then edit the sites.xml file to list the site collections that should be moved.


To move the site collections specified in the sites.xml file from one content database to another, use the following syntax:

stsadm -o mergecontentdbs -url http:// -sourcedatabasename WSS_Content_1 -destinationdatabasename WSS_Content_2 -operation 3 -filename sites.xml

Note:
The number 3 in the operation parameter corresponds to "Read from file".


After the site collection is moved and to ensure the changes take effect on all front-end Web servers in the farm, run the following command from a command prompt:

iisreset /noforce