This section specifically talks about steps where you need to move all the SharePoint 2007 database from SQL Sever (SQL 2000) to a new SQL Server (SQL 2008).
Note:The challange in this scenario is that we cannot use the same SQL instance name on the new SQL server
I am putting down an outline of steps to move databases to a new SQL server (alias name cannot be mentioned). Again this is an outline steps keeping in mind that you have an Out of the Box (un-customized) SharePoint site.
1. Start the SharePoint Products and Technologies Configuration Wizard and disconnect the web server from the farm
Launch SharePoint Products and Technologies Configuration Wizard and in Modify Server Farm Settings > select the option Disconnect from this server farm > Click Next
Once the server is disconnected from the Farm reboot the server
Check if there are any SharePoint web sites and application pool in IIS Manager, if yes delete them
2. Take a SQL backup of the SharePoint content Databases from the old SQL Server
3. Restore SharePoint content Databases to the new SQL Server
4. Using the SharePoint Products and Technologies Configuration Wizard define a new farm in the SQL server, that creates a new configuration database
a) Launch SharePoint Products and Technologies Configuration Wizard
b) On the Welcome to SharePoint Products and Technologies page, click next.
c) Click yes in the dialog box that notifies you that some services might need to be restarted during configuration.
d) On the Connect to a server farm page, click No, I want to create a new server farm, and then click Next.
e) In the Specify Configuration Database Settings dialog box, in the Database server box, type the name of the computer that is running SQL Server (new SQL Server name).
f) Type a name for your configuration database in the Database name box, or use the default database name. The default name is "SharePoint_Config"
g) In the User name box, type the user name of the Server farm account. (Be sure to type the user name in the format DOMAIN\username.)
Important: The server farm account is used to access your configuration database. It also acts as the application pool identity for the SharePoint Central Administration application pool, and it is the account under which the Windows SharePoint Services Timer service runs. The SharePoint Products and Technologies Configuration Wizard adds this account to the SQL Server Logins, the SQL Server Database Creator server role, and the SQL Server Security Administrators server role. The user account that you specify as the service account must be a domain user account, but it does not need to be a member of any specific security group on your Web servers or your back-end database servers. We recommend that you follow the principle of least privilege and specify a user account that is not a member of the Administrators group on your Web servers or your back-end servers.
h) In the Password box, type the user's password, and then click Next.
i) On the Configure SharePoint Central Administration Web Application page, select the Specify port number check box and type a port number if you want the SharePoint Central Administration Web application to use a specific port, or leave the Specify port number check box cleared if you do not care which port number the SharePoint Central Administration Web application uses.
j) In the Configure SharePoint Central Administration Web Application dialog box keep default settings
k) On the Completing the SharePoint Products and Technologies Configuration Wizard page, click Next.
l) On the Configuration Successful page, click Finish.
m) The SharePoint Central Administration Web site home page opens.
5. Create and configure a Shared Services Provider
a) Start the Office SharePoint Server Search service
i. On the SharePoint Central Administration home page, click the Operations tab on the top link bar.
ii. On the Operations page, in the Topology and Services section, click Serviceson server.
iii. Click Start next to Office SharePoint Server Search.
iv. Select the Use this server for indexing content check box and Use this server for servicing search queries check box. This expands the page and adds the Index Server Default File Location, Indexer Performance, and Web Front End and Crawling sections.
v. In the Contact E-mail Address section, type the e-mail address you want external site administrators to use to contact your organization if problems arise when their sites are being crawled by your index server.
vi. In the Farm Search Service Account section, specify the User name and Password of the account under which the search service will run. This domain account should not be a member of the Farm Administrators group in the Central Administration Web site (the WSS_ADMIN_WPG Windows security group). For least privilege scenarios, this should be a separate domain account, used only for this service. For more information about this account, see Plan for administrative and service accounts (Office SharePoint Server).
vii. Optionally, you can also configure other settings or accept the default settings.
viii. When you have configured all the settings, click Start.
b) Create a Web application to host the SSP and create the SSP
i. On the SharePoint Central Administration home page, click the Application Management tab on the top link bar.
ii. On the Application Management page, in the Office SharePoint Server Shared Services section, click Create or configure this farm's shared services.
iii. On the Manage this Farm's Shared Services page, click New SSP.
iv. On the New Shared Services Provider page, in the SSP Name section, click Create a new Web application.
Note: If you see any items in the Web application drop-down list, a Web application has already been created. You can either use this Web application or create another.
v. On the Create New Web Application page, in the Application Pool section, specify the User name and Password for the user account that the Web application pool will run under.
vi. You can also configure other settings on this page, or click OK to create the new Web application.
Note: By default, the Web application uses the default Web site in IIS and port 80. This port might be used by other Web applications. Ensure that this port is open for use, or choose another port before you click OK.
By default, Restart IIS Manually is selected. If you use this setting, you must restart the default Web site in IIS, or restart the W3C service by using the command line.
vii. On the New Shared Services Provider page, in the SSP Service Credentials section, type the user name and password for the user account that the SSP service will run under.
viii. Optionally, you can also configure other settings.
ix. When you have configured all the settings, click OK.
x. If you used the same Web application for the SSP administration site and the My Sites site collection, you will be prompted to use separate Web applications for these site collections. If you want to use the same Web application, click OK. For more information about site planning, see Plan Web site structure and publishing (Office SharePoint Server).
xi. After the SSP has been created, click OK on the confirmation page that appears.
6. Create a new Web application
a) Click the Start button, point to All Programs, then point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
b) On the Central Administration home page, click Application Management.
c) On the Application Management page, in the SharePoint Web Application Management section, click Create or extend Web application.
d) On the Create or Extend Web Application page, in the Adding a SharePoint Web Application section, click Create a new Web application.
e) On the Create New Web Application page, in the IIS Web Site section, you can configure the settings for your new Web application.
i. To choose to use an existing Web site, select Use an existing Web site, and specify the Web site on which to install your new Web application by selecting it from the drop-down menu.
ii. To choose to create a new Web site, select Create a new IIS Web site, and type the name of the Web site in the Description box.
iii. In the Port box, type the port number you want to use to access the Web application. If you are creating a new Web site, this field is populated with a suggested port number. If you are using an existing Web site, this field is populated with the current port number.
iv. In the Host Header box, type the URL you wish to use to access the Web application. This is an optional field.
v. In the Path box, type the path to the site directory on the server. If you are creating a new Web site, this field is populated with a suggested path. If you are using an existing Web site, this field is populated with the current path.
vi. In the Security Configuration section, configure authentication and encryption for your Web application.
(.) In the Authentication Provider section, choose either Negotiate (Kerberos) or NTLM.
(..) In the Allow Anonymous section, choose Yes or No. If you choose to allow anonymous access, this enables anonymous access to the Web site using the computer-specific anonymous access account (that is, IUSR_<computername>).
(...) In the Use Secure Sockets Layer (SSL) section, select Yes or No. If you choose to enable SSL for the Web site, you must configure SSL by requesting and installing an SSL certificate.
vii. In the Load Balanced URL section, type the URL for the domain name for all sites that users will access in this Web application. This URL domain will be used in all links shown on pages within the Web application. By default, the box is populated with the current server name and port.
The Zone box is automatically set to Default for a new Web application, and cannot be changed from this page. To change the zone for a Web application, see Extend an existing Web application later in this article.
viii. In the Application Pool section, choose whether to use an existing application pool or create a new application pool for this Web application. To use an existing application pool, select Use existing application pool. Then select the application pool you wish to use from the drop-down menu.
(.) To create a new application pool, select Create a new application pool.
(..) In the Application pool name box, type the name of the new application pool, or keep the default name.
(...) In the Select a security account for this application pool section, select Predefined to use an existing application pool security account, and then select the security account from the drop-down menu.
(....) Select Configurable to use an account that is not currently being used as a security account for an existing application pool. In the User name box, type the user name of the account you wish to use, and type the password for the account into the Password box.
ix. In the Reset Internet Information Services section, choose whether to allow Microsoft Windows SharePoint Services to restart IIS on other farm servers. The local server must be restarted manually for the process to finish. If this option is not selected and you have more than one server in the farm, you must wait until the IIS Web site is created on all servers and then run iisreset /noforce on each Web server. The new IIS site is not usable until that action is completed. The choices are unavailable if your farm only contains a single server.
x. Under Database Name and Authentication, choose the database server, database name, and authentication method for your new Web application.
xi. Click OK to create the new Web application, or click Cancel to cancel the process and return to the Application Management page.
7. Remove the content database from the newly created web application
a) Browse to Central Administration > Application Management > Content Databases
b) Select the newly created Web application
c) Click on the database listed
d) Select the check box ?emove Content Database? e) Click Ok
8. Add the content database to the newly created web application Using SharePoint Central Administration
a) Browse to Central Administration > Application Management > Content Databases
b) Select the newly created Web application
c) Add a content database
d) In the page that comes up provide the Database server name and the restored database name. All other settings can be left at default
e) Click Ok
Note: repeat this step if you have more than 1 content database for the particular web application.
Using Command line
Go to Start > Run > cmd > OK
cd c:\Program Files (x86)\Common Files\Microsoft Shared\Web Server Extensions\12\bin
stsadm -o addcontentdb -url http://<sitename> -databasename <content databasename>
Now if you have customizations on your site, then those needs to be backed up and redone on the servers once you have disconnected the server from the existing farm and create a new farm. Again this needs to be tested as this would depend on how the customizations have been implemented in your environment.