Moving SharePoint Databases to a new SQL Server – Part 1
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
Date: 3/11/2008
Time: 5:05:00 PM
User: N/A
Computer: [SharePoint Server]
Description:
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.
I am having a similar error. I moved my content db over to a new environment without running stsadm -o prepareToinstall first, and now can’t attach the content db or create the webapp. I am trying to run stsadm -o preparetoinstall with the oldcontentdb switch to fix it, but I just get errors like:
A content database with the name xxxx does not exist on teh specified server instance.
And A site collection with the URL xxxx does not exist in this farm.
Which are true I suppose. I can’t attach the content db because I didn’t run preparetoinstall, but I can’t run preparetonistall because I can’t attach the contentdb. I’m trapped in circle
I can run preparetoinstall and then do another backup and restore to my new location, but I would have to wait several days to coordinate this with my dba and do it at night when users aren’t on. Just wondering if there was a quicker way around this to get this working. (When we go live we’ll do it on a weekend but I need to dry run the move so it goes smoothly)
In my last position I never had problems moving content dbs and never used the preparetomove switch, for some reason it worked, which is why I didn’t know to use it this time
woops I meant stsadm -o preparetomove
Chris,
I’m not sure of another way that I can tell you works off the top of my head. If you are keeping the same URL/Host Header in the new environment you could try:
1. Restoring the DB into SQL
2. Open up Central Admin
3. Create/Extend Web Application
4. Create a new web application
5. Enter everything as normal, however when you get to the database, put in the DB name of the database you restored into SQL. SharePoint won’t complain that it already exists, rather it will just use that database, including all the content in it, to create the web web application
However, this will only work as long as it’s the same URL from my experience. I’m not sure if there will be any other limitations as it’s honestly been a long time since I moved a content DB to a new environment, but it is something to try
I’ll comment further if I come up with any other ideas.
i have moved 10 content DB from C: to D: drive, after that i can able to open all my sites except Central Admin site.
below is the Event Viewer log entry for the issue.
SharePoint_AdminContent_3e04cf00-04b9-4d09-88d2 is the Central Admin content DB.
Will this work If i attach only the SharePoint_AdminContent_3e04cf00-04b9-4d09-88d2 DB ?
Can anyone help me?.
SQL Database
‘SharePoint_AdminContent_3e04cf00-04b9-4d09-88d2-aebdb16820fa’ on SQL
Server instance ‘SharePointDb’ not found.
Additional error information from SQL Server is included below.
Cannot open database
“SharePoint_AdminContent_3e04cf00-04b9-4d09-88d2-aebdb16820fa” requested
by the login. The login failed.
umm…. I found that with WSS 3.0, in a distributed environment, you can stop the services on the WFE running Central Admin (also quiesce the farm if you have the option), and then run stsadm -o renameserver to change the db server names. Then, its just detach/attach or backup/restore and then boot your WFE. Please note that if you have multiple WFEs you will have to run the stsadm -o renameserver command on them as well. It seems to work well and does not involve alot of pomp and circumstance.
Manual changes in the Sharepoint DBs are COMPLETELY UNSUPPORTED!