How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
Johnny
Check out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||How to Move TEMPDB from one location to another
--X--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --X--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
--
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--­--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME ='E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME ='E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:
> How to Move TEMPDB from one location to another
> --­--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME => 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME => 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> > Hello,
> >
> > Does anyone know how I can successfully move the tempdb database to a
> > different drive besides C? I tried detaching and re-attaching it but you
> > can't do that with system databases.
> >
> > FYI, I am using SQL Server 2005.
> >
> > Thanks!
> > Johnny
>
>sql

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--_--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --_--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files
,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

How can I get the port of a named instance of SQL Server

Thanks for Microsoft's Windows XP Service Pack 2 and Windows Server 2003 that make the security much stronger. However, besides the invisible benefit so far, I have become the victim of this security policy.

I have several named instances of SQL 7/2000 installed in my machine. They are not visible out of the Microsoft new fire wall. I need to access the instances from outside the fire wall, but I don't know which port I should open for the instances.

From SQL online, the default instance of SQL server is connected through TCP/IP by default port 1433. I could successful open that port and made the default instance visible to outside. However, the port 1433 doesn't work for the named instances. SQL online said, the port for named instance is dynamically (by default) chosen the first time the instance is started. So, actually, I have no way to know the port.

Is there anyway that I can check the database or somewhere to get the port that is used by the named instance?

Thanks for any input and recommendation.> I am the first victim of this security policy.

don't kid yourself. a lot of people have been caught out - many by inadequate preparation or by a simple naivete. your problem seems more of an actual gotcha though.

try turning off the firewall and doing a netstat -a|||Hi, Atrax, thanks for your input. But, ...|||wtf? I didn't follow that at all.|||Never mind.

By the way, I got the solution for the original question. Using SQL Server Network Utility can find all ports for default and named instances.