Steps to move SQL server from one machine to another

STEP BY STEP PROCEDURE TO MOVE SQL SERVER FROM ONE MACHINE TO ANOTHER :-

1.) Back Up Data
a.) Click Start, point to All Programs, point to SharePoint Portal Server, and
then click SharePoint Portal Server Data Backup and Restore.
b.) The Microsoft Office SharePoint Portal Server 2003 Backup and Restore dialog
box opens.
c.) Click the Backup tab.
d.) In the Backup location and name box, do the following:
e.) Type or browse to the location where you want to store the backup files.
f.) After the folder name, type the file name that will be the prefix for each
backup file.
g.) In the tree view, expand Available components by clicking the plus sign.
h.) Click the components that you want to back up.
i.) In the Backup comment box, type a comment.
j.) Optionally, select the Overwrite existing files check box.
k.) Click Backup.
l.) On the successful completion message box, click OK.


2.) Delete Portal Site, leave databases by doing the following:
a.) Go to the SharePoint Portal Server Central Administration for portal>
b.) Go to the heading "Portal Site and Virtual Server Configuration"\select "List
and manage portal sites"
c.) select your Portal website and in the drop down box select "Delete Portal
Sites"
d.) It will launch a "Delete dialog box
e.) Next to the heading "Associated Databases", it has a checkbox named "delete
all database" make sure this checkbox is NOT checked, then click OK


3.) Go to Topology page, remove all roles by doing the following:
a.) Go to SharePoint Portal Server Central Administration for portal>
b.) go to the heading "Server Configuration"\select "Configure server topology"
c.) SharePoint Portal Server Central Administration Configure Server Topology
page
d.) select "Change Component Assignments" button and uncheck everything next to
Component Assignment, job, server, index, and set your "Job Server" to none.
e.) click OK
(FYI: If it prompts you for username and password, go ahead and enter an admin's
account)


4.) Disconnect from Config DB by doing the following:
a.) Go to SharePoint Portal Server Central Administration for portal>
b.) go to the heading "Server Configuration"\select "Configure configuration
database server"
c.) select the radio button "Disconnect from configuration database" this
references all the old SQL Server location\click OK
(FYI: When you delete your config database and create a new one you do not lose
anything. It does not contain any data in it. It is just a connection between
sharepoint and SQL.)


5.) Create new config database, on new SQL server by doing the following:
a.) Go to SharePoint Portal Server Central Administration for portal>
b.) go to the heading "Server Configuration"\select "Configure configuration
database server"
c.) select the radio button "Create configuration database"
enter all your new SQL Server database name
d.) click OK


5.) VIA SQL, backup the SQL databases from the old server and restore to the new
server by doing the following:
a.) go to the old SQL Server Database and you will have three database to backup
up the SITE, SERV, and PROF
b.) right click on one of your databases\select "All Tasks"\"Backup database..."
c.) under the General Tab\select the database name, select Add button
d.) select Filename and put the path and the name of one your backup like
portalsite_backup.bak
e.) click OK and do this for the other ones like SERV and PROF
f.) You do not need to backup the Config database we went a clean one from the new
SQL Server database.
g.) Go to your new SQL Server Enterprise Manager and we have to restore all the
databases from the old server to the new one
h.) right click on Databases folder\select "All Tasks"\"Restore Database..."
i.) under General tab enter the name you want the "Restore database as" for
example SITE
j.) Next to the Restore heading select the "From Device" radio button\select the
"select devices" button
k.) select Add button\ in the Filename enter the path to your backup file that you
created earlier that I called portalsite_backup.bak
l.) select OK and make sure Database - Complete is checked.
m.) now do the restore steps for your backup SERV and PROF databases to this new
SQL server


6.) Go to Topology page, add all roles by doing the following:
a.) Go to SharePoint Portal Server Central Administration for portal>
b.) go to the heading "Server Configuration"\select "Configure server topology"
c.) SharePoint Portal Server Central Administration Configure Server Topology
page
d.) select "Change Component Assignments" button and check everything next to
Component Assignment, job, server, index, and set "Job Server" to your new server
e.) click OK


7.) Create a new portal site, choose the restore option by doing the following:
a.) Go to the SharePoint Portal Server Central Administration page
b.) Go to the heading "Portal Site and Virtual Server Configuration"\select
"Create a portal site"
c.) in the Create Portal Site dialog box select the radio button "Restore a
portal"
In the section Restore Portal From Database this will let you specify the new SQL
server and restored databases
Enter all our new SQL Server name and pointing to the SITE, SERV, and PROF on your
new SQL Server
d.) select OK

Refresh your website and see if your portal is up and running