user database directory - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

user database directory

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-05-03 : 11:05:00

The network administrator setup a test instance of sql server 2008 r2 standard edition for me to work.I have little experience with sql server administration and deploying a small C#.net 2010 web application. Due to this fact, I have the following questions:

1. Can you tell me how I can find where the "user database directory" and "Temp DB Log directory" are located?

2. Can you tell me how to change the location of where the "user database directory" and "Temp DB Log directory" are located at?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-04 : 18:41:36

If you are asking where the physical database files are located, you can right-click a database in Management Studio, bring up Properties and then look under Files. The files Path and Names are listed.
Moving database files is doable but a little more complicated. Follow these steps:

use MASTER

---**************************************************************************
--- 1) Define new file location(s)
---**************************************************************************

ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'T:\SQLTemp\TempDB.mdf')
go
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempLog.ldf')
go

---**************************************************************************
--- 2) Stop the SQL Service
---**************************************************************************

---**************************************************************************
--- 3) Physically move the data file(s) to the new location (OS level)
--- (Optional for TempDB)
---**************************************************************************

---**************************************************************************
--- 4) Restart the SQL Service and any dependent services
--- (e.g., SQL Agent)
---**************************************************************************

---**************************************************************************
--- 5) Validate the change (Sanity Check)
---**************************************************************************

SELECT
name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TempDB');

---**************************************************************************
--- 6) Physically delete the original data file(s) (OS level)
--- (Optional but a good idea!)
---**************************************************************************

---**************************************************************************
--- 7) Make a backup of the MASTER database
--- (Optional but a very good idea!)
---**************************************************************************

Moving the MASTER database is even more involved. Google (Bing?) it and/or look it up in Books Online.

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources