How to move Microsoft SharePoint databases from one SQL server to another

I have been using Ahmed Ammar's excellent instructions for moving MOSS databases. However, I noticed I could only find them in the Google cache, so I have taken the liberty of repeating them in total here, with a couple of notes from me. Ahmed, let me know if you want me to take this down, but folks, in the interests of preserving a very useful resource, here are Ahmed's instructions.

 

Step 1 – Move
SharePoint_AdminContent

Step 2 – Move Sharepoint_Config database

Step 3 – Move all other site collection databases

Step 4 – Restore Indexers and SSP (Shared Service Provider)

Step 5 – Correct configuration database.

Shortcuts:

SSP: SharePoint Service Provider
CA: Central Administration
RDP: Remote Desktop
MOSSSRV: Web Front server hosting SharePoint central administration and web service.
OLDSQL: SQL server you want to move DBs from.
NEWSQL: SQL server you want to move DBs to.

Prerequisite:

Write down version of your SharePoint farm.

New SQL server needs to have mixed security mode set and TCPIP access enabled.

Local SQL user is required for DB access and creation, together with domain search account, that will perform crawling.

Topology

MOSS 2007 SP2 – ver. 12.0.0.6421 with two servers in a farm.  (Win2k3 and W2k8 32 bit systems)

One web front end hosting CA and second SQL Server 2005 Standard Edition.

Step 1 – Move Sharepoint_AdminContent database

Central Administration website is stored in  Sharepoint_AdminContent
Database, thus it cannot be moved from administration interface, however you can see it there under its web application.

0, Login to OLDSQL and make backups of all databases related to SharePoint farm to shared folder.

1, Put DB offline using CA - Application Management – Content Databases – select proper web application "SharePoint Central Administration V3" and change database status offline.

DO NOT CHECK REMOVE!!!

2, Login to MOSSSRV server using RDP

(I recommend you to create BAT files with commands, in case you mistype character and close CMD.EXE window by mistake.)

Launch prepare to move command from CMD.EXE or BAT file:

STSADM –o preparetomove –contentdb OLDMOSS:SharePoint_AdminConfig_0245-87547-a547f" –site http://mosssrv:4235

 After Operation completed successfully, disconnect DB using:

STSADM –o deletecontentdb –url "http://mosssrv:4235" –databasename "SharePoint_AdminConfig_0245-5698-485-asd21"

3, Switch to OLDSQL server and in SQL Management studio backup SharePoint_AdminContent_2254-sdsa454-45454
database.

After backup, copy this DB to NEWSQL and restore database. Now it's the best time to rename database just to "SharePoint_AdminContent" and get rid of GUID.

4, Go back to MOSSSRV and launch attach DB command :

STSADM –o addcontentdb –url http://mosssrv:4235 –databasename "SharePoint_AdminConfig" -databaseserver "NEWSQL"

5, After operation completed appear, launch command iisreset /noforce.

6, Check http://mosssrv:4235 – e.g. CA website if it is functioning.

 Now you moved Sharepoint_AdminContent
database and renamed it to more understandable name.

Step 2 - Move Sharepoint_Config database

Most difficult part from SharePoint structure view is moving Sharepoint_Content database. This is the first database created and holds settings of your Sharepoint structures.

1,Go to OLDSQL and backup Sharepoint_Content database with suffix "before_move" for example "SharePoint_Config_before_move.bak"

2, Go to MOSSSRV and launch command to disconnect database from server  :

Psconfig.exe –cmd configdb –disconnect

3, Go to OLDSQL server and in SQL Management studio backup SharePoint_Config
database.

After backup, copy this DB to NEWSQL and restore database.

4, Connect database back to sharepoint farm using :

psconfig.exe -cmd configdb  -connect -server "newsql" -database "SharePoint_Config" -dbuser "mossdbcreate" -dbpassword "Aaa123456" -user "DOMAIN\USER" -password "user_password"

Notes:

·         mossdbcreate user is local NEWSQL user with permission to create DB.

·         DOMAIN\USER is user that was set as Sharepoint Administrator. ( I used domain admin account for all these operations.)

·         Your new "SharePoint_AdminContent" database will be used automatically.

Naaman's Life Note:

This didn't work for me, so in the end I ran the following command, which worked:

psconfig.exe -cmd configdb  -connect -server "newsql" -database "SharePoint_Config" -user "DOMAIN\USER" -password "user_password"

5, Launch Sharepoint Technology wizard and provision new CA :

·         Choose "No do not disconnect" and click NEXT,

·         Change the port to the same you had before e.g.  4235 in our case !! and leave NTLM and click NEXT

·         Wizard will now display summary page (already filled with NEWSQL name) and will start creating CA website.After finish it will open you CA website.

Browse around to check everything is functioning…try SSP Admin page for example.

Now we almost finished move of Sharepoin_Config DB, but this step needs to be finished as described in last step. ( You may notice, that in Operations – Server in Farm is written OLDSQL as home of configuration database.)

  
 

Step 3 - Move all SharePoint Site Collection Content Databases

Now move all production "normal" databases.

1, restore your production DBs from step 0 to NEWSQL or backup them all again at OLDSQL and restore in NEWSQL.

2, Go to CA – Application Management – Content DB

3, Change the web application from the drop down list appropriately.

4, open content DB, write down its name, select "offline" and check "Remove Content Database". (If database was created using special account use that account to delete it).

5, After removal, click "Add Content Database" and add the same database but from NEWSQL.

  
 

Repeat these steps for all your production databases.

Note: Some sources are recommending to use chain : preparetoremove > disconnect > connect (same as in Sharepoint_AdminContent) to move all content databases. This is probably needed, if you are moving databases to different farm environment and not to the same Sharepoint_Config database. It has something to do with site and DB GUID.

  
 

Step 4 – Restore SSP

Shared Service Provider – is a feature of Sharepoint Farm and you will not found it by WSS. As name is saying it goes about services, that could be shared in a farm. For example crawling (or indexing or searching) could be configured here and one server can perform crawling and its searchDB could be then used be then used by other servers in a farm.

1, Go to OLDSQL and backup SharedServiceProved_DB (in fact I do not know default name, but there are two databases for SSP – one is content and second is search.)

Then go to NEWSQL and restore this DB. There is no reason to restore search DB, because it will be created anyway.

2, Open CA -  In order to restore SSP from a database you need to enable indexers. This is done in Operations – Servers on Farm – Office SharePoint Server Search.

You need to provide content access account – see prerequisites. You can then leave default name of search DB – WSS_SEARCH_MOSSSRV.

3, In the same menu enable also second Office search service.

4, Go back to SSP Administration page – now choose "Restore SSP"

     Fill the form with existing DB name you restored to NEWSQL and click OK.

Note : Restoration and provisioning process of new SSP could take long time.

You can also get into trouble here, especially if SharePoint's versions are different. You need to be sure, you are restoring SSP from same version of SharePoint system.

5, After successful finish you should see two providers. Now, by clicking "Change associations" change association of old SSP's web applications to new one.

6, By clicking on "Change Default SSP" you change default SSP to restored one.

7, Delete old SSP with option "and delete the associated databases". After clicking OK, un-provisioning of old SSP will occur and it takes a lot of time. May be you will need to restart IIS with /noforce if system will stop responding.

Now you are done with moving all the databases. Before continuing, please double-check all content databases are transferred. You can stop SQL Server service on OLDSQL and check all sites and CA.

 
 

Step 5 – Correct configuration database

As you may noticed, no one told Sharepoint_Config, that configuration database is not on old server, but someplace else. In fact this should be done by disconnecting and connecting operations done by SharePoint Wizard, but system writes, that config DB is still on OLDSQL in CA.

Fact is, system is now using NEWSQL for SharepointConfig, but some sub-systems may not work, because somewhere is still written OLDSQL.

In my case, creating site collection from stsadm in new contentdb was issuing error, that DB is not accessible. Unfortunately, system did not write which DB, so I naturally thought it was NEWSQL, that is not accessible and I have bad credentials, but content DB was created, so what was wrong??

I then started OLDSQL resp. SQL service there is tried again.

So I looked at NEWSQL – directly into SharePoint_Config database and find out you have to change Name in first row in dbo.Objects table, where OLDSQL still resides.

I did it using "New Query" button in SQL Management studio and executing this SQL command.

update dbo.Objects set Name = replace(Name, 'oldsql','newsql.ccc.com')

Note : You cannot use newsql name only, because then system reports an error. My workaround was to use FQDN e.g. with dns name like MS's favorite contoso.local  - it is unique in table, and still valid for the system. Probably you can create DNS A record and use this one instead. It will anyway point to same location.