change a named instance to the default instance

We have a SQL 7 that I want to upgrade to SQL 2000 but
the setup wizard does not allow it to do so. The only
option that I'm allowed is to create a named instance.
If I do this and copy all of the databases to the SQL
2000 named instance, can I uninstall SQL 7 and change the
named instance to the default instance?You cannot make a named instance to a default or change name of an instance.
You have do use backup/restore or detach/attach.
Some of the below articles *might* be of help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:047801c3aa2a$3cd905f0$a101280a@.phx.gbl...
> We have a SQL 7 that I want to upgrade to SQL 2000 but
> the setup wizard does not allow it to do so. The only
> option that I'm allowed is to create a named instance.
> If I do this and copy all of the databases to the SQL
> 2000 named instance, can I uninstall SQL 7 and change the
> named instance to the default instance?|||Hi Jim,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you'd like to upgrade SQL 7 to SQL
2000. However, I'm unsure of the meaning "The only option that I'm allowed
is to create a named instance." When upgrading, SQL Server 7.0
automatically becomes the default instance of SQL Server 2000.
As we known, you can overwrite the existing installation of SQL Server 7.0
by installing a default instance of SQL Server 2000. You can also keep the
installation of SQL Server 7.0 intact by installing a named instance of SQL
Server 2000. Both operations are performed using the following procedure.
To upgrade SQL Server 7.0 to SQL Server 2000
1. Insert the Microsoft SQL Server 2000 compact disc for the edition to
which you want to upgrade into your CD-ROM drive. If the compact disc does
not autorun, double-click Autorun.exe in the root directory of the compact
disc.
2. Select SQL Server 2000 Components, select Install Database Server, and
then setup prepares the SQL Server Installation Wizard. At the Welcome
screen, click Next.
3. In Computer Name dialog box, Local Computer is the default option and
the local computer name appears in the edit box. Click Next.
4. In the Installation Selection dialog box, click Upgrade, remove, or add
components to an existing instance of SQL Server, and then click Next.
5. In the Instance Name dialog box, Default will be selected. Click Next.
# Note # When upgrading, SQL Server 7.0 automatically becomes the
default instance of SQL Server 2000.
6. In the Existing Installation dialog box, click Upgrade your existing
installation, and then click Next.
7. In the Upgrade dialog box, you are prompted as to whether you want to
proceed with the requested upgrade. Click Yes, upgrade my <text specific to
the upgrade> to start the upgrade process, and then click Next. The upgrade
runs until finished.
8. In the Connect to Server dialog box, select an authentication mode, and
then click Next.
If you are not sure which mode to use, accept the default: The Windows
account information I use to log on to my computer with (Windows).
9. In Start Copying Files dialog box, click Next.
10. In the Setup Complete dialog box, click Yes, I want to restart my
computer now, and then click Finish.
Additionally, instead of a full upgrade from SQL Server 7.0 to SQL Server
2000, which overwrites SQL Server 7.0, you can upgrade SQL Server 7.0
databases and associated meta data. To upgrade a SQL Server 7.0 database to
a SQL Server 2000 database, please use the following procedure:
1. Expand a server group, and then expand a server.
2. Right-click the server, point to All Tasks, and then click Copy Database
Wizard.
3. Complete the steps in the wizard.
After upgrading databases from SQL Server 7.0, run sp_updatestats (update
statistics) against the database on the destination server to ensure
optimal performance of the copied database.
Jim, does this answer your question? Please feel free to let me know if
this help solves your problem. If there is anything more I can still
assist you with, please feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You are correct in that I want to upgrade my SQL 7 to SQL
2000. I followed the steps you have below But when I get
to step 5 the wizard comes up with an empty instance text
box and will not allow me to use the word Default. It
says that there is an error in the current SQL 7 install
that the Client tools or the registry may have been
modified and only gives me the option to install a named
instance.
I need the upgrade to be the default instance because
there is a large number of programs that call the
database with the default instance. I am thinking that I
might be able to detach all of the databases then
uninstall SQL 7 and install SQL 2000 and finish up by
reattahing the databases.
I am conserned that I will lose scheduled jobs,
maintenace programs and DTS packages by doing the detach
attach method. Is there a way to ensute that I can bring
these other objects to the new SQ: 2000?
>--Original Message--
>Hi Jim,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>First of all, I would like to confirm my understanding
of your issue.
>From your description, I understand that you'd like to
upgrade SQL 7 to SQL
>2000. However, I'm unsure of the meaning "The only
option that I'm allowed
>is to create a named instance." When upgrading, SQL
Server 7.0
>automatically becomes the default instance of SQL Server
2000.
>As we known, you can overwrite the existing installation
of SQL Server 7.0
>by installing a default instance of SQL Server 2000. You
can also keep the
>installation of SQL Server 7.0 intact by installing a
named instance of SQL
>Server 2000. Both operations are performed using the
following procedure.
>To upgrade SQL Server 7.0 to SQL Server 2000
>1. Insert the Microsoft SQL Server 2000 compact disc
for the edition to
>which you want to upgrade into your CD-ROM drive. If the
compact disc does
>not autorun, double-click Autorun.exe in the root
directory of the compact
>disc.
>2. Select SQL Server 2000 Components, select Install
Database Server, and
>then setup prepares the SQL Server Installation Wizard.
At the Welcome
>screen, click Next.
>3. In Computer Name dialog box, Local Computer is
the default option and
>the local computer name appears in the edit box. Click
Next.
>4. In the Installation Selection dialog box, click
Upgrade, remove, or add
>components to an existing instance of SQL Server, and
then click Next.
>5. In the Instance Name dialog box, Default will be
selected. Click Next.
> # Note # When upgrading, SQL Server 7.0
automatically becomes the
>default instance of SQL Server 2000.
>6. In the Existing Installation dialog box, click
Upgrade your existing
>installation, and then click Next.
>7. In the Upgrade dialog box, you are prompted as to
whether you want to
>proceed with the requested upgrade. Click Yes, upgrade
my <text specific to
>the upgrade> to start the upgrade process, and then
click Next. The upgrade
>runs until finished.
>8. In the Connect to Server dialog box, select an
authentication mode, and
>then click Next.
>If you are not sure which mode to use, accept the
default: The Windows
>account information I use to log on to my computer with
(Windows).
>9. In Start Copying Files dialog box, click Next.
>10. In the Setup Complete dialog box, click Yes, I
want to restart my
>computer now, and then click Finish.
>
>Additionally, instead of a full upgrade from SQL Server
7.0 to SQL Server
>2000, which overwrites SQL Server 7.0, you can upgrade
SQL Server 7.0
>databases and associated meta data. To upgrade a SQL
Server 7.0 database to
>a SQL Server 2000 database, please use the following
procedure:
>1. Expand a server group, and then expand a server.
>2. Right-click the server, point to All Tasks, and
then click Copy Database
>Wizard.
>3. Complete the steps in the wizard.
>After upgrading databases from SQL Server 7.0, run
sp_updatestats (update
>statistics) against the database on the destination
server to ensure
>optimal performance of the copied database.
>
>Jim, does this answer your question? Please feel free to
let me know if
>this help solves your problem. If there is anything
more I can still
>assist you with, please feel free to post it in the
group.
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>|||> I am conserned that I will lose scheduled jobs,
> maintenace programs and DTS packages by doing the detach
> attach method. Is there a way to ensute that I can bring
> these other objects to the new SQ: 2000?
You can't restore msdb. But you can script the jobs alerts etc (EM, Management, Agent ... Script).
But I very much doubt it'll work for maint wiz. However, that shouldn't be too much work to redo
through the wizard. Also, don't forget stuff in master.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message news:017801c3af8f$a20b0080$a301280a@.phx.gbl...
> You are correct in that I want to upgrade my SQL 7 to SQL
> 2000. I followed the steps you have below But when I get
> to step 5 the wizard comes up with an empty instance text
> box and will not allow me to use the word Default. It
> says that there is an error in the current SQL 7 install
> that the Client tools or the registry may have been
> modified and only gives me the option to install a named
> instance.
> I need the upgrade to be the default instance because
> there is a large number of programs that call the
> database with the default instance. I am thinking that I
> might be able to detach all of the databases then
> uninstall SQL 7 and install SQL 2000 and finish up by
> reattahing the databases.
> I am conserned that I will lose scheduled jobs,
> maintenace programs and DTS packages by doing the detach
> attach method. Is there a way to ensute that I can bring
> these other objects to the new SQ: 2000?
> >--Original Message--
> >Hi Jim,
> >
> >Thank you for using MSDN Newsgroup! It's my pleasure to
> assist you with
> >your issue.
> >
> >First of all, I would like to confirm my understanding
> of your issue.
> >
> >From your description, I understand that you'd like to
> upgrade SQL 7 to SQL
> >2000. However, I'm unsure of the meaning "The only
> option that I'm allowed
> >is to create a named instance." When upgrading, SQL
> Server 7.0
> >automatically becomes the default instance of SQL Server
> 2000.
> >
> >As we known, you can overwrite the existing installation
> of SQL Server 7.0
> >by installing a default instance of SQL Server 2000. You
> can also keep the
> >installation of SQL Server 7.0 intact by installing a
> named instance of SQL
> >Server 2000. Both operations are performed using the
> following procedure.
> >
> >To upgrade SQL Server 7.0 to SQL Server 2000
> >1. Insert the Microsoft SQL Server 2000 compact disc
> for the edition to
> >which you want to upgrade into your CD-ROM drive. If the
> compact disc does
> >not autorun, double-click Autorun.exe in the root
> directory of the compact
> >disc.
> >2. Select SQL Server 2000 Components, select Install
> Database Server, and
> >then setup prepares the SQL Server Installation Wizard.
> At the Welcome
> >screen, click Next.
> >3. In Computer Name dialog box, Local Computer is
> the default option and
> >the local computer name appears in the edit box. Click
> Next.
> >4. In the Installation Selection dialog box, click
> Upgrade, remove, or add
> >components to an existing instance of SQL Server, and
> then click Next.
> >5. In the Instance Name dialog box, Default will be
> selected. Click Next.
> > # Note # When upgrading, SQL Server 7.0
> automatically becomes the
> >default instance of SQL Server 2000.
> >
> >6. In the Existing Installation dialog box, click
> Upgrade your existing
> >installation, and then click Next.
> >7. In the Upgrade dialog box, you are prompted as to
> whether you want to
> >proceed with the requested upgrade. Click Yes, upgrade
> my <text specific to
> >the upgrade> to start the upgrade process, and then
> click Next. The upgrade
> >runs until finished.
> >8. In the Connect to Server dialog box, select an
> authentication mode, and
> >then click Next.
> >If you are not sure which mode to use, accept the
> default: The Windows
> >account information I use to log on to my computer with
> (Windows).
> >9. In Start Copying Files dialog box, click Next.
> >10. In the Setup Complete dialog box, click Yes, I
> want to restart my
> >computer now, and then click Finish.
> >
> >
> >Additionally, instead of a full upgrade from SQL Server
> 7.0 to SQL Server
> >2000, which overwrites SQL Server 7.0, you can upgrade
> SQL Server 7.0
> >databases and associated meta data. To upgrade a SQL
> Server 7.0 database to
> >a SQL Server 2000 database, please use the following
> procedure:
> >
> >1. Expand a server group, and then expand a server.
> >2. Right-click the server, point to All Tasks, and
> then click Copy Database
> >Wizard.
> >3. Complete the steps in the wizard.
> >After upgrading databases from SQL Server 7.0, run
> sp_updatestats (update
> >statistics) against the database on the destination
> server to ensure
> >optimal performance of the copied database.
> >
> >
> >Jim, does this answer your question? Please feel free to
> let me know if
> >this help solves your problem. If there is anything
> more I can still
> >assist you with, please feel free to post it in the
> group.
> >
> >Best regards,
> >
> >Billy Yao
> >Microsoft Online Support
> >----
> >Get Secure! - www.microsoft.com/security
> >This posting is provided "as is" with no warranties and
> confers no rights.
> >Please reply to newsgroups only. Thanks.
> >
> >
> >.
> >|||Hi Jim,
The following step by step KB guide you move the DTS packages and Scheduled Jobs.
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Additionally, if you want to move the database diagrams, please following the following article
to separately move them (otherwise they will be lost):
320125 HOW TO: Move a Database Diagram
http://support.microsoft.com/?id=320125
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
=====================
The issue is not resolved. I'm trying to decide the next steps to take to get the 7.0 Database
upgraded to 2000.
I'm upgrading a Standard edition to a Standard edition. As stated in the thread I need to find
out
what other objects that I need to migrate, and how, that won't be done when I detach and
reattach
the databases. Mainly what comes to mind is the DTS packages, Scheduled Jobs and not
so
important backups and maintenance plans.
A lost of the items and links of where to find How to's would be great.|||Billy,
Thanks for your help. This did the trick. I now have
all the information that I need to do the upgrade this
Saturday. Once again thank you for digging up all of the
articales. They have given me the necessary information
to complete the task.
Take Care
>--Original Message--
>Hi Jim,
>The following step by step KB guide you move the DTS
packages and Scheduled Jobs.
>314546 HOW TO: Move Databases Between Computers That Are
Running SQL Server
>http://support.microsoft.com/?id=314546
>Additionally, if you want to move the database diagrams,
please following the following article
>to separately move them (otherwise they will be lost):
>320125 HOW TO: Move a Database Diagram
>http://support.microsoft.com/?id=320125
>
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>
>=====================>The issue is not resolved. I'm trying to decide the
next steps to take to get the 7.0 Database
>upgraded to 2000.
>I'm upgrading a Standard edition to a Standard edition.
As stated in the thread I need to find
>out
>what other objects that I need to migrate, and how, that
won't be done when I detach and
>reattach
>the databases. Mainly what comes to mind is the DTS
packages, Scheduled Jobs and not
>so
>important backups and maintenance plans.
>A lost of the items and links of where to find How to's
would be great.
>.
>