Changing the server name and IP address

A server with SQL 2000 installed is moving to a new physical location. Our
company standards mean that the name of the server and the IP address of the
server will change also. This SQL instance name is the same as the server
name. Will I have to uninstall and reinstall SQL Server (and recover the
databases) when this move is made so that the instance name equals the
server name? Is there a stored procedure that changes the instance name to
the server name? Thank you.[posted and mailed, please reply in news]

steven virnig (pezguy@.mn.rr.com) writes:
> A server with SQL 2000 installed is moving to a new physical location.
> Our company standards mean that the name of the server and the IP
> address of the server will change also. This SQL instance name is the
> same as the server name. Will I have to uninstall and reinstall SQL
> Server (and recover the databases) when this move is made so that the
> instance name equals the server name? Is there a stored procedure that
> changes the instance name to the server name? Thank you.

If memory serves, it's as simple as:

exec sp_dropserver @.@.servername
exec sp_addserver 'NewName', 'local'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

Changing SQL Server Management Studio "My Projects" path

Hi,

I want to change the default "My Projects" save location from "My Documents\SQL Server Management Studio\Projects" to somewhere else. I didn't see the My Projects path in the SQL Server Management Studio Options.

How can I change the path?

Thanks,

Mike

One way is to use Regedit, and search for 'SQL Server Management Studio\Projects'.

You will find the location for NewProjects, DefaultProjects, NewProjItems, DefaultProjItems -and a few other things you might like to know about.

As always, with editing the registry, use caution.

|||I have the same question/issue. I've looked for a registry entry like 'SQL Server Management Studio\Projects' but have not been able to locate anything that even comes close. Running under Windows Vista Enterprise. Can you give the full path to the registry key? TIA|||

On my computer, Windows Server 2003, I found it in two locations:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

AND

HKEY_USERS\{My SID}\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

The values to find and change are:

DefaultNewProjectLocation

MyDocumentsLocation

Both need to be 'Expandable String Value' data

Again, the RegEdit search was for: [SQL Server Management Studio\Projects]

(no square brackets)

|||For SQL Server Express the location within the registry is:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM

and the search item to use is:

"SQL Server Management Studio Express\Projects"

Changing SQL Server Management Studio "My Projects" path

Hi,

I want to change the default "My Projects" save location from "My Documents\SQL Server Management Studio\Projects" to somewhere else. I didn't see the My Projects path in the SQL Server Management Studio Options.

How can I change the path?

Thanks,

Mike

One way is to use Regedit, and search for 'SQL Server Management Studio\Projects'.

You will find the location for NewProjects, DefaultProjects, NewProjItems, DefaultProjItems -and a few other things you might like to know about.

As always, with editing the registry, use caution.

|||I have the same question/issue. I've looked for a registry entry like 'SQL Server Management Studio\Projects' but have not been able to locate anything that even comes close. Running under Windows Vista Enterprise. Can you give the full path to the registry key? TIA|||

On my computer, Windows Server 2003, I found it in two locations:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

AND

HKEY_USERS\{My SID}\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

The values to find and change are:

DefaultNewProjectLocation

MyDocumentsLocation

Both need to be 'Expandable String Value' data

Again, the RegEdit search was for: [SQL Server Management Studio\Projects]

(no square brackets)

Changing Server Name And IP address

We are going to be moving our company to a new location. Changing server
names and Ip address of the servers. I am trying to find out and research
possible impact to the SQL databases we have. Any information on this links
etc would be a great help. If there are any impacts at all. This will be
servers only.Renaming a Server
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
AMB
"RussN" wrote:

> We are going to be moving our company to a new location. Changing server
> names and Ip address of the servers. I am trying to find out and research
> possible impact to the SQL databases we have. Any information on this lin
ks
> etc would be a great help. If there are any impacts at all. This will be
> servers only.|||... which unfortunately doesn't mention jobs, so also look at
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...[vbcol=seagreen]
> Renaming a Server
> http://msdn.microsoft.com/library/d...nstall_5r8f.asp
>
> AMB
> "RussN" wrote:
>|||And then there is the whole cluster issue. If you have a cluster to move,
hire an expert. He will tell you to rebuild it from scratch in the new
location.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
> ... which unfortunately doesn't mention jobs, so also look at
> http://www.karaszi.com/SQLServer/in...server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>|||Good catch, Geoff. I've updated my article with a note on this. Let me know
if you want me to remove
the credits, or change it. :-)
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> And then there is the whole cluster issue. If you have a cluster to move,
hire an expert. He
> will tell you to rebuild it from scratch in the new location.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>|||Thanks for the credit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiBZBtQnFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Good catch, Geoff. I've updated my article with a note on this. Let me
> know if you want me to remove the credits, or change it. :-)
> http://www.karaszi.com/SQLServer/in...server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
>

Changing Server Name And IP address

We are going to be moving our company to a new location. Changing server
names and Ip address of the servers. I am trying to find out and research
possible impact to the SQL databases we have. Any information on this links
etc would be a great help. If there are any impacts at all. This will be
servers only.
Renaming a Server
http://msdn.microsoft.com/library/de...stall_5r8f.asp
AMB
"RussN" wrote:

> We are going to be moving our company to a new location. Changing server
> names and Ip address of the servers. I am trying to find out and research
> possible impact to the SQL databases we have. Any information on this links
> etc would be a great help. If there are any impacts at all. This will be
> servers only.
|||... which unfortunately doesn't mention jobs, so also look at
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...[vbcol=seagreen]
> Renaming a Server
> http://msdn.microsoft.com/library/de...stall_5r8f.asp
>
> AMB
> "RussN" wrote:
|||And then there is the whole cluster issue. If you have a cluster to move,
hire an expert. He will tell you to rebuild it from scratch in the new
location.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
> ... which unfortunately doesn't mention jobs, so also look at
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>
|||Good catch, Geoff. I've updated my article with a note on this. Let me know if you want me to remove
the credits, or change it. :-)
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> And then there is the whole cluster issue. If you have a cluster to move, hire an expert. He
> will tell you to rebuild it from scratch in the new location.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for the credit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiBZBtQnFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Good catch, Geoff. I've updated my article with a note on this. Let me
> know if you want me to remove the credits, or change it. :-)
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
>

Changing Server Name And IP address

We are going to be moving our company to a new location. Changing server
names and Ip address of the servers. I am trying to find out and research
possible impact to the SQL databases we have. Any information on this links
etc would be a great help. If there are any impacts at all. This will be
servers only.Renaming a Serve
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
AMB
"RussN" wrote:
> We are going to be moving our company to a new location. Changing server
> names and Ip address of the servers. I am trying to find out and research
> possible impact to the SQL databases we have. Any information on this links
> etc would be a great help. If there are any impacts at all. This will be
> servers only.|||... which unfortunately doesn't mention jobs, so also look at
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
> Renaming a Server
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>
> AMB
> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing server
>> names and Ip address of the servers. I am trying to find out and research
>> possible impact to the SQL databases we have. Any information on this links
>> etc would be a great help. If there are any impacts at all. This will be
>> servers only.|||And then there is the whole cluster issue. If you have a cluster to move,
hire an expert. He will tell you to rebuild it from scratch in the new
location. :)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
> ... which unfortunately doesn't mention jobs, so also look at
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing
>> server
>> names and Ip address of the servers. I am trying to find out and
>> research
>> possible impact to the SQL databases we have. Any information on this
>> links
>> etc would be a great help. If there are any impacts at all. This will
>> be
>> servers only.
>|||Good catch, Geoff. I've updated my article with a note on this. Let me know if you want me to remove
the credits, or change it. :-)
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> And then there is the whole cluster issue. If you have a cluster to move, hire an expert. He
> will tell you to rebuild it from scratch in the new location. :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>> ... which unfortunately doesn't mention jobs, so also look at
>> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
>> news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing server
>> names and Ip address of the servers. I am trying to find out and research
>> possible impact to the SQL databases we have. Any information on this links
>> etc would be a great help. If there are any impacts at all. This will be
>> servers only.
>|||Thanks for the credit.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiBZBtQnFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Good catch, Geoff. I've updated my article with a note on this. Let me
> know if you want me to remove the credits, or change it. :-)
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
>> And then there is the whole cluster issue. If you have a cluster to
>> move, hire an expert. He will tell you to rebuild it from scratch in the
>> new location. :)
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>> ... which unfortunately doesn't mention jobs, so also look at
>> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing
>> server
>> names and Ip address of the servers. I am trying to find out and
>> research
>> possible impact to the SQL databases we have. Any information on this
>> links
>> etc would be a great help. If there are any impacts at all. This
>> will be
>> servers only.
>>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.c..._parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used wa
s
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything el
se
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
Nancy
Yes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.co...parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.com/rd_sql_server_startup_parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:
> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change location
s
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only all
ow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?
Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>
|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>
|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment. :)
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:
> Same as in a non-clustered environment. :)
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
> >I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> > SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> > the
> > location of the data, now that the virtual server is functional I want to
> > change the location of the log files to a different resource of the
> > cluster
> > (different drive) but it is not letting me do so. Hoe do you change
> > locations
> > of your drives in a clustered environment?
> >
> > Any ideas?
> >
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing IP

I am using merge replication with SQL ver 7, on a NT4 box. I need to
move one of seven subscribing servers to a new location which means
changing the IP address. I want to do the move without taking down
replication if possible. My plan is to move the box and change to new
IP address. Then update the WINS entry for the new address, and have
replication resume.
Should this work? Let me know if anyone can think of potential
problems.
thanks in advance, Jim
It should work. Replication is sensitive to subscriber name changes, not ip
address changes. Just make sure that you did not hard code an ip address in
the alias you may have created for this server in client network utility.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim" <ndpace@.att.net> wrote in message
news:c834d859.0409291039.4499a3df@.posting.google.c om...
> I am using merge replication with SQL ver 7, on a NT4 box. I need to
> move one of seven subscribing servers to a new location which means
> changing the IP address. I want to do the move without taking down
> replication if possible. My plan is to move the box and change to new
> IP address. Then update the WINS entry for the new address, and have
> replication resume.
> Should this work? Let me know if anyone can think of potential
> problems.
> thanks in advance, Jim
|||Guys!
I have the similar problem, but the server name will be changed also.
I believe it should be enough to call a kind of system SP, which will
replace old box names to the new ones. Could you point me to the right
direction?
Best regards,
Serge.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uEUCatlpEHA.1816@.TK2MSFTNGP09.phx.gbl...
> It should work. Replication is sensitive to subscriber name changes, not
ip
> address changes. Just make sure that you did not hard code an ip address
in
> the alias you may have created for this server in client network utility.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim" <ndpace@.att.net> wrote in message
> news:c834d859.0409291039.4499a3df@.posting.google.c om...
>
|||Serge,
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
This will rename your server, but you'll need to set up
the publications from fresh afterwards.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

Changing default location for FTDATA folder

When I restore a database it will place the catalogs in the default location
"C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA", how can I change this
default location so that when I restore a new database, the full text
catalogs are stored elsewhere ?
Changing the location of the catalog is only an option in SQL 2005.
Basically the full text catalog is in a different file group so you have an
option of exactly where you want to park it.
For SQL 200 I suggest you follow the instructions in
http://support.microsoft.com/default...b;en-us;240867
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GMG" <nospam@.nospam.com> wrote in message
news:evgizv87FHA.3048@.TK2MSFTNGP10.phx.gbl...
> When I restore a database it will place the catalogs in the default
> location
> "C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA", how can I change
> this
> default location so that when I restore a new database, the full text
> catalogs are stored elsewhere ?
>
|||I am aware of this document and have found it very complicated and with a
lot of registry updates which makes me uneasy. I have finally opted for
dropping the catalog and then recreating it in a different location,
followed by full population.
Despite this when the documentation for sp_help_fulltext_catalogs states:
"NULL indicates the default directory determined during installation", my
question is how do I change this default directory ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:equvR897FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Changing the location of the catalog is only an option in SQL 2005.
> Basically the full text catalog is in a different file group so you have
an
> option of exactly where you want to park it.
> For SQL 200 I suggest you follow the instructions in
> http://support.microsoft.com/default...b;en-us;240867
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "GMG" <nospam@.nospam.com> wrote in message
> news:evgizv87FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
|||Try this key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\SQL2000\MSSQLServer
And set it using this entry FullTextDefaultPath
Note that my instance name is SQL2000, so it could be
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer for you
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GMG" <nospam@.nospam.com> wrote in message
news:O4vaSO%237FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I am aware of this document and have found it very complicated and with a
> lot of registry updates which makes me uneasy. I have finally opted for
> dropping the catalog and then recreating it in a different location,
> followed by full population.
> Despite this when the documentation for sp_help_fulltext_catalogs states:
> "NULL indicates the default directory determined during installation", my
> question is how do I change this default directory ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:equvR897FHA.3224@.TK2MSFTNGP09.phx.gbl...
> an
>

Changing Default Backup Location for SQL Server 2003

Does anybody know how to change the default backup location for SQL Server
2003 without re-installing it? My backup location is defaulting to the C:
drive & I want to change this to the E: drive?
Many thanks,
RitaHi
SQL Server 2003 does not exist. SQL 2000?
In the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
BackupDirectory key
If it is a named instance, adjust the path.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rita" <Rita@.discussions.microsoft.com> wrote in message
news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
> Does anybody know how to change the default backup location for SQL Server
> 2003 without re-installing it? My backup location is defaulting to the C:
> drive & I want to change this to the E: drive?
> Many thanks,
> Rita|||Many thanks Mike - and yes, it is SQL Server 2000!
Regards,
Rita
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server 2003 does not exist. SQL 2000?
> In the registry:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
> BackupDirectory key
> If it is a named instance, adjust the path.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rita" <Rita@.discussions.microsoft.com> wrote in message
> news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
>
>

Changing Default Backup Location for SQL Server 2003

Does anybody know how to change the default backup location for SQL Server
2003 without re-installing it? My backup location is defaulting to the C:
drive & I want to change this to the E: drive?
Many thanks,
Rita
Hi
SQL Server 2003 does not exist. SQL 2000?
In the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
BackupDirectory key
If it is a named instance, adjust the path.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rita" <Rita@.discussions.microsoft.com> wrote in message
news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
> Does anybody know how to change the default backup location for SQL Server
> 2003 without re-installing it? My backup location is defaulting to the C:
> drive & I want to change this to the E: drive?
> Many thanks,
> Rita
|||Many thanks Mike - and yes, it is SQL Server 2000!
Regards,
Rita
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server 2003 does not exist. SQL 2000?
> In the registry:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
> BackupDirectory key
> If it is a named instance, adjust the path.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rita" <Rita@.discussions.microsoft.com> wrote in message
> news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
>
>

Changing Default Backup Location for SQL Server 2003

Does anybody know how to change the default backup location for SQL Server
2003 without re-installing it? My backup location is defaulting to the C:
drive & I want to change this to the E: drive?
Many thanks,
RitaHi
SQL Server 2003 does not exist. SQL 2000?
In the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
BackupDirectory key
If it is a named instance, adjust the path.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rita" <Rita@.discussions.microsoft.com> wrote in message
news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
> Does anybody know how to change the default backup location for SQL Server
> 2003 without re-installing it? My backup location is defaulting to the C:
> drive & I want to change this to the E: drive?
> Many thanks,
> Rita|||Many thanks Mike - and yes, it is SQL Server 2000!
Regards,
Rita
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2003 does not exist. SQL 2000?
> In the registry:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> BackupDirectory key
> If it is a named instance, adjust the path.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rita" <Rita@.discussions.microsoft.com> wrote in message
> news:B9E59639-209E-4D6B-86AA-239D623D4BB2@.microsoft.com...
> > Does anybody know how to change the default backup location for SQL Server
> > 2003 without re-installing it? My backup location is defaulting to the C:
> > drive & I want to change this to the E: drive?
> >
> > Many thanks,
> >
> > Rita
>
>