Several common reasons for needing to move a database from one SQL server to another are:
-Scalability (spreading the load of your SQL server across multiple SQL servers)
-Disaster Recovery (your SQL server had a catastrophic disaster and you need to recover from that event)
-Database Reorganization (you simply need to change the server that hosts your content database or rename the database)
Move SharePoint Content Databases
1. Detach the content database from the source SQL server and copy the .mdf and .ldf to the target SQL server. (Note: while you are moving the database files your users will see "Cannot connect to content database" when visiting their site)
2. Attach the .mdf and .ldf files on the target SQL server as a new database (Note: this is your opportunity to rename the database if you desire)
3. Run the following stsadm commands from the command line on your SharePoint server to complete the move: (Note: the deletecontentdb does not actually delete the database from SQL server, it simply disconnects the database from SharePoint)
stsadm –o deletecontentdb –url http://YourSiteUrl –databasename YourContentDb –databaseserver SourceSqlServer
stsadm –o addcontentdb –url http://YourSiteUrl –databasename YourContentDb –databaseserver TargetSqlServer
Move SharePoint Configuration Database
1. Backup configuration database from the source SQL server
2. Restore the backup onto the target SQL server (Note: this is your opportunity to rename the config database if desired)
3. Run the following stsadm commands from the command line on your SharePoint server to complete the move: (Note: the deleteconfigdb does not actually delete the database from SQL server, it simply disconnects the database from SharePoint)
stsadm -o deleteconfigdb
stsadm –o setconfigdb -connect –databaseserver TargetSqlServer -databasename DatabaseName -farmuser FarmSqlAccount -farmpassword FarmSqlPassword