Migrating SharePoint 2007 SQL Databases
Have you deployed SharePoint 2007 with SQL Server Express? Has it become such a success that you are now out growing SQL Server Express and it's time for the big guns...... Whatever your reason the steps below should help you successfully migrate from one SQL Server to another (doesn't necessarily have to be SQL Express either!):
NOTE: I implemented this is a lab envirnoment. I would recommend this approach before attempting any of the steps below on a production system (to allow for any adjustmensts or tailoring of steps for your setup).
1. Run “SharePoint Products and Technologies Configuration Wizard”
2. Click “Next” followed by “Yes” (stop services)
3. Select “Disconnect from this server farm” and click “Next” followed by “Yes”
4. Click “Finish”
5. Detach all MOSS databases (configuration and content) from SQL Express
6. BACKUP DATABASES
7. Stop SQL Server Express Service
8. Attach all MOSS databases (configuration and content) to new SQL Server 2005
9. Run “SharePoint Products and Technologies Configuration Wizard” again
10. Click “Next” followed by “Yes” (stop services)
11. Select “Yes, I want to connect to an existing server farm”
12. In Database Server text box enter name of server where SQL Server 2005 is installed
13. Click “Retrieve Database Names” – This should return the configuration database now on SQL Server 2005
14. Under Specify Database Access Account enter DOMAIN\User_Name (domain account with access to DB) and password
15. Click “Next”
16. Specify port number for Central Admin - should default to old port number, if not enter old port number
17. Click “Next”
18. Confirm configuration settings and click “Next”
19. Click “Finish”
20. Once complete Central Administration will automatically open up
Within Central Administration you will need to restart any services which are not started and create a new search database.
Hopefully this will have done the trick and you are now up and running again but using a different database :)
Also, the following "Migrate Content Databases" TechNet article is worth reading if you are interested in the topic: https://technet2.microsoft.com/windowsserver/WSS/en/library/03c94172-a203-4d47-bf9f-239bb6647aa61033.mspx?mfr=true