Removing a server: mysql

Showing posts with label mysql. Show all posts

Friday, March 30, 2012

renaming drive letter

I want to add a new drive to replace an existing drive used by SQL
server(cluster installation).
My plan is to
1. stop sql related services.
2. copy sql db files to new drive...
3. Rename new drive with existing drive letter.
4. start sql.
My question is - will the change of drive letter work normally for cluster
installations or do i need to take any special care?If the drive letter is the same, the original files and directories are put
back, and the account running SQL Server has permissions to the files then
SQL won't detect any difference and be fine. However,
the cluster service may be a different story.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DD1F651A-F5D2-49B0-A220-3BD6343E815D@.microsoft.com...
>I want to add a new drive to replace an existing drive used by SQL
> server(cluster installation).
> My plan is to
> 1. stop sql related services.
> 2. copy sql db files to new drive...
> 3. Rename new drive with existing drive letter.
> 4. start sql.
> My question is - will the change of drive letter work normally for cluster
> installations or do i need to take any special care?|||Hi
Changing a drive letter in a cluster becomes a lot more complicated as the
cluster services depend on it.
You have to between steps 2 and 3, remove the dependency in cluster manager,
then re-name the letter, establish the dependencies in cluster manager and
then start SQL Server.
It is best to take SQL Server and Agent offline, and then reboot each node
to make sure the groups and resources fail over correctly. Then bring SQL
Server online again.
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/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DD1F651A-F5D2-49B0-A220-3BD6343E815D@.microsoft.com...
>I want to add a new drive to replace an existing drive used by SQL
> server(cluster installation).
> My plan is to
> 1. stop sql related services.
> 2. copy sql db files to new drive...
> 3. Rename new drive with existing drive letter.
> 4. start sql.
> My question is - will the change of drive letter work normally for cluster
> installations or do i need to take any special care?

renaming drive letter

I want to add a new drive to replace an existing drive used by SQL
server(cluster installation).
My plan is to
1. stop sql related services.
2. copy sql db files to new drive...
3. Rename new drive with existing drive letter.
4. start sql.
My question is - will the change of drive letter work normally for cluster
installations or do i need to take any special care?
If the drive letter is the same, the original files and directories are put
back, and the account running SQL Server has permissions to the files then
SQL won't detect any difference and be fine. However,
the cluster service may be a different story.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DD1F651A-F5D2-49B0-A220-3BD6343E815D@.microsoft.com...
>I want to add a new drive to replace an existing drive used by SQL
> server(cluster installation).
> My plan is to
> 1. stop sql related services.
> 2. copy sql db files to new drive...
> 3. Rename new drive with existing drive letter.
> 4. start sql.
> My question is - will the change of drive letter work normally for cluster
> installations or do i need to take any special care?
|||Hi
Changing a drive letter in a cluster becomes a lot more complicated as the
cluster services depend on it.
You have to between steps 2 and 3, remove the dependency in cluster manager,
then re-name the letter, establish the dependencies in cluster manager and
then start SQL Server.
It is best to take SQL Server and Agent offline, and then reboot each node
to make sure the groups and resources fail over correctly. Then bring SQL
Server online again.
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/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DD1F651A-F5D2-49B0-A220-3BD6343E815D@.microsoft.com...
>I want to add a new drive to replace an existing drive used by SQL
> server(cluster installation).
> My plan is to
> 1. stop sql related services.
> 2. copy sql db files to new drive...
> 3. Rename new drive with existing drive letter.
> 4. start sql.
> My question is - will the change of drive letter work normally for cluster
> installations or do i need to take any special care?
sql

renaming drive letter

renaming domain

We have a small site where we want to rename the domain and bring it into
our domain.
current names are as
domainx.local -holds SQL server cluster
domainx.net -main domain
we want to bring domainx.local into the domainx.net AD.
will will rename and migrate. Are there any tools for SQL2005?
we were planning on using the microsoft domain rename tool and ADMT3.
Any other issues with SQL?
Regards
CC
SQL itself doesn't really care what domain it is in, as long as all of the
resources and servers it has to access are still accessible the way they
were before. The only thing you will have to update is the windows service
accounts if any of them are from AD and not the local machine. Will it be
renamed or are you moving to a new domain while the old domain remains up
for a short period of time? If the latter I would recommend setting up a
trust and switching the accounts, or understanding that you might have blips
in uptime/availability.
"Ned" <Cal@.obded.org> wrote in message
news:%231UNGh2ZIHA.2000@.TK2MSFTNGP05.phx.gbl...
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
|||Hi
I assume that the machine names for the SQL Servers are not changing?
If you have granted logins to domain accounts or groups then you may need to
change them see http://support.microsoft.com/kb/298897/
John
"Ned" wrote:

> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
>

renaming domain

We have a small site where we want to rename the domain and bring it into
our domain.
current names are as
domainx.local -holds SQL server cluster
domainx.net -main domain
we want to bring domainx.local into the domainx.net AD.
will will rename and migrate. Are there any tools for SQL2005?
we were planning on using the microsoft domain rename tool and ADMT3.
Any other issues with SQL?
Regards
CCSQL itself doesn't really care what domain it is in, as long as all of the
resources and servers it has to access are still accessible the way they
were before. The only thing you will have to update is the windows service
accounts if any of them are from AD and not the local machine. Will it be
renamed or are you moving to a new domain while the old domain remains up
for a short period of time? If the latter I would recommend setting up a
trust and switching the accounts, or understanding that you might have blips
in uptime/availability.
"Ned" <Cal@.obded.org> wrote in message
news:%231UNGh2ZIHA.2000@.TK2MSFTNGP05.phx.gbl...
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>|||Hi
I assume that the machine names for the SQL Servers are not changing?
If you have granted logins to domain accounts or groups then you may need to
change them see http://support.microsoft.com/kb/298897/
John
"Ned" wrote:
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
>

Renaming Destination in DTS

I searched, but couldn't find anything to match what I am looking for...

Basically, is there any way to tell DTS to create a new table each time that the backup is run? I am scheduling the backup for 1 hour intervals for 5 days, but need the databases that are backed up to be unique, so i would end up wiht 120 of them total.

Is there any way to do this through DTS? Or am I hosed?

Thanks in advanceYep, you can do it. You'll have to adapt what's in the links, but it does work. You can pass variables into a DTS job and have it alter execution each time it's run.

http://www.swynk.com/friends/green/textfile.asp
http://www.swynk.com/friends/green/DTSHowTo3.asp

http://www.sqldts.com/
http://www.dts2000.com/

Renaming databases with grace, any hint ?

Hi,

I want to implement the following pattern (pseudo-code follows):

[for index in x different databases, where x is rather big]

Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.

My question: what is the simpliest way to achieve the rename operation (step 3) ?

I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.

I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.

I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?

best,

Thibaut

The best method for what you describe is probably to DETACH the "last good" and ATTACH the "new" as the old name. Yes, you will need to know the file names.
|||Thanks Tom. Too bad I have to know the file names, this pattern is very useful...

sql

Renaming databases sloooow

We have SSAS 2005 setup which rebuilds our cubes each night on a single server. What we were trying to do is make it so that each night a second database is built and processed for the next days data, and then the previous days database is dropped and the new one renamed to the correct name. This was meant to minimize downtime. The problem is, renaming the database is slow (it takes hours!) .. is this a bug? We've tried renaming it through AMO and SQL Studio, both of which cause the CPU to go up to 50% for more than 2 hours before the rename completes. These databases have approx 8 cubes with many measure groups.

You should be able to change database name by issuing a simple XMLA command like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>CountMeasure</DatabaseID>
</Object>
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>MyDb1</ID>
<Name>MyDb2</Name>
</Database>
</ObjectDefinition>
</Alter>

Try running Profiler and see what is happening during database rename.
During rename you should see bunch of Notification events about changes in database objects.

See how long in average each Notification Event takes. Is it possible rename of the database is getting blocked by some other operation?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Renaming Database or files

Hi all, I have a dev database on a dev machine that is the same name as a database on a production server.

I would like to attach the dev database to the production server but I can't because there would be two database with the same name. I tried just renaming my dev .mdf to another name but that doesn't work., I think I need to rename the database files but I don't know how.

Can anyone tell me the solution to this problem?

Thanks

This will rename pubs to pubs2

EXEC sp_renamedb 'pubs', 'pubs2'

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks SQL_Menace, I'll give that a try

Renaming Database Files

How can I go about safely renaming the data and log files for a
database?
Thanks.Hello ExcelMan,
I would go this way:
- Detach the database
- Rename the files
- Attach the database
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>|||On Aug 31, 11:31 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem =D6nsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Did you specify the new names when you executed sp_attach_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
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||There must be something you miss.
Tom could be right. How about trying this process via SSMS?
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Logical or physical ones?
Whatever you try, first make a full backup.
See "alter database" statement in BOL. One way could be doing a backup and
restore it using options "replace" and "move".
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
backup database test
to disk = 'c:\temp\test.bak'
go
alter database test
set single_user with rollback immediate
go
restore database test
from disk = 'c:\temp\test.bak'
with replace,
move 'test' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.ldf'
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
drop database test
go
I played a little bit trying to change the physical file names usainf "alter
database" but there is a msg saying:
"The new path will be used the next time the database is started."
but I do not know how to start the db programatically. I tried setting it to
offline and then online but it fails. Here is the script.
use master
go
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set single_user with rollback immediate
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
alter database test
modify file (name = 'test1', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf')
go
alter database test
modify file (name = 'test1_log', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.mdf')
go
alter database test
set online
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set multi_user
go
drop database test
go
AMB
"ExcelMan" wrote:
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>

Renaming database after log shipping.

In order to move from one data center to another, I am planning to use log shipping.
Lets say,

Current Data center - DC1
Current Server- S1
Current Database- DB1

New Data Center -DC2
New Server -S2

Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.

So for this log shipping, -

Primary - S1- DB1
Secondary - S2 DB1_New

So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.

So want to know, will this renaming the database (DB1_New to DB1) cause any issues?

Anyone had similar experience?

Thanks

Generally, you would be okay - some of that depends on how you have things configured, how long you are going to ship the logs before failing over to the other site, if logins/users have changed. You would want to consider what jobs you have running on the server, how this impacts your fail over plans and backups, anything pointing to the DB_new database name would need to be evaluated, etc. After the fail over and rename, you'd probably want to do a full backup of your databases, make sure the users are all intact, etc.

-Sue

|||

hi,

even if u have the same dbs in both source and detination prior to configuring log shipping, u need not create a new db in secondary instead u can drop the db in secondary......and while configuring log shipping u have the option to create the db @.destination if the db doesnt exist there already........if the failover is done properly all users from primary will be able to connect.....ensure that wherever u specified the db1 name in application change it to db1_new in your scenario........

Renaming Database

I got an MSSQL database which I'm trying to export to MySQL using DTS, the name of the database is MyDatabase.com and when I try to export the tables I get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Unspecified error
Could not find server 'MyDatabase.com' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Context: Error calling GetRowset to get DBSCHEMA_TABLES schema info. Your provider does not support all the schema required by DTS.

My guess is that since the database name ends with .com DTS is thinking that it's a server and not a db..... I got 2 databases that end with .com and a few others that don't, if I try to export any of the ones that end with .com I get the same error, but if I try to export another one it works just fine... My question is, is there any way I can rename the database to something else or is there any other way around this problem..?

Thanks in advance,
Fernansp_renamedb will rename a database.

Have you tried [MyDatabase.com]sql

renaming database

I tried to renaming database using query analyser of sql
server 2000 but I got this error msg.
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform
the operation.
My sysntax was
sp_renamedb 'old_dbname', 'new_dbname'
How to handle this? what could be the solution?
Thanx
Dave
hi dave,
make sure no-one else is connected to the database, including you.
you can try:
use master
go
exec sp_renamedb ..........
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
--if, still you get errors you can run following command,to disconnect other
users:
--note:it will rollback existing transaction
alter database <db_name> set single_user with rollback IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||still same proble, bro...
dave
>--Original Message--
>hi dave,
>make sure no-one else is connected to the database,
including you.
>you can try:
>use master
>go
>exec sp_renamedb ..........
>--or (if sql 2000) you can try
>alter database old_Database_name modify name =
new_database_name
>--if, still you get errors you can run following
command,to disconnect other
>users:
>--note:it will rollback existing transaction
>alter database <db_name> set single_user with rollback
IMMEDIATE
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Try this. Stop the SQL Server from SQL Server Service Manager and Start
again, and then try to rename it.
Rohtash Kapoor
http://www.sqlmantra.com
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28f5f01c4656d$493c0f50$a601280a@.phx.gbl...[vbcol=seagreen]
> still same proble, bro...
> dave
> including you.
> new_database_name
> command,to disconnect other
> IMMEDIATE
|||Hi,
Follow the below steps to rename a database in SQL server 2000.
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
3. Set to multi user mode
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28e1e01c4654c$a8962ab0$a601280a@.phx.gbl...
> I tried to renaming database using query analyser of sql
> server 2000 but I got this error msg.
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform
> the operation.
> My sysntax was
> sp_renamedb 'old_dbname', 'new_dbname'
> How to handle this? what could be the solution?
> Thanx
> Dave
|||all you can do is restart the sql server service, which will clear out all
the connection to the db, and run
use master
go
exec sp_renamedb --
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You could detach the database and then re-attach it with a different
name.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP

Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP

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 column

ALTER TABLE ISMPBI CHANGE `PBICCGRFNUM` `PBICCGRFNUM1` BIGINT NOT NULL;
this query to rename column name in my sql but not working in sql server
so what is exact syntax to rename columnSee the syntax for the ALTER TABLE command in the online manual (http://msdn2.microsoft.com/en-us/library/ms190273.aspx)

Btw: don't use the backticks. They are a MySQL "speciality" and do not work with any other DBMS (actually they are not needed in MySQL as well)|||MS SQL SERVER does not support RENAME syntax i cant find the syntax to rename column can any one give me exact syntax to rename column|||The ALTER TABLE documentation links to the sp_rename (http://msdn2.microsoft.com/en-us/library/ms188351.aspx) function:
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type
Example B: "renaming a column"|||thanks

here is exact syntax

EXEC sp_rename 'BFMENUDEFINITION.[GROUP]', 'MENUGROUP', 'COLUMN';

'BFMENUDEFINITION== tablename

GROUP == old column

'MENUGROUP'== new columnsql

Renaming Cluster Nodes

Realise it is a common question and have searched through the group:
I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
renamed (Nodes, cluster name)
Is there any way to bypass re-installing SQL 2000 after this has been
completed?
(Various non microsoft sites point towards using sp_dropserver and
sp_addserver or updating the syservers table)
eg http://blog.opsan.com/archive/2005/05/10/465.aspx
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
Thanks.
why you need rename nodes?
you can change virtual server name but microsoft not recommend this.
do NOT update system tables manually.
if you rename sql server (sp_dropserver and sp_addserver), cluster will not
check sql status and servis will be mark as failure.
best solution: install new sql
Aleksandar Grbic
MCDBA, Senior Database Administrator
"RichardT" wrote:

> Realise it is a common question and have searched through the group:
> I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
> renamed (Nodes, cluster name)
> Is there any way to bypass re-installing SQL 2000 after this has been
> completed?
> (Various non microsoft sites point towards using sp_dropserver and
> sp_addserver or updating the syservers table)
> eg http://blog.opsan.com/archive/2005/05/10/465.aspx
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> Thanks.
|||"RichardT" <RichardT@.discussions.microsoft.com> wrote in message
news:7DE1DC20-FF62-4B1B-BE0A-6151F11D06F9@.microsoft.com...
> Realise it is a common question and have searched through the group:
> I have a Windows 2003 R2 cluster (with SQL 2000 SP4) which needs to be
> renamed (Nodes, cluster name)
> Is there any way to bypass re-installing SQL 2000 after this has been
> completed?
> (Various non microsoft sites point towards using sp_dropserver and
> sp_addserver or updating the syservers table)
> eg http://blog.opsan.com/archive/2005/05/10/465.aspx
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
Well, I would start with going back to basics and doing a risk assessment
associated with the change and mitigation strategies. Risk, everything
possible going wrong. Mitigating that risk: Don't make the change as it is
completely unnecessary.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
NYC starting Feb 19th
London starting March 6th

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 and migration Problems

Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
moved the Database over to this new server, then renamed the server and
assigned it the same IP as the old one. then we did the sp_dropserver
and ap_addserver script to cange the SQL server name. the problems that
we keep getting it that the client machines can't connect to the SQL
Server now. They can see the server and ping to it but can't access the
actual SQL databses. the error message that I get is that its "SQL
Server cannot be found or access denied". any ideas? we have been
trying to get his new server up and running but keep running into this
issue. are we missing any steps?
Thanks
Hello,
Did you restarted the SQL Server and SQL Agent service after issuing
SP_DROPServer and SP_ADDSERVER commands?
As well as did you gave the below command to add server
SP_ADDSERVER 'Newservername','LOCAL'
Try both and keep us posted..
Thanks
Hari
<rncota@.gmail.com> wrote in message
news:1168899609.093776.80630@.l53g2000cwa.googlegro ups.com...
> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
|||Hi
"rncota@.gmail.com" wrote:

> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
If you connect via query analyser on the machine itself and SELECT
@.@.SERVERNAME do you get the new name or the old one? If the old one check
that you are dropping the server correctly as described by
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
You don't say if you transferred the logins as well as the database, see
http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
will show any orphaned users.
Have you tried connecting with the IP address? Check which protocols are
installed on the server and client and that the clients don't have any
aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
not specified as on when installing.
John
|||Hari Prasad wrote:
> Hello,
> Did you restarted the SQL Server and SQL Agent service after issuing
> SP_DROPServer and SP_ADDSERVER commands?
> As well as did you gave the below command to add server
> SP_ADDSERVER 'Newservername','LOCAL'
> Try both and keep us posted..
> Thanks
> Hari
>
Yes, we used that process to change the internal name of the SQL
Server. We checked the name using Select @.@.SERVERNAME. also we only
have one instance running, the defualt one.
|||> If you connect via query analyser on the machine itself and SELECT
> @.@.SERVERNAME do you get the new name or the old one? If the old one check
> that you are dropping the server correctly as described by
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> You don't say if you transferred the logins as well as the database, see
> http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
> will show any orphaned users.
> Have you tried connecting with the IP address? Check which protocols are
> installed on the server and client and that the clients don't have any
> aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
> not specified as on when installing.
> John
Yes, we checked using Select @.@.SERVERNAME and the new server name comes
up. We also migrated the logins as well as DTS packages and jobs. When
I run exec sp_change_users_login 'Report' nothing comes up as being
orphaned.
I can ping the server using both the Name of the Server and the IP
address of the server through a command prompt from a client machine.
the problem comes up when i try to connect through applications that
access the databases. an example of how I test it was through and ODBC
connection that is required for one of our apps. When went in to test
the connection to it in the ODBC settings, it says that it cannot find
the specified server. I know the login infomation is correct and I know
the logins exisit.
Just to give more information to help figure out this problem, another
example is when we were testing our DTS packages on the SQL server,
anywhere that referenced the name to the SQL Server would come back
with an error telling us that the server could not be found. which is
odd because these packages are sitting inside that server. Very odd. I
feel like there is something missing some where, but we have done
everything by the book from what I can tell.
|||Also i just did a test, since we couldn't get the new SQL server
running properly we went back to the old server. the new server is up
but now running on a different name. When i test a ODBC connection to
the new server using a connection with the name is has now, the
connection works fine. seems to be some issue when we try to pass on
the name and IP of the old server to it.
|||rncota@.gmail.com wrote:
> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
Try this simple test:
1. Choose a login that you are attempting to connect with, one that
fails, i.e. login name "bob"
2. Open a Query Analyzer session, connect to your application database
as "sa"
3. EXEC sp_dropuser 'bob'
4. EXEC sp_adduser 'bob'
5. EXEC sp_addrolemember 'db_datareader', 'bob'
Now test a connection to the application database using the login 'bob'
- does it connect successfully?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||
> Try this simple test:
> 1. Choose a login that you are attempting to connect with, one that
> fails, i.e. login name "bob"
> 2. Open a Query Analyzer session, connect to your application database
> as "sa"
> 3. EXEC sp_dropuser 'bob'
> 4. EXEC sp_adduser 'bob'
> 5. EXEC sp_addrolemember 'db_datareader', 'bob'
> Now test a connection to the application database using the login 'bob'
> - does it connect successfully?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I tried this test, it works fine when I try to connect to the server
with its current name but when We change the server name it stops
working.
|||Hi
"rncota@.gmail.com" wrote:

> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
By being able to connect to the server when the name was changed back means
that your protocols are ok. Did you check for aliases on the client? Have you
tried to connect using the IP? Can you connect using Query Analyser from the
client? When you changed the name of the server (for the second time) did you
drop the server and add it again?
John

Friday, March 30, 2012

renaming drive letter

renaming drive letter

renaming drive letter

renaming domain

renaming domain

Renaming Destination in DTS

Renaming databases with grace, any hint ?

Renaming databases sloooow

Renaming Database or files

Renaming Database Files

Renaming database after log shipping.

Renaming Database

renaming database

Renaming Data Source view giving error

Renaming Data Source view giving error

Renaming Computer Running SQL or MSDE

Renaming column

Renaming Cluster Nodes

Renaming cluster

Renaming and migration Problems

Removing a server

Blog Archive

About Me