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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-03-07 : 20:56:16
|
| New PC arrived. I've installed SQL 2K and SP3.How do I change the location of my databases to drive D?Sam |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-03-07 : 21:13:08
|
| 1. Backup your database using either T-SQL or EM.2. Restore database using T-SQL specifying the move syntax.Look at Move clause, Restore and Backup in BOL3. Backup up newly restored database4. After verifying everything is ok you can drop the original database.You can also you sp_detach_db and sp_attach_dbHere is an article that might also come in handy.http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071Edited by - ValterBorges on 03/07/2003 21:19:35 |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-07 : 22:10:05
|
| Thanks Valter - Your advice helps with my next step, but I didn't make it clear. I appologize for not stating the problem clearly.My older server keeps the databases atD:/SQLdataBut the new server installation is using \Program Files\Microsoft SQL Server\MSSQL....I'd like to change this default to Drive D.Properties of the SQL Server has a tab titled Database Settings. I presume that the Default Data Directory is what needs definition, but it's a multi-folder entity. I was looking for some directions on how to move the default installation to Drive D.I'll need to move the databases next.SamEdited by - SamC on 03/07/2003 22:26:39 |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-03-08 : 13:04:20
|
Hi SamC,Not quite sure what you mean byquote:
but it's a multi-folder entity.
Your correct for new database to be automatically created on the D drive you'll need to change the default data directory and the default log directory. These can be the same directory. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-08 : 13:19:43
|
| After initial SQL server installation, all the databases (master, northwind, etc) are in C:/Program Files/...I want the whole lot of it moved to D:/SQLData/ and kept there for new database creations.Maybe I should remove the installation and try again??Sam |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-03-08 : 17:36:06
|
| No, move the databases as per the kb article and then use EM to specify the default data and log directories (right click on the server and choose properties,click on the Database settings tab). Make sure that the paths don't have a trailing backslash (which they will if you use the browse folder button) and you will probably need to to restart the SQL service for it to take effect.HTHJasper Smith |
|
|
|
|
|
|
default database location