SQL File Locations

[circulent]

I have a new SQL 2005 server, and would like to make sure all database related files are saved onto a separate partition. When do I specify this when I install SQL? Alternatively, how can I move all database related files once an installation is already complete?

Thanks

 

[ptheriault]

for future databases you can do this by setting the database default location.

Right click on the server instance in SSMS and select properties. you will find the Database default locations on the Database settings page.

For existing databases you will have to detach them, move the files and attach them.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

When you install SQL Server, it will ask you where you want the files....these are the system files including the system databases.

Once it is installed, user databases are set up based on the default settings as Paul explained.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

To get to the screen that SQLBill talks about you have to click the advanced button on the screen that asks you what services to install.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

I should also mention that you can't just move the system databases.
To move the master you have to change the file locations in the start up parameters.
To move model and temp use the alter database command.

I usually use the restore with move option to move the msdb database. But if you enable a trace flag you can detach and attach it like user databases. I forgot what number the trace flag is but Denny or Bill might know it.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

1207 or something?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[circulent]

Great. Thanks guys

 

[SQLBill]

Isn't it the -T3608 flag?

-SQLBill

Posting advice: FAQ481-4875

 

[DTracy]

Hi guys,

I have almost the same situation here with a new W2003 server with SQL2005, and two W2000 servers with SQL2000 databases. I want to move the SQL2000 databases onto the new SQL2005 server. I read about the detach/attach thing in the MSKB. When using 2005 and I detach a database does reattach copy the contents onto the 2005 server?

Thanks and regards,
David.

 

[mrdenny]

Could be, I use the trace flags so rarly I don't have any of them memorized.

When you attach a database all of the data within that database is available on the new server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com