SharePoint: How To Move Databases to a New DB Server

Back up SharePoint visa the built-in tools prior to performing the steps.
Back up all SharePoint databases.

Move SharePoint_AdminContent database (Central Administration)

• Put the database offline. Go to Central Administration > Application Management > Content Databases > (select proper web application) - SharePoint Central Administration, and change database status to Offline. DO NOT tick "Remove content database".
• Prepare to move

stsadm -o preparetomove -contentdb <current db server\current db instance,port>:<current SharePoint_AdminContent database name> -site  http://<current SP central admin site with port no>

• Disconnect the DB

stsadm -o deletecontentdb -url "http://<current SP central admin site with port no>" -databasename "<current SharePoint_AdminContent database name>"

• Restore the DB to the new database server. This time around, rename to DB (get rid of the GUID) to something that's easier to identify/understand.
• Attach the DB back to Central Administration

stsadm -o addcontentdb -url http://<current SP central admin site with port no> -databasename "SharePoint_AdminContent" -databaseserver "<new database server>"

• iisreset /noforce
• Run Configuration Wizard

Move SharePoint_Config database

• Disconnect DB from the server
  psconfig.exe -cmd configdb -disconnect
• Backup SharePoint_Config and restore to the new server.
• Connect database back to sharePoint farm
  psconfig.exe -cmd configdb -connect -server "<new db server>" -database "SharePoint_Config" -user "domain name\username" -password "password"
• Run Configuration Wizard and provision new Central Administration. Choose "No, do not disconnect", and click Next. Change the port to <some port used as before>, NTLM, click Next.

Move other SharePoint Content databases

• Restore WSS_Content to the new database server.
• Go to Central Administration > Application Management > Content Databases
• Change the web application from the drop down list as appropriate.
• Set it to offline, and check Remove Content Database..
• Once done, click  "Add Content Database" and add the newly-restored database from the new database server.

Restore SSP

• Restore SharedServices1_DB to the new DB server.
• Enable indexers in Central Administration. Go to Operations > Servers on Farm > Office SharePoint Server Search.
• On Shared Services Administration, click on New SSP. Name it something else. Create a new web application. Point all the databases to the new database server. Create new databases.
• After a successful creation, you should be able to see two Shared Services. Change Default SSP to the new one. Delete the old Shared Services. To delete, hover over SharedServices1, and on the drop down menu, select Delete. Check "Remove without deleting the associated database." (NOTE: if you delete the associated database, you will lose all your existing profile information and audiences.) Once the SSP is deleted, the web application associated to it will be assigned under the default SSP.
• Restore SSP. You can assign a name same as the deleted one - so you won't get confused. But it doesn't have to. Select the web application that was previously associated with the deleted SSP. On the SSP database, select the previously restored database - SharedServices1_DB. (NOTE: when restoring, the DB should already exist.) On the Search database, you can enter a new DB name or same as the old one. This search DB will be created.
• iisreset /noforce
• Run Configuration Wizard.
• Check the newly restored Shared Services Portal (SSP). Ensure that the content sources, rules, scopes are there - just like before.
• Run full crawls.
• Delete the new SSP you created on Step 3.
• Delete the web application associated to the newly-created SSP. Central Administration > Application Management > Delete Web Application

Correct Configuration Database

• After doing all the steps above, you'll still find in Central Administration that your default configuration database is the old DB server. Open SharePoint_Config database on the new DB server.

select * from objects where name = 'SharePoint_Config'

Note down the ID of the other row where parent ID is not equal to its own. Let's call this ID1.

Id	ParentId	Name	Remarks
91C0FDF7-4B52-4DE6-A336-010F1AE3C921	B57DC01E-40D5-4FB6-A8A4-9E58E19ECCC0	SharePoint_Config	ID1
AF086F84-9931-4C0D-9AB5-342103B2272D	AF086F84-9931-4C0D-9AB5-342103B2272D	SharePoint_Config

• Get  ID fo the new SQL Server. Let's call this ID2.

Id	ParentId	Name	Remarks
F9209CAB-4E05-4202-A262-8F696C40FF49	AF086F84-9931-4C0D-9AB5-342103B2272D	<new sql server>	ID2

• Select ID from objects where parentid = ID2. Note the ID as ID3.
  

  Id	ParentId	Name	Remarks
  2B496A84-8202-4421-B966-E2DBAB8469B0	F9209CAB-4E05-4202-A262-8F696C40FF49	<new DB instance,port>	ID3

• Replace the IDs on the statement. Update objects set parentid = ID3 where ID = ID1

Still the database will still be listed on the "Servers in the Farm".  But it's no longer the default configuration server. To remove the old database server,

• Select * from objects for any references on the old DB server
  

  Id	ParentId	Name	Remarks
  3B55AC46-8729-494C-A1D5-546EFCFC577E	AF086F84-9931-4C0D-9AB5-342103B2272D	<old db server>
  B57DC01E-40D5-4FB6-A8A4-9E58E19ECCC0	3B55AC46-8729-494C-A1D5-546EFCFC577E	<old db instance, port>

• Select * from dependencies where object ID is  any of the above IDs
• select * from objects to get the corresponding ID of the DB instance and port
  

  Id	ParentId	Name
  2B496A84-8202-4421-B966-E2DBAB8469B0	F9209CAB-4E05-4202-A262-8F696C40FF49	<new DB instance, port>

• Update dependencies  set the object ID to the new one.

 After performing the above step, you will be able to remove the server from the farm.