Removing a server: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Renaming Computer Running SQL or MSDE

A number of questions have been posted in the past regarding the
problems encountered when renaming a computer that is running SQL or
MSDE. I too have had these problems but recent experience prompts me
to suspect that it is permissible to rename a computer that is running
XP Pro with MSDE2000 without any such problems.
Does anyone know of, or can anyone direct me to official Microsoft
information which details the restrictions on renaming a host computer
that is running SQL2000 in its various forms (Enterprise, Standard,
Personal Ed, MSDE)
Thanks
In my experience (and those of my customers) when you rename a computer and
reboot, SQL Server will reconfigure itself to the new machine name just
fine. Of course any scripts, DTS packages, or any other processes and
procedures that reference the old server name will need to be brought up to
date.
Jim
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404122023.225e5433@.posting.google.c om...
> A number of questions have been posted in the past regarding the
> problems encountered when renaming a computer that is running SQL or
> MSDE. I too have had these problems but recent experience prompts me
> to suspect that it is permissible to rename a computer that is running
> XP Pro with MSDE2000 without any such problems.
> Does anyone know of, or can anyone direct me to official Microsoft
> information which details the restrictions on renaming a host computer
> that is running SQL2000 in its various forms (Enterprise, Standard,
> Personal Ed, MSDE)
> Thanks
|||Thanks for the response Jim, but earlier versions of MSDE and SQL
often fail to work if the computer is renamed although that does not
seem to be the case recently and I have heard a rumor the MSDE2003
will not have this problem. I would like to know the offical Microsoft
position on the, but it is hard to come by.
Thanks again.
"J Young" <thorium48@.hotmail.com> wrote in message news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> In my experience (and those of my customers) when you rename a computer and
> reboot, SQL Server will reconfigure itself to the new machine name just
> fine. Of course any scripts, DTS packages, or any other processes and
> procedures that reference the old server name will need to be brought up to
> date.
> Jim
> "David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
> news:78d72c83.0404122023.225e5433@.posting.google.c om...
|||I've only seen problems with SQL Server 7 (ie and MSDE v1).
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404132223.739b363e@.posting.google.c om...
> Thanks for the response Jim, but earlier versions of MSDE and SQL
> often fail to work if the computer is renamed although that does not
> seem to be the case recently and I have heard a rumor the MSDE2003
> will not have this problem. I would like to know the offical Microsoft
> position on the, but it is hard to come by.
> Thanks again.
> "J Young" <thorium48@.hotmail.com> wrote in message
news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
|||Greg is correct. This problem is only present in SQL 7.0 and MSDE 1. In
these version of SQL Server there is no concept of an instance name and the
server name that you would address is always the name of the machine (except
in the case of a SQL7 cluster, which is another topic altogether).
Jim
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404132223.739b363e@.posting.google.c om...
> Thanks for the response Jim, but earlier versions of MSDE and SQL
> often fail to work if the computer is renamed although that does not
> seem to be the case recently and I have heard a rumor the MSDE2003
> will not have this problem. I would like to know the offical Microsoft
> position on the, but it is hard to come by.
> Thanks again.
> "J Young" <thorium48@.hotmail.com> wrote in message
news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]

Renaming cluster

I am looking for some help with a SQL Upgrade that we are doing. We have an
older server running SQL 2000. We also have a MSCS cluster that we've built
which is running SQL 2005. We want to get all the databases on the SQL 2005
cluster and retire the SQL 2000 server.
Is there any way to rename the cluster? It seems to me that the easiest way
would be to restore the databases from 2000 to 2005, take down the 2000
machine, rename the 2005 cluster to have the same name as the old SQL 2000
server and we're done.
The other option is to bring down the 2000 server, but keep the 2005 machine
with the new name - problem is there's a lot of old code that has the
connection strings hardcoded. It was suggested that we could do a DNS
pointer to resolve the old server name to the new name, but that didn't seem
to work with all of the apps that we've tested...
Any suggestions? I haven't been able to find much advice on how to do a
2000 to 2005 upgrade when you're also switching servers. Thanks for your
time.
Why bother renaming? Create a DNS SRV record repointing the old name to the
new server/instance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Morgan" <Morgan@.discussions.microsoft.com> wrote in message
news:66673D99-7035-491D-87EA-A10DE2A89FEB@.microsoft.com...
>I am looking for some help with a SQL Upgrade that we are doing. We have
>an
> older server running SQL 2000. We also have a MSCS cluster that we've
> built
> which is running SQL 2005. We want to get all the databases on the SQL
> 2005
> cluster and retire the SQL 2000 server.
> Is there any way to rename the cluster? It seems to me that the easiest
> way
> would be to restore the databases from 2000 to 2005, take down the 2000
> machine, rename the 2005 cluster to have the same name as the old SQL 2000
> server and we're done.
> The other option is to bring down the 2000 server, but keep the 2005
> machine
> with the new name - problem is there's a lot of old code that has the
> connection strings hardcoded. It was suggested that we could do a DNS
> pointer to resolve the old server name to the new name, but that didn't
> seem
> to work with all of the apps that we've tested...
> Any suggestions? I haven't been able to find much advice on how to do a
> 2000 to 2005 upgrade when you're also switching servers. Thanks for your
> time.
|||That was the original plan. We did this in a test environment - old java
code we have that uses JDBC wasn't able to resolve the server name. Also, a
..net service that we tested didn't resolve the name correctly, either, which
is why I thought it wasn't going to work out. Perhaps there was a separate
isue with that service when we tested it. As far as you know, the DNS
solution should work pretty well?
Are there any "got'chas" or other downsides I should keep in mind?
Thanks for your time.
"Geoff N. Hiten" wrote:

> Why bother renaming? Create a DNS SRV record repointing the old name to the
> new server/instance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Morgan" <Morgan@.discussions.microsoft.com> wrote in message
> news:66673D99-7035-491D-87EA-A10DE2A89FEB@.microsoft.com...
>

Renaming a server

Hi all,

I have a Windows 2003 server box running SQL 2000 and BizTalk 2006. I need to change the name of the underlying server, could anyone point me in the right direction of what I would need to change in terms of SQL and BizTalk config to make it all sit happily with this?

Kind regards
Taz

Check out http://msdn2.microsoft.com/en-us/library/ms143799.aspx

Thanks,

Peter Saddow

Wednesday, March 28, 2012

Renaming a Server

Hello

I am wondering if this is able to be done and what implications there would
be with SQL Server.

Server1 name: Production Currently running Windows NT and SQL 7
instance name is Production
Server2 name: Test Currently running Windows 2000 and
SQL 2000, instance name is Test - default installation

Bring the Server1 offline and rename Server 2 with the name of Production.
Do we have to change the instance name in SQL now on Server 2? If so can we
do this? I didn't think you could change a instance name and had to
reinstall SQL.

Another Question:

Can you have 2 instances of SQL with the same name but reside on different
servers?

Thanks
SherHi

[cut]
> Another Question:
> Can you have 2 instances of SQL with the same name but reside on different
> servers?
Yes, you can have instance1 on server1 and server2 - you always connect to
any of them specifying full name: server1/instance1 or server2/instance1.

Tomik|||snewell2003 via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am wondering if this is able to be done and what implications there
> would be with SQL Server.
> Server1 name: Production Currently running Windows NT and SQL 7
> instance name is Production
> Server2 name: Test Currently running Windows 2000
> and SQL 2000, instance name is Test - default installation

Is that Test\Test or just Test? Production is obviously not
Production\Prodction as there are no named instances on SQL 7.

> Bring the Server1 offline and rename Server 2 with the name of
> Production. Do we have to change the instance name in SQL now on Server
> 2? If so can we do this? I didn't think you could change a instance
> name and had to reinstall SQL.

If SQL 2000 is a default instance, this should not be difficult. There
is one thing you need to once you have moved:

EXEC sp_dropserver Test
go
EXEC sp_addserver, Production, LOCAL

And the reboot afterthis. Else @.@.servername will be wrong.

> Can you have 2 instances of SQL with the same name but reside on different
> servers?

As long as the machines are not connected to each other. if they are on
the same network, I would not recommend it.

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

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

Monday, March 26, 2012

Rename SQL Default instance in cluster

HI GROUP
I have my production sql server computer name "sql2k"
and of course the default sql instant name as "sql2k"
because they're running on same box aand stand alone
server.
I do have another active\active sql cluster in place
node1 name is sql1 node 2 name sql2 and virtual computer
name sqlcluster. My default sql instance name
is "sqlserver1"
and it running on Node1 with failed over using Node 2
My sql second instance name is "sql/server2" and it
running on Node 2 with failed over using Node1.
I would like to rename my "sqlserver1" sql instance name to
my production sql name is "sql2k, my plan is to uninstall
the sql default instant on node 1 and reinstall sql with
the my sql production name after shutdown my prodution sql
server. my question is do I need to rename the computer
virtual server name too or just sql instance name?
Do any one have any idea what will cause any problem to
rename sql instance in cluster and of course the cluster
will have diff ip address with sql production server
only the name will be transfer to my default cluster on
node 1. Please give me any opinion if you have with this
Thanks a lot
You can't rename a SQL Server instance in a cluster. The only way to
accomplish this is to uninstall SQL Server and reinstall it. There aren't
any issues to doing this that I'm aware of. Just keep the name under 13
chanracters.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thanks
But I would like to transfer production sql name to the
default sql cluster name is it possible?
I know I can not have 2 sql name in the same network
but what if I shutdown my production and during the
reinstall sql on defaukt cluster Can I use my production
sql name for it? I still not clear if sql name using any
dns or wins like computer name on network if that the case
then where do I need to look for do a clean up of that name
before using it for my cluster?
Thanks a lot

>--Original Message--
>You can't rename a SQL Server instance in a cluster. The
only way to
>accomplish this is to uninstall SQL Server and reinstall
it. There aren't
>any issues to doing this that I'm aware of. Just keep
the name under 13
>chanracters.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||Yes, you can reuse that name as long as the original server is offline. The
names that you use for the machine, cluster, and SQL Server are DNS
resolvable.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Rename SQL 2005 Instance

Hi,

I have a SQL Server 2005 installation, running under the instance of MyServer\SQL2005. So I have 1 instance called 'SQL 2005'.

I would like to rename the instance back to the default instance. So afterwards, the server instance should just be called 'MyServer'. Does anyone know if this is possible without loosing any data?

Thank you

Jeremy

I don't think it's doable

I searched around when I tried to change my default instance install to named instance - can't do it

had to uninstall, and re-install again

as long as you don't delete your MDF/LDF files, you shouldn't lose much (backup first anyway)

|||

Thanks for your reply,

This is what I was fearing.....although it is strange that Microsoft specificly ship the AS instance rename tool. Yet there's no indication of how you can do this for the database engine.

If anyone does know of a method it would be appreciated,

Thanks

Jeremy

|||

Jeremy,

You can rename a default instance of the database engine just by renaming the computer. See the topic "How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005" in BOL. You cannot rename the instance part of a named instance.

Ron

|||http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/544c4eaf43ddfaf3/e9065e05718e984e

Rename server running Reporting Services

Has anyone successfuly renamed a SSRS 2005 server? I tried this:
http://technet.microsoft.com/en-us/library/ms345235.aspx
to no avail. The SSRS service is running, I just can't connect to it using
MSIE (http://localhost/reports or http://localhost/reportserver) or
Management Studio. ISS is up and running. I'm using MSSQL 2005 Enterprise
SP1 on Windows Server 2003 Standard.
Thank you,
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comCheck the Reporting services Configuration Manager whether it is connected to
your database server using
All Programs\SQL Server 2005\Configuration Tools\Reporting services
Configuration
And in Internet Information Services Manager check whether the
server/websites containing reportserver is started using
start -> run -> inetmgr
Also try using http://your servername/reportserver
--
Regards,
ArvindRavish
"Alain Quesnel" wrote:
> Has anyone successfuly renamed a SSRS 2005 server? I tried this:
> http://technet.microsoft.com/en-us/library/ms345235.aspx
> to no avail. The SSRS service is running, I just can't connect to it using
> MSIE (http://localhost/reports or http://localhost/reportserver) or
> Management Studio. ISS is up and running. I'm using MSSQL 2005 Enterprise
> SP1 on Windows Server 2003 Standard.
> Thank you,
> --
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
>|||I had already tried all those things. Couldn't connect. I also tried
rebooting the server after making the recommended modifications. That
doesn't work either. Funny thing is, I have all green checkmarks in
Reporting Services Configuration Manager. Also note that the computer rename
occurs after a Ghost multicast and a sysprep (so that we can have different
names for all servers).
Thank you,
--
Alain Quesnel
alainsanspam@.logiquel.com
www.logiquel.com
"ArvindRavish" <ArvindRavish@.discussions.microsoft.com> wrote in message
news:ECA81F96-D43B-4E36-B8B7-2A10C473F395@.microsoft.com...
> Check the Reporting services Configuration Manager whether it is connected
> to
> your database server using
> All Programs\SQL Server 2005\Configuration Tools\Reporting services
> Configuration
> And in Internet Information Services Manager check whether the
> server/websites containing reportserver is started using
> start -> run -> inetmgr
> Also try using http://your servername/reportserver
> --
> Regards,
> ArvindRavish
>
> "Alain Quesnel" wrote:
>> Has anyone successfuly renamed a SSRS 2005 server? I tried this:
>> http://technet.microsoft.com/en-us/library/ms345235.aspx
>> to no avail. The SSRS service is running, I just can't connect to it
>> using
>> MSIE (http://localhost/reports or http://localhost/reportserver) or
>> Management Studio. ISS is up and running. I'm using MSSQL 2005 Enterprise
>> SP1 on Windows Server 2003 Standard.
>> Thank you,
>> --
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>

Rename Server

Dan|||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/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx
.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to
> change the server name and IP address. Will there be any problem of acces
sing the existing
> database? Thanks.
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> 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/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

Dan
|||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/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>
|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> 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/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to change the server name and IP address. Will there be
any problem of accessing the existing database? Thanks.Diane,
You'll need to update the connection information to the SQL Server in your
code as well to reflect the name and IP change i.e., connection strings,
DSNs, etc...
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks very much for your prompt response. Exactly what and where do I need
to change in SQL code? I know how to change the DNS entry. Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Diane,
> You'll need to update the connection information to the SQL Server in your
> code as well to reflect the name and IP change i.e., connection strings,
> DSNs, etc...
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||DSNs, ADO/ADO.NET connection strings and any other connection sources for
you applications. Touch base with your developers prior to making this
change. Also, if possible test the applications in a testing environment
first before making the changes in the production environment. Consider
having a roll-back strategy in place "just in case" something doesn't go as
expected.
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks very much for your prompt response. Exactly what and where do I
> need to change in SQL code? I know how to change the DNS entry. Thanks.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
After you rename the computer, open up Query Analyzer so that you can reset
the SQL Server's reference. It won't know who it is anymore. ;-)
--
USE master
GO
-- Drop the old computer name
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
BEGIN
SET NOCOUNT ON
exec sp_dropserver @.server = 'OldName'
END
-- Add the new computer name
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname ='NewName')
BEGIN
SET NOCOUNT ON
exec sp_addserver @.server = 'NewName', @.local = 'local'
END
Client applications that currently point to the old computer name/IP address
will need to be modified to point at the new computer name/ip address.
Rick Sawtell
MCT, MCSD, MCDBA|||http://support.microsoft.com/kb/303774/
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OaW%23LGtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> DSNs, ADO/ADO.NET connection strings and any other connection sources for
> you applications. Touch base with your developers prior to making this
> change. Also, if possible test the applications in a testing environment
> first before making the changes in the production environment. Consider
> having a roll-back strategy in place "just in case" something doesn't go
> as expected.
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
>> Thanks very much for your prompt response. Exactly what and where do I
>> need to change in SQL code? I know how to change the DNS entry. Thanks.
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>>
>|||Thanks very much.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Thanks very much.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:O32s4MtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> http://support.microsoft.com/kb/303774/
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
And to add to the other replies, if you have replication enabled on this
server or remote logins then you will find that you cannot use sp_dropserver
until you disable replication.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
I had to go through this myself 2 days ago :)
Dan|||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/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>|||Thank you very much for your input. I appreciate it very much.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OLUnfy1vFHA.2880@.TK2MSFTNGP12.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
> And to add to the other replies, if you have replication enabled on this
> server or remote logins then you will find that you cannot use
> sp_dropserver until you disable replication.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
> I had to go through this myself 2 days ago :)
> Dan
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> 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/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||Hi Rick,
Your instructions work. I appreciated that you took the time to write me
the detail instructions. I have a question and I don't know if it is
related to renaming the server or not. I got this error message when I
typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'SMART_DATA'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Do you have any suggestions? Thanks.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Are you in the right database when running the command? Can you check if the object exists in
sysobjects? Also, who is the owner of the object?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me the detail instructions.
> I have a question and I don't know if it is related to renaming the server or not. I got this
> error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
> Do you have any suggestions? Thanks.
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||Thanks for your prompt response, Tibor.
Yes, I am in the right dabase when running the command. From Query
Analyzer, I selected Query, Change Database and point to Smart_Data dabase
and ran the command.
I see the object exists in sysobjects. I went to Tools, Object Search. I
saw Smart_Data under db_name for sysobjects. The owner of the object is
dbo.
Tibor, I think I made a BIG mistake by not following Rick's instructions.
Rick's instructions said to "USE master". But, I changed "master" to my
database name "smart_data". So, for every master, I changed to smart_data.
Maybe that was the reason that I got the error message when I typed the
command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's
instructions by using the master table? Do you have any other suggestions?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
> Are you in the right database when running the command? Can you check if
> the object exists in sysobjects? Also, who is the owner of the object?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||You don't really have to be in master when executing sp_Dropserver and sp_addserver, so that should
not be the cause of your problem. I've never seen a case where SHOWCONTIG doesn't work if the table
exists and there isn't an object owner problem. I'm out of ideas, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks for your prompt response, Tibor.
> Yes, I am in the right dabase when running the command. From Query Analyzer, I selected Query,
> Change Database and point to Smart_Data dabase and ran the command.
> I see the object exists in sysobjects. I went to Tools, Object Search. I saw Smart_Data under
> db_name for sysobjects. The owner of the object is dbo.
> Tibor, I think I made a BIG mistake by not following Rick's instructions. Rick's instructions said
> to "USE master". But, I changed "master" to my database name "smart_data". So, for every master,
> I changed to smart_data. Maybe that was the reason that I got the error message when I typed the
> command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's instructions by using the master
> table? Do you have any other suggestions? Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if the object exists in
>> sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me the detail
>> instructions. I have a question and I don't know if it is related to renaming the server or not.
>> I got this error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me
> the detail instructions. I have a question and I don't know if it is
> related to renaming the server or not. I got this error message when I
> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Do you have any suggestions? Thanks.
In your other replies to this you have Smart_Data as the table name. Have
you tried
DBCC SHOWCONTIG (Smart_Data)
It's possible that you have your codepage set to a case-sensitive one, in
which case you must use the correct case for any object name.
Dan|||Thanks for your response, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23USAzov0FHA.904@.tk2msftngp13.phx.gbl...
> You don't really have to be in master when executing sp_Dropserver and
> sp_addserver, so that should not be the cause of your problem. I've never
> seen a case where SHOWCONTIG doesn't work if the table exists and there
> isn't an object owner problem. I'm out of ideas, I'm afraid...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks for your prompt response, Tibor.
>> Yes, I am in the right dabase when running the command. From Query
>> Analyzer, I selected Query, Change Database and point to Smart_Data
>> dabase and ran the command.
>> I see the object exists in sysobjects. I went to Tools, Object Search.
>> I saw Smart_Data under db_name for sysobjects. The owner of the object
>> is dbo.
>> Tibor, I think I made a BIG mistake by not following Rick's instructions.
>> Rick's instructions said to "USE master". But, I changed "master" to my
>> database name "smart_data". So, for every master, I changed to
>> smart_data. Maybe that was the reason that I got the error message when I
>> typed the command DBCC SHOWCONTIG. Do you know if I still can rerun
>> Rick's instructions by using the master table? Do you have any other
>> suggestions? Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if
>> the object exists in sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database?
>> Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore.
>> ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>|||Thanks for your suggestions, Dan.
I have tried Smart_Data, SMART_DATA, smart_data. The database name under
Enterprise Manager is SMART_DATA. Do you have any other suggestions?
Thanks.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
> In your other replies to this you have Smart_Data as the table name. Have
> you tried
> DBCC SHOWCONTIG (Smart_Data)
> It's possible that you have your codepage set to a case-sensitive one, in
> which case you must use the correct case for any object name.
> Dan
>|||> The database name under
> Enterprise Manager is SMART_DATA.
Database name? Earlier you said that the name of the table is SMART_DATA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
> Thanks for your suggestions, Dan.
> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
> Thanks.
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name. Have
>> you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one, in
>> which case you must use the correct case for any object name.
>> Dan
>|||Tibor,
You answered my question!!! It was my mistake. I used the database name
instead of the table name. By using the table name with DBCC SHOWCONTIG, I
now have statistics.
Thanks very much for asking the questions. I sincerely apologize for my
errors.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
> Database name? Earlier you said that the name of the table is SMART_DATA.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>|||> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
No need to apologize. I'm glad you sorted it out. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:ueCbj2z0FHA.3524@.tk2msftngp13.phx.gbl...
> Tibor,
> You answered my question!!! It was my mistake. I used the database name
> instead of the table name. By using the table name with DBCC SHOWCONTIG, I
> now have statistics.
> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
>> Database name? Earlier you said that the name of the table is SMART_DATA.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>
>

Rename machine with SQL server

I'm trying to write a script to run after changing the computer name of a machine running SQL Server 2005. I know about the article on the SQL Developer center that talks about sp_dropserver and sp_addserver. My question is about the Windows groups that are created with the machine name embedded inside (e.g. MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER).

Do I need to worry about these? If the answer is no because the name of the group is irrelevant, how do I go about changing the machine name for the login (i.e. the MACHINENAME in front of the slash)? I don't want to hardcode these group names into my script.

Thanks for any help.

There is no need to do anything with those local groups. They're created when you install/setup sqlserver. When you change the computer name, you would need to update sqlserver so that its servername matches up with the computername. See the following article for more detail.

http://msdn2.microsoft.com/en-us/library/ms143799(SQL.90).aspx

Friday, March 23, 2012

rename logical file

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> > rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> > affect anything on the server?
> >
> >
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files
> > named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > to
> > rename it as CC_Data and CC_Log to make it consistent. Would this
> > renaming
> > affect anything on the server?
> >
> >
>|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:
> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
> > No. It goes quickly.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> > Hi,
> >
> > Would it affect anything / any process on the production server?
> >
> > "Tom Moreau" wrote:
> >
> > > You can change the logical file names, using ALTER DATABASE, without
> > > taking
> > > the DB's offline.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > > Hi,
> > >
> > > I have 3 databases running on Production server. All the databases have
> > > names like AA, BB and CC with their Logical Data Files and Log files
> > > named
> > > as
> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > > to
> > > rename it as CC_Data and CC_Log to make it consistent. Would this
> > > renaming
> > > affect anything on the server?
> > >
> > >
> >
> >
>|||> Does changing the logical name also change the physical (file) name?
No.
> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>> The logical filename is just an easy way to refer to it when using ALTER
>> DATABASE. They are unique to the DB - not the server - so you can have a
>> number of DB's with the same logical filenames, but with different physical
>> filenames. This is convenient is a scenario where you have one DB per
>> client and what all of your DB maintenance scripts to do the same thing to
>> each customer DB.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
>> Hi Tom,
>> Can you please explain to me what exactly is the purpose of a logical
>> Filename? When we have more than one databases on one SQL Server, can we
>> have
>> the same logical name for all the databases? Thanks in advance.
>> "Tom Moreau" wrote:
>> > No. It goes quickly.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> >
>> >
>> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
>> > Hi,
>> >
>> > Would it affect anything / any process on the production server?
>> >
>> > "Tom Moreau" wrote:
>> >
>> > > You can change the logical file names, using ALTER DATABASE, without
>> > > taking
>> > > the DB's offline.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
>> > > Hi,
>> > >
>> > > I have 3 databases running on Production server. All the databases have
>> > > names like AA, BB and CC with their Logical Data Files and Log files
>> > > named
>> > > as
>> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
>> > > to
>> > > rename it as CC_Data and CC_Log to make it consistent. Would this
>> > > renaming
>> > > affect anything on the server?
>> > >
>> > >
>> >
>> >
>>|||Thank you.
"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||I normally drop the db, then copy it to the new location - data in the
data directory, logs to their directory. Rename the files I copied my
test.mdf and test.ldf to test2.mdf and test2.ldf
Then in SMS do a attach database and browse to the mdf file. Select the
new one. If necessary do the same for the log (you'll have to do this
if you moved it).
* In the top window check the "Attach AS" and change it to whatever you
want - for me test2
* In the bottom window under "Current File Path" browse to the new file
and select the one you renamed.- do for mdf and ldf files. For mdf I
selected test2.mdf and for ldf test2.ldf
* In top window change owner if necessary.
* Click OK
Now go to the database in object explorer and right click->properties.
Check the files window. You'll see the new files selected and if you
want you can change the logical names.
Yabe wrote:
> "Tibor Karaszi" wrote:
>> Does changing the logical name also change the physical (file) name?
>> No.
>>
>> If not,
>> is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>|||Probably just the SSMS GUI that is messing with you. I suggest you read about sp_attach_db in Books
Online. Since the mdf has the path and name of the original ldf (and other db files), you need to
specify all file names, like:
EXEC sp_attach_db 'NewDbName', 'C:\NewPath\NewFileName.mdf', 'C:\NewPath\NewFileName.ldf'
Above from memory, so please check Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:23B5D429-5E6A-4209-BCC2-CB9C20C0C94B@.microsoft.com...
>
> "Tibor Karaszi" wrote:
>> > Does changing the logical name also change the physical (file) name?
>> No.
>>
>> > If not,
>> > is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to
>> change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this
>> very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

Friday, March 30, 2012

Renaming Computer Running SQL or MSDE

Renaming cluster

Renaming a server

Wednesday, March 28, 2012

Renaming a Server

Monday, March 26, 2012

Rename SQL Default instance in cluster

Rename SQL 2005 Instance

Rename server running Reporting Services

Rename Server

Rename Server

Rename Server

Rename machine with SQL server

Friday, March 23, 2012

rename logical file

rename logical file

Removing a server

Blog Archive

About Me