Migrate or Move Sharepoint Content and Config Db from one server to other (SQL 2005 To SQL 2008)

To migrate or move all SharePoint databases or config db (content and Configuration) from SQL 2005 To SQL 2008 for SharePoint 2007

Steps:

1. Note down SSP Settings
   1. Central Admin -> Shared Services Administration
   2. Take Print out of the page
   3. Central Admin->SSP->Search Administration->Content Sources
   4. Take Print out of the page
   5. Note Down Content Access account
   6. Central Admin -> SSP->user Profile properties -> view user profile properties
   7. Take print out of the page
   8. Note down all web applications and associated databases
      1. Central Admin -> Operations ->Perform a backup
      2. Take print out of the page
      3. Stop the farm
      4. Take back up of all content databases
      5. Restore content databases to the new server (except WSS_Search)

Attach Content Databases to web applications

 Below procedure will be used for Admin Content database as well

Go to STSADM

stsadm –o deletecontentdb –url  http://url –databasename “databasename”  -databaseserver “oldserver”

stsadm –o addcontentdb –url http://url –databasename “databasename” –databaseserver “newserver”

Move SSP to the new server

 Take back up of SSP through central admin

1. Take back up using stsadm  (stsadm -o backup -directory ”\\server\SharedFolderbackup”  -backupmethod full -item “SSPName”)
2. Take backup of SSP content database from SQL management  Studio
3. Place SSP backup in the shared folder where both servers has access and SQL service account from new server should also have access to the backup folder
4. Delete SSP (stsadm -o deletessp -title ”SSPName” -deletedatabases –force)
5. Detach SSP Content database (stsadm -o -deletecontentdb -url http://url -databasename “databasename”)
6. Make sure SharePoint Timer Service is running
7. Make sure Search Services are running on the new server
8. Restore SSP to new server using central admin
9. Check User profiles page (you might see an error : An Indexer is not assigned to the SSP, follow steps 11 and 12 else go to 13)
10. Go to SSP ->Edit properties
11. Make sure index server is selected
12. Stop Search Service on the old server
13. Create Search service from central admin (enter new server details)
14. Make sure Search Services are running on the new server
15. If needed run the crawl
16. Visit the site to make sure everything is normal and running smooth

Move SharePoint Config Database

Make sure you login as Farm Admin and you have access to database as DbCreator on the new sql server.

1. Take back up of SharePoint Configuration Database
2. Restore Config DB to the new server
3. Disconnect Config DB from the old server  (Psconfig.exe –cmd configdb –disconnect)
4. Connect the Config DB to the new Server (psconfig.exe -cmd configdb -connect -server  ”newsql” –database “SharePoint_Config”  -user  ”domain\user” -password  ”user_password”)

OR

psconfig.exe -cmd configdb -connect -server ”newserver” -database “Sharepoint_Config” -dbuser “domain\user” -dbpassword “password” -user “domain\user” -password  “password”

 Run SharePoint Config Wizard (select do not disconnect). Select the same port as it was earlier for central admin.

-          Re-Start IIS

-          Database should now be linked to the new SQL server.

Check Central Admin -> Operations -> Servers in the Farm

Configuration database server might be pointing to the old server. This is because config db stores information about all servers and re-name server command might not work at this stage

Check my previous blog about fixing this issue

In that case please make a change (change the parentID of the new server) in the config database.