Moving the Content DB to new Server

On very rare occasion we may need to change the server in which the content database is located. SharePoint provide a very simple solution to facilitate this requirement.

Word of Warning!
Before getting your hands dirty be sure that you have backed up entire farm or have appropriate disaster management plan in case of any catastrophe.

• Open Central Administration and navigate to the application management tab.
• Under SharePoint Web Application Management Section click on the Content Databases.
• Verify you have selected the correct Web Application from the Menu.
• Click on the database to be relocated.
• Select the checkbox labelled "Remove Content Database" -> Accept the confirmation and press OK.
• Now the selected database is removed from the list of the database .
• Open SQL Server Management Studio and connect to the current database server.
• Select the database -> Task -> Click Detach.
• Select the Drop connection check box and press OK.
• Now copy the MDF and LDF file from the current location and paste it to the new server.
• Open SQL Server Management Studio and connect to the new database server and attach this database
• Make sure that the SharePoint database access account is given appropriate access to the new database.
• Now again Open Central Administration -> Application Management -> Content Database -> Select correct web application.
• Click "Add a content database" link.
• Enter new database server name
• Enter moved database name
• Select appropriate authentication mechanism
• Select Search Server and press OK

Verify the availability of your database in the database list and also verify whether your site is working as required.