SQL Server Performance Forum – Threads Archive

I need to move MsSqlServer from C to D

Hi, i have a hard disk memory problem: my C partition is full. I misconfigurated the server, thinking that C would have never become full, but now it is [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] So, I need to set sql server so that it uses D partition to store data and logs, and of course I need to do it without re-installing Sql Server… Any suggestion? Thanks in advance for any helps or suggestion… Ciao Filippo Filippo

I saw that opening enterprise manager and right-clicking on the local db..then Properties, there is a voice under DATABASE SETTINGS that says "New Database default location", with the possibility to browse to the new destination folder for both "DEFAULT DATA DIRECTORY" and "DEFAULT LOG DIRECTORY". Is it so simple? Can i just browse to d:mssqlackup and d:mssqldata ?? Filippo Filippo

You can use sp_detach_db to detach databases, move them and attach them using sp_attach_db. Bambola.

Thank you bambola,
tell me please if what follows below is right (i have to do that for 2 DBs): 1) I stop mssqlserver 2) run: sp_detach_db ‘name_of_db1’ (true or false? or skipping second argument?) 3) run: sp_detach_db ‘name_of_db2’ (again, second argument?) 4) copy: name_of_db1.ldf, name_of_db1.mdf, name_of_db2.ldf, name_of_db2.mdf to new location on drive D 5a) EXEC sp_attach_db @dbname = N’name_of_db1′,
@filename1 = N’d:
ewlocationMSSQLData
ame_of_db1.mdf’,
@filename2 = N’d:
ewlocationMSSQLData
ame_of_db1.ldf’ 5b) EXEC sp_attach_db @dbname = N’name_of_db2′,
@filename1 = N’d:
ewlocationMSSQLData
ame_of_db2.mdf’,
@filename2 = N’d:
ewlocationMSSQLData
ame_of_db2.ldf’ 6) start mssqlserver — right this way? Please telle me about second argument of sp_detach_db, cause I’m not sure about its meaning… Grazie
Filippo Filippo

The second parameters tells if to run update statistics before detaching database. false will run it, true will skip it.
It seems ok. There is no need though to stop sql server.
If you find it too confusing, you can use EM to do this task.
Bambola.

U won’t be able to deatch the database if SQL Server is not running. So U have to keep SQL Server running. However, restarting the SQL Server will close all the open connections and so u’ll not have any problems while detaching. I am not very sure if this command can be used to detach the system databases. Hey guys! Any ideas? Gaurav

Thanks bambola, everything went good <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Gaurav, I’ve just seen your post now. Btw I want to say thans to you too. I didn’t stop the service as you and bambola have suggested here. Everything SEEMS to be ok…and I hope I won’t get errors in the next days.. Thanks again Ciao Filippo

]]>