Recently I had to move all databases including the content database in a MOSS 2007 farm from one SQL server to another. I tried few methods and found the following steps are the easiest:

[Assumption: All databases in the farm are on one SQL server and the SQL server was not assigned to any other roles]

  1. Quiesce the farm ... make sure no one is accessing the farm
  2. List all databases in the farm (http://blogs.msdn.com/johnlee/archive/2008/03/09/list-all-databases-in-a-moss-2007-farm.aspx)
  3. Detach all databases from the above list
  4. Copy all databases to another SQL server and attach all of them
  5. Go to one of Web Front End (WFE) server
    • Launch regedit and navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB, double click the dsn and change the Data Source from the original SQL Server name to new SQL Server name
    • Run stsadm.exe -o renameserver -oldservername <OldSQLServerName> -newservername <NewSQLServerName>
  6. Repeat step 5 on all WFE and other servers except the SQL server
  7. Reboot all servers except the SQL server

[Assumption: WSS/MOSS was installed on a single box - the new SQL server name will be "NewSQL"]

  1. Introduce the new SQL server ("NewSQL") into this farm by creating a web application pointed to the new SQL Server
  2. Quiesce the farm ... make sure no one is accessing the farm
  3. List all databases in the farm (http://blogs.msdn.com/johnlee/archive/2008/03/09/list-all-databases-in-a-moss-2007-farm.aspx)
  4. Detach all databases from the above list
  5. Copy all databases to a NewSQL server and attach all of them
  6. Open SSMS, pointed to configuration database, run the following script to get the Id for the "NewSQL" and record the ID, let's refer it as "InstanceID_GUID"
    7. select ID from objects 
where parentid = (
    select Id from objects 
    where name = 'NewSQL')
  7. Since all roles are assigned to single server so I could not use stsadm.exe renameserver command to achieve the same goal. What I did is to run the following script to update the parentID of all databases to above instance Guid
    8. UPDATE objects 
SET parentid = 'InstanceID_GUID' 
WHERE id IN 
(
    SELECT o.id 
    FROM objects o
        INNER JOIN classes c on c.id = o.classid     
    WHERE c.Fullname LIKE '%Administration.SPConfigurationDatabase%'
    OR c.Fullname LIKE '%Administration.SPContentDatabase%'
)
  8. Go to one of Web Front End (WFE) server
    • Launch regedit and navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB, double click the dsn and change the Data Source from the original SQL Server name to new SQL Server name
  9. Repeat step 9 on all WFE and other servers except the SQL server
  10. Reboot all servers except the SQL server
  11. Change the "Default database server" from Central Administration to "NewSQL"

[Disclaimer] The methods stated above are just how I did and provided as is for informational purpose only and I have not tested them in production environment and do not know if they are supported methods.