Steps for Migrating WSS 3.0 from Windows Internal Database to Microsoft SQL 2005

[Jim S]

Providing this as a point of reference for other people that may have
done the stupid things I did when I started playing with Sharepoint
Services 3.0 (like not really reading the installation instructions
when I first installed it and assuming that it would find the SQL 2005
instance I already had in place and use that instead of installing
Windows Internal Database). When I did this I searched for weeks
trying to figure out how to move from WID to SQL and didn't find any
real concrete answers, so I just tried stuff until I got it working.

This scenario involves moving your WSS 3.0 sites from Windows Internal
Database to Microsoft SQL 2005 (doesn't really matter whether it's
local or remote to the main WSS server instance).

1. First Fully backup your current WSS Content databases. I've found
no way to move config databases, and from my testing there seems to be
little reason to do so.
a. Utilize stsadm.exe from a cmd prompt to back up your entire
Sharepoint farm:
stsadm.exe -o backup -url http://<YourRootSite> -filename C:
\SPBackups\FullFarmBackup.dat

(you may want to change the path the backup file is stored
depending on your situation)
b. Verify the backup is complete and browse to the location the
backup file is stored to verify it exists and is of
a reasonable size.
2. Uninstall WSS 3.0 from Add/Remote Programs Control Panel
3. *IMPORTANT* Remove Windows Internal Database - http://support.microsoft.com/kb/920277
If you were utilizing a single-server, basic WSS 3.0 installation
and uninstall it, it will leave behind the Windows Internal Database,
which will cause any new installations of WSS 3.0 to have issues.
4. Reboot the server to finish up any uninstall scripts.
5. Make sure your SQL 2005 database is prepped per the instructions
for installing a WSS 3.0 Server Farm and then install using the steps
for a server farm (even if you'll only be installing on a single
server).
a. http://technet2.microsoft.com/WindowsServer/WSS/en/library/89e4c579-5720-45e0-917e-abeb95266c3e1033.mspx
b. http://technet2.microsoft.com/windowsserver/WSS/en/library/1f505e96-60e2-41ac-bf5d-9739105f047c1033.mspx?mfr=true
6. Once everything is installed and you're using the SharePoint
Products and Technologies Configuration Wizard to configure sharepoint
make sure to point it to your existing SQL server as you go through
the steps.
7. Upon completion of the wizard use WSS 3.0 Central Administration
page to configure your basic settings such as Incomming and outgoing
email settings, logging, search server, etc.
8. In Central Administration->Application Management choose "Create or
Extend Web Application" to create your IIS instance. Follow the
standard steps here.
9. In Central Administration->Content Databases choose "Add a content
database" and use your SQL server. Don't forget to select your search
server that you set up earlier. This will create an empty content
database which we will restore our site from step 1 to.
10. Open a command prompt again. Use stsadm.exe to restore your site
farm:
ststadm.exe -o restore -url http://<YourRootSite> -filename C:
\SPBackups\FullFarmBackup.dat

This step will take a little bit, but should say Operation Completed
Successfully at the end. You should now be able to access your
sharepoint site again using the new SQL server database location.

This should also work as a disaster recovery process starting at step
2 being that you should have daily or weekly scheduled sharepoint
backups using stsadm (and at least monthly SQL database backups to
help with SQL performance and log management).

I'll be jotting it down in my Disaster Recovery plan, and welcome any
comments, additions, or changes that might streamline this process
from other people that have done this sort of move.

Thanks,