Detaching Databases in MOSS 2007

While database maintenance is performed, few things have to be considered and executed to keep your Sharepoint environment healthy. If you are detaching databases from a production environment, preparetomove is a must.

Any database attached to MOSS 2007 farm and feeding from an SSP has to have stsadm -o preparetomove executed prior to the actual detach from the farm.

IT WILL break the relationship between shared service provider and database if preparetomove is not used. This relationship can be repaired but it will lose the part of search index that relates to that database.

The full command is below.

stsadm -o preparetomove
               {-ContentDB |
                -Site }
               [-OldContentDB ]
               [-undo]

For detaching databases the clean way from the farm follow these two commands:

stsadm -o preparetomove -contentdb SQLSERVER:DBNAME -site http://www.yoursite.com

And remove database from farm:

stsadm -o deletecontentdb -url http://www.yoursite.com -databaseserver SQLSERVER -databasename DBNAME

When you’re ready to reattach your database to the farm follow this only step:

stsadm -o addcontentdb -url http://www.yoursite.com -databasename DBNAME -databaseserver SQLSERVER

What this does? It tells the SSP that you are relocating this database and prepare to get a new database GUID. It tells the system that the current GUID for databaseA is stale and that when this database name is picked up the next time in the crawl to replace its GUID. This way it will shift all of the Site information and indexs for the old database to the new database GUID.

If you fail to use this command prior to the move it will create a stale entry in your SSP and all sites going forward in the database will not be crawled. Additionally it will create a pretty ugly error in your web front end application log that looks similar to the following.

Failure trying to synch web application 01a25da4b1ava7cga32, ContentDB 3730120u274b4j-ad5a-e8y2544d11en: A duplicate site ID 1b4a438we24e0h-ba57858g41ua(http://portal/site) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

There is much more to this offered resolution.

First you need to find out which databases are currently not in sync, this can be done from a simple stsadm command on your SSP web front end.

stsadm -o sync -listolddatabases 5

This command will list all databases that have not synced up correctly with the SSP in greater than 5 days. Based on your schedule to index your age value may change. You may want to change this value to 10 or 20. The result set returned is a list of database GUID’s and the date/time they were last synchronized. Now that we have determined the list we need to clean them up and get those guys synced. To do this we execute an stsadm command that sets all of those GUID’s as old, then during your next index it will pick up the new GUID’s for your production databases not currently being synced.

stsadm -o preparetomove -contentdb sqlserver:dbname -oldcontentdb

where sqlserver is where your content is located, dbname is any database in your child farm, and GUID is a guild from the list of non-syncing databases we generated with the above command.

Based on your crawl schedule wait until the next full crawl. Once it completes go back into your SSP and run the stsadm -o sync -listolddatabases 5 command again. Anything left can likely be removed at this point. You can remove what is left by running the following command.

stsadm -o sync -deleteolddatabases 5

this will delete all GUID entries in the SSP for anything that is out of sync for more than 5 days. You have now successfully cleaned all inconsistencies with syncing in your SSP. All out of sync alerts should now be removed.

Remember! Use stsadm -o preparetomove before detaching any databases!