Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
Ednaldo
Please check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default...b;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:

> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
EdnaldoPlease check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/defaul...kb;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:

> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
EdnaldoPlease check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:
> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

change ip address and machine name

I need to change the ip address and machine name on a server which I have sql server 7.0 enterprise edition running on. Is there any specifics to be concerned over (sql registration, etc.)
thanksHi,
AFAIK SQL Server Wont bother about the IP Address. Once
you change the machine name of SQL BOx, you have to delete
the previous registration and re-register with the new
server name.
Run SQL Server 7.0 Setup from the Product CD. This will
just update SQL Server Internally to reflect new machine
name. Once you are done with that you need to update the
SQL Server by the below two stored procedures
sp_dropserver <old_name> go
sp_addserver <newname>, local go
Stop and Restart the SQL Service.
HTH
--
Regards
THIRUMAL REDDY MARAM
Sys Admin / SQL Server DBA
>--Original Message--
>I need to change the ip address and machine name on a
server which I have sql server 7.0 enterprise edition
running on. Is there any specifics to be concerned over
(sql registration, etc.)?
>thanks
>.
>sql

Change from Enterprise Edition to Developer Edition

We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
Joseph
You can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph
|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:

> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>
|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:

Change from Enterprise Edition to Developer Edition

We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade fro
m
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:

> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a pl
an
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/...y/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>sql

Change from Enterprise Edition to Developer Edition

We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> > We want to install SQL Server 2005 Developer on a server that has SQL
> > Server
> > 2000 Enterprise edition and we are getting an error of you can't upgrade
> > from
> > Enterprise to Developer. The server is not supporting production and has
> > never supported production; it is strictly for development activities. Is
> > there a way we can do that?
> >
> > Thanks,
> >
> > Joseph
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
--
Ekrem Ã?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?
andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
> edtion. May I know what is the best way to do it?
sql

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
> edtion. May I know what is the best way to do it?

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standar
d
> edtion. May I know what is the best way to do it?

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default...b;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.
|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default...b;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> > There's a setting in properties for SQL server in Enterprise Manager where
> > you can point out new default location for databases and logs. I want to
> > do
> > just that, but have two questions:
> >
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> >
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> >
> > [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> >
> > Looking forward to any help.
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/defaul...kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFil
es
> or F:\DBLogFiles). These don't need to match the default SQL Server folde
r
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/defaul...kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John

Celeron Processor

All the documentation says that a Celeron processor will not run MSDE 2000.
Just loaded a small enterprise app that is based on MSDE2000. Now the PC in
question is just out of the box Celeron PC (1.5G M370). Now would this just
be a freak occurance that does not portend fo the the futre?
hi,
CWells wrote:
> All the documentation says that a Celeron processor will not run MSDE
> 2000. Just loaded a small enterprise app that is based on MSDE2000.
> Now the PC in question is just out of the box Celeron PC (1.5G M370).
> Now would this just be a freak occurance that does not portend fo the
> the futre?
AFAIK, there's not such a constraint for MSDE not running on celeron
processors... (http://www.google.it/search?hl=it&q=...+celeron&meta=)
can you please post a link about that?
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Cause SQL Server Restricted Mode (db_owner, dbcreator,)

What would cause SQL Server 2000 Enterprise Edition
databases to be in access restricted mode where only
db_owner, dbcreator, and sysadmin?
SQL Server was not placed in access restricted my any
administrator.
This is an active/active cluster with SQL Server 2000
SP3A with Windows 2000 Advanced Server.
Could the cluster place SQL Server 2000 in a access
restricted mode?
If SQL Server 2000 resources were very low for extended
period would the cluster or SQL Server place SQL Server
in access restricted mode?
Please help me resolve this issue.
Thank You,
Dan
The only thing that can do that is an administrator. If it is a new
database, it will look exactly like that since SQL Server uses the principle
of having to explicitly grant access. But, if it is an existing database,
an administrator had to have changed the security. You are saying that no
one did this. I'd more accurately say that no one is willing to admit to
having done that. How can you find out? Download Log Explorer from
Lumigent Technologies and point it at your tran log + tran log backups. It
will show you pretty explicitly when the security was changed and by whom.
I'd also recommend taking a look at their companion product Entegra which
can notify you immediately when security changes and a whole host of other
things change.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com