How to change the instance name?

Hello DBA's

I have installed SQL Server 2005 on a machine with named instance, but later I noticed that I created it with the wrong name than desired. Now I need to change the instace name of the SQL Server that I have installed. How can I do that

Thanks

Satya

There is not a supported way to rename an instance. You need to uninstall and then reinstall.

Thanks,

Peter Saddow

|||

Thanks Peter

Satya

how to change the instance name back to default of SQL2k5

hi,

While installing SQL SERVER 2005, I had opted for providing custom name for the SQL Server and named it as for eg. as 'xyz' . Now I would prefer to change it back to default instance so that I can use server=localhost in my connection string of my ASP.NET page. With the custom instance name everytime I have to give
server="Machinename\xyz" which is annoying as I will have to change the connection strings in so many places for my exisiting ASP.NET page.

For e.g.

strConnection="server="Machinename\xyz";database=test;Integrated Security=SSPI;";

I tried using server=(local). It did not work...:(

Also on my another machine which has SQL2k5 installed with default instance I am able to use this string:
strConnection="server=localhost;database=test;Integrated Security=SSPI;";

while the same string I cannot use on the one in which I provided the instance name.

Guess uninstall is the only way.

anyone knows how can I change it back to default instance?

I'm not sure if there is a way to change an instance name. If you must do this, install another copy of SQL Server with the default instance name. Then backup your databases in the xyz instance. Restore them in the default instance. Then uninstall xyz instanace. As for the programs with changing connections strings in web apps, I would recommend pointing them to datasources instead of putting connections string in there. Then, if you modify your sql server configuration you simply have to change the datasouce and not all of your web.config files.|||

Thanks JonM. I know how to create datasources but I don;t know what needs to be put in the ASP.NET file or web.config files to establish the connection.

Suppose I have database called 'test' with DSN name as 'test' in the 'xyz' instance of SQL Server on my machine named 'Development'

How do I achieve this?

Thank you once again for your help.

|||

Basically you change your connection string to 'Datasource=xyx', and remove all of the other stuff, you may still have to specify a username and password if you didnt specify it in the datasouce.

sql

How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

|||I am referring to the path that you see in the Service Properties through the SQL Server Configuration Manager, the tooltip and the Online Help is a bit contradictory concerning this path. Nevertheless I figured out where to change it in the registry but instead I did a full reinstall of SQL Server 2005.|||

You don't want to change this path. If you do the SQL Server won't start. The "Binary Path" which is listed is the path to the actualy exe file which is the SQL Server service. The only way to change this would be to uninstall and reinstall the SQL Server.

What is the end result that you are trying to get?

|||If you look closely and the tooltip for that path and the same explanation in the help when you hit F1 then you will notice that it is not really clear what this path is for. This path is not the binary path because I installed SQL Server in C:\Program Files\... and during the installation I changed the location for the databases to D:\Databases. The path I am talking about was set to D:\Databases but afterwards I had to move the data files to E:\Databases so in SQL Server I changed the database location to E:\Databases but for some reason the in the SQL Server it still pointed to D:\Databases which in the meantime no longer existed. I finally had to reinstall SQL Server anyway because the binaries had to be in the D:\Program Files\ and not on the C-drive.

How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

|||I am referring to the path that you see in the Service Properties through the SQL Server Configuration Manager, the tooltip and the Online Help is a bit contradictory concerning this path. Nevertheless I figured out where to change it in the registry but instead I did a full reinstall of SQL Server 2005.|||

You don't want to change this path. If you do the SQL Server won't start. The "Binary Path" which is listed is the path to the actualy exe file which is the SQL Server service. The only way to change this would be to uninstall and reinstall the SQL Server.

What is the end result that you are trying to get?

|||If you look closely and the tooltip for that path and the same explanation in the help when you hit F1 then you will notice that it is not really clear what this path is for. This path is not the binary path because I installed SQL Server in C:\Program Files\... and during the installation I changed the location for the databases to D:\Databases. The path I am talking about was set to D:\Databases but afterwards I had to move the data files to E:\Databases so in SQL Server I changed the database location to E:\Databases but for some reason the in the SQL Server it still pointed to D:\Databases which in the meantime no longer existed. I finally had to reinstall SQL Server anyway because the binaries had to be in the D:\Program Files\ and not on the C-drive.

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:
> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
> > Database space can be allocated using EM. But sometimes people allocated a
> > huge size for data or log file, for instance, 10GB.
> >
> > When the database goes into running for production, maybe only needs maximun
> > 1GB
> > . So I want to reduce this size.
> >
> > Shrink only truncate log.
> >
> > How to do this with recreating a db. How to know the space used in db? For
> > instance, maybe only 300MB used although 10GB allocated.
> >
> > Does the huge space affect the db performance?
> >|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
> > Hi
> >
> > Check out DBCC SHRINKFILE see
> > http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> >
> > If you allocate the size too small the database will only spend time and
> > resources growing the database, therefore it should be a reasonable initial
> > size that avoids this. If the files are always growing and being shrunk you
> > may get fragmentation of the physical files may affect performance.
> >
> > If you are in full recovery mode and have regular log backups, the size of
> > the log file should be reasonably constant. You should make sure that the
> > initial size is also large enough to cover your normal usage. Read
> > http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> > information.
> >
> > Make sure that your filegrowth is not set to a percentage otherwise it would
> > grow exponentially.
> >
> > John
> >
> > "KentZhou" wrote:
> >
> > > Database space can be allocated using EM. But sometimes people allocated a
> > > huge size for data or log file, for instance, 10GB.
> > >
> > > When the database goes into running for production, maybe only needs maximun
> > > 1GB
> > > . So I want to reduce this size.
> > >
> > > Shrink only truncate log.
> > >
> > > How to do this with recreating a db. How to know the space used in db? For
> > > instance, maybe only 300MB used although 10GB allocated.
> > >
> > > Does the huge space affect the db performance?
> > >sql

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maxim
un
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initia
l
> size that avoids this. If the files are always growing and being shrunk yo
u
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it wou
ld
> grow exponentially.
> John
> "KentZhou" wrote:
>|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DB
CC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
>

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
.. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?
Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>
|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:

How to change SQL Server 2005 instance name

Hi,
I have installed SQL Server 2005 Express Edition on my system. The default instance name is set as SQLEXPRESS. I want to change this to something else. Is there anyway I can do that?
Thanks,
pravi

Hi Pravi,

here is a reference material for renaming a Server / Instance

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp

HTH

Hemantgiri S. Goswami

|||

hi Pravi,

named instances can not be renamed...

regards

How to change Name Server/instance?

I've change the computer's name. So my SQLServer doesn't work. Now, How to
change Name Server/instance? So my SQLServer can work again.> I've change the computer's name. So my SQLServer doesn't work. Now, How to
> change Name Server/instance? So my SQLServer can work again.
http://www.karaszi.com/sqlserver/in...server_name.asp
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||hi
For this, u need to drop the existing server
sp_dropserver '<old server>'
and add a new server name. do not forget to add the reserved word "local'
sp_addserver '<new server>', 'local'
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Bpk. Adi Wira Kusuma" wrote:

> I've change the computer's name. So my SQLServer doesn't work. Now, How to
> change Name Server/instance? So my SQLServer can work again.
>
>sql

How to change instance name

In SQL 2000 SP3 how do you change instance name
Here's the answer:
http://vyaskn.tripod.com/administration_faq.htm#q13
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A9A4ED1A-2A8B-49C8-950B-E4EE2A4E0F39@.microsoft.com...
In SQL 2000 SP3 how do you change instance name

How to change instance name

In SQL 2000 SP3 how do you change instance nameHere's the answer:
http://vyaskn.tripod.com/administration_faq.htm#q13
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A9A4ED1A-2A8B-49C8-950B-E4EE2A4E0F39@.microsoft.com...
In SQL 2000 SP3 how do you change instance namesql

How to change instance ?

I did an upgrade to my MSDE on my machine, but i use a different name (Infinity) instead of SQLEXPRESS or MSSQLSERVER. I'm wondering if there is a way for me to change it back to SQLExpress ?

There is no way to rename an instance in SQL Server. You'll need to install a new instance and move your database from the old instance to the new instance.

Cheers,

Dan

how to change "Default language for user" for Instance name? (with regedit or other method

Hi all,
how to change "Default language for user" for Instance name? (with regedit
or other method?)
I want to change other language for my default language for user option
programaticly (in setup program)
ThanxAt the instance level you use sp_configure 'default language' specifying the
langid you want (this value can be obtained from the syslanguages table in
master)
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Asking" <asking@.ispro.net.tr> wrote in message
news:OybHe5moGHA.3808@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> how to change "Default language for user" for Instance name? (with regedit
> or other method?)
> I want to change other language for my default language for user option
> programaticly (in setup program)
>
> Thanx
>|||Thank you very much
"Asking" <asking@.ispro.net.tr> wrote in message
news:OybHe5moGHA.3808@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> how to change "Default language for user" for Instance name? (with regedit
> or other method?)
> I want to change other language for my default language for user option
> programaticly (in setup program)
>
> Thanx
>

how to build a sql server instance in the network using sql express?

Hi,

how to build a sql server instance in the network using sql express?

thank you.

Have a look at the install documentation and books only for the setup command line switches, you should be able to get the data there to set up the instance, Then just do the install using the net setup config, you may have to add a setup.ini file to the same directory as the installer. (At least that is how we did it with MSDE).

|||Thank you very much. However, Where can I find those documents you said?|||

The SQL Books online is installed with the sql products and can also be downloaded from the Microsoft Downloads Site. You can also find information on the installer options in the readme files for the express package.

|||I am sorry I still cannot find the information in the SQL Books Online. Could you give a link where the location of the information is? Thank you very much.|||

hi,

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx

regards

|||oops, I think I asked a wrong question. I will open a new thread with the correct question. Sorry!

how to build a sql server instance in the network using sql express?

Hi,

how to build a sql server instance in the network using sql express?

thank you.

Have a look at the install documentation and books only for the setup command line switches, you should be able to get the data there to set up the instance, Then just do the install using the net setup config, you may have to add a setup.ini file to the same directory as the installer. (At least that is how we did it with MSDE).

|||Thank you very much. However, Where can I find those documents you said?|||

The SQL Books online is installed with the sql products and can also be downloaded from the Microsoft Downloads Site. You can also find information on the installer options in the readme files for the express package.

|||I am sorry I still cannot find the information in the SQL Books Online. Could you give a link where the location of the information is? Thank you very much.|||

hi,

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx

regards

|||oops, I think I asked a wrong question. I will open a new thread with the correct question. Sorry!