How to migrate SharePoint (WSS, MOSS) databases?

I was asked to do a migration of a wss site database from Windows Internal Database to SQL Server 2008. Well this is not my area of work but since I had some free time my assistance was requested.

There were many articles on the internet describing how to move the content databases but all of them advised not to move the SharePoint Config and Admin Content databases. The proposed solution was to delete the farm and rebuild the farm (so that new Config and Admin Content databases would be created) and attaché the old content databases. But this is a difficult, time consuming exercise which would also involve losing all the configuration data. After a lot of research and scanning the registry and the SharePoint databases, I was able to achieve my goal with minimal effort and all data secure.

MORE...

Here are the steps:

1. Backup all the content databases.(If you want to know the names of your content databases look at the section “How to find my content databases?”)

2. Backup the Admin Content Database(If you want to know the names of your Admin Content databases look at the section “How to find my content databases?”)

3. Back up the SharePoint Configuration database.(If you want to know the name of your SharePoint Configuration database look at the section “How to find my SharePoint Configuration database?”)

4. Restore all the above databases to the new server.

5. Registry changes to point to the new Admn Config DB.
a. Locate the registry key “HKEY_LOCAL_MACHINESOFTWAREMicrosoftShared ToolsWeb Server Extensions12.0SecureConfigDB”
b. In the “dsn” data, change the name of the database to that of the new database. (Make sure you leave other parameters untouched)

6. Detach all the content databases (including the Admin Content Database). Please note that you should NOT detach/attach the SharePoint Configuration database. Refer to the section “How to detach/attach the content databases” below.

7. Now you can stop your old database server and all should be fine.

How to find my content databases?
1. Go to "SharePoint Central Administration"

2. Click on "Application Management" tab.

3. Click on the “Content databases” link under the “Application Security” group.

4. Select each web application by selecting from the “Web Application” dropdown on the top fight of that page. You will find the content database for each of them listed.


How to find my SharePoint Configuration database?
In your database server you would find one database which has the text “SharePoint_Config” as part of its name. This is your SharePoint Configuration database.

How to detach/attach the content databases?
1. First detach the current content database
stsadm-o Deletecontentdb -url “http://mysite:2000” -databasename “content_database_name” -databaseserver “my_old_database_server_name”

2. Now attach the new content database
stsadm-o addcontentdb -url “http://mysite:2000” -databasename “content_database _name” -databaseserver “my_new_database_server_name”
Repeat the above steps for all the content databases including the Admin content database.

Note:
If you happen to get an “Access Denied” error then it means the current login is not the the Farm Application Pool account. You could temporarily grant yourself the Farm Administrator by the following command:

stsadm -o updatefarmcredentials -userlogin -password

Then reset IIS using the command:

iisreset /noforce

Before doing the above steps make sure you note down the Application Pool Account by going to

IIS > Application Pools > Your Allplication Pool > Properties > Identity

Also after you have done the migration you need to reset the Application Pool User to the one that was previously defined.


General Notes:
1. Instead of the registry fix you could use the command

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

For a wss installation the command is much simple like

psconfig.exe -cmd configdb -connect -server "NewDBServer" -database "Sharepoint COnfig Database Name"
However I was not able to test this properly and I will not guarantee that it would work properly.

2. For MOSS installations, before detaching the content databases use the “preparetomove” command. This is not necessary in wss (nor the command available)
stsadm -o preparetomove -contentdb “Content_DB_Name” -site

3. All the commands are to be run on the Command prompt