Here it is! Finally, a “How-To” for moving EVERY SharePoint database to a new server without losing any data. Unfortunately, due to the time of night and a last permission glitch I want to work through, I won’t have the complete instructions in this post, but I’ll give you a little teaser, and don’t worry, the details are close behind.
The first step is to move all the content databases to the new server. This can be done through Central Admin and using stsadm. The first step is to prepare the database to move using stsadm -o prepartetomove. After this, all you need to do is navigate to Central Admin -> Application Management -> Content Databases. From here select a web application, remove the databases, open up SQL Server, backup the database, restore the database to the new server, and then use stsadm –o addcontentdb on the SharePoint server to attach the database back to the web application. Continue to do this for all the web applications in your server farm.
This will also work for your Central Admin content database. For a step-by-step on moving Content Databases, including the Central Admin, see this blog post by Rickey Whitworth.
After this is where the problems start to arise, usually in moving the configuration database, the Search database, and the SharedServices Database(s). I’ll go into this in more detail in the next post, but it involves, more stsadm, running Configuration Wizard, resetting the search service and manually modifying the Objects Table and the Dependencies table in your SharePoint Configuration Database. Most people have commented that after moving the SharePoint Configuration Database using stsadm –o setcontentdb they get continual errors in the Event log along the lines of the one below. The method I will describe in the next post got rid of this error for me and left me with a running SharePoint environment.
Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Database
Event ID: 3760
Time: 5:05:00 PM
Computer: [SharePoint Server]
SQL Database ‘SharePoint_Config’ on SQL Server instance ‘[OldServer]’ not found. Additional error information from SQL Server is included below.
Cannot open database SharePoint_Config” requested by the login. The login failed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.