SQL Server Performance Forum – Threads Archive

Move SQL 2000 DB from C to E

Howdy<br /><br />I have an app (DIY Intranet Application) that when it installs creates the DB’s on CrgFilesSQLserver….<br /><br />I tried creating the databased prior and telling the app to use the shells but it deletes them and put them back on c:<br /><br />I have only 40Gb on C and 400Gb on E:<br /><br />Now that the app is installed I would love to move the DB’s completely to E:<br /><br />I would like to do transparently to the front end (web front end)<br /><br />Could someone lend me some advice please <img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><br />Thanks a mill<br /><br />Mike

---

Read about MODIFY DATABASE from BOL.
Very easy command to use to move db from one location to other

---

Check this out:http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Why not just detach, move files to E:, and reattach in new location?

---

… which is easier and what is referred under Frank’s KBA reference.
Under the application code you can set the path to create the database. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I tried the ALTER DATABSE command with no success:
use master
alter database Rename2
modify file (name = ‘i:datenmssql$isql2logsRename_Log.ldf’, filename = ‘i:datenmssql$isql2logsRename2_Log.ldf’)
returns
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name. Can you please give me a hint? I’ll have to rename several DBs and I’d like to have the logical and physical file names to match the DB name. If this is possible with two lines of T-SQL, I’d prefer this way instead of detaching and attaching the DB. Thanks in advance, Ralph

---

Instead use RESTORE statment to accomplish the task of moving tlog files.
RESTORE FILELISTONLY
FROM DISK = ‘c:Northwind.bak’
GO
RESTORE DATABASE TestDB
FROM DISK = ‘c:Northwind.bak’
WITH MOVE ‘Northwind’ TO ‘c: est estdb.mdf’,
MOVE ‘Northwind_log’ TO ‘c: est estdb.ldf’
GO
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Ralph, did you not read the document Frank provided you? It was pretty specific about how to move user databases on the same server. Read step 1. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Oh yes, I did – I quickly learned that Frank’s links are always worth reading. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> And actually the way described is the one I used last night to ‘move’ the files. It is already faster than the backup/restore I used earlier, but when reading ranjitjain’s post I thought there might be even an easier way.<br />Sorry for not mentioning this.

---

[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] No problem. We always like to hear how people resolved an issue. Frank does give great answers, so you can learn a lot from him.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.

---

Maybe the application simply installs to the default directory, which afaik is somewhere in your stated path. It might help to set the database an log default directories in the server configuration. You can set this by roght-clicking the server object in Enterprise Manager, Properties, Tab ‘Database Settings’. (I don’t know whether there is a T-SQL command, too – I did not find one.)

---

@derrickleggett: <br />Yes, you’re right. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> I didn’t mean to be impolite. Instead of that I found that this forum is amazingly full of deep knowledge and I really do appreciate all of your posts. I’ve read many threads in here since I registered and I learned what kind of problems one can have and the way experienced people approach them. This is something you cannot learn from books. <br /><br />Thanks to the people that are so kind to share their knowledge and – as I found often enough – save some others from desparation. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Sorry Frank, guess I should have checked your KB reference first.

---

]]>