SQL Server Performance Forum – Threads Archive

Changing system database files location

hey folks, Now that we know how to change the location of Master Database files http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3502 what about changing the location of MSDB, TEMP and MODEL databases files??
It’s not just for sporting, believe me … Could we try to change it manually (to the new locations) and then change the system tables as sysfiles??? thanks a lot
pep solÃ -niubÃ³ ———————— No one travels so high as he who knows not where he is going… {{Oliver Cromwell}}

You can use the process as referred KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;224071 from that post or moves the Model and MSDB databases simply by using the "detach" and "reattach" commands. Review this linkhttp://www.devx.com/dbzone/Article/17564/1763/page/2 for further information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hey hey hey … "System databases master, model, msdb, and tempdb cannot be detached." that’s not new, these databases can’t be treated as the ordinary ones … so sp_detach_db and sp_attach_db dont seem to be working in this context … or they work under other cirumstances?? the script u gave me does most of it using eventually attach and detach ..

Except master other system dbs can be detached & attached, which I can confirm it worked for me. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Well, if I try to execute this rough statement from Query Analyzer, master database connection — sp_detach_db "msdb" I get this error message … — Server: message 7940, level 16, status 1, line 1
— System databases master, model, msdb, and tempdb cannot be detached. Should I configure any setting prior to doing this???
Theres any neglecting parameter I am ommitting? thanks a lot!

It seems you aren’t paying attention to the referred knowledgebase article, as stated :

To move the MSDB database on SQL Server 2000, follow these steps:
In SQL Server Enterprise Manager, right-click the server name and click Properties.
On the General tab, click Startup Parameters.
Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps:
Stop, and then restart SQL Server.
Make sure that the SQL Server Agent service is not currently running.
Detach the msdb database as follows:
Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:Mssql8Data) to the new location (E:Mssql8Data).
Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
Stop and then restart SQL Server.
Reattach the msdb database
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Amazing!! It works absolutetly fine … Sorry for not having taken into account the role of the TRACE FLAGS …. I thought it was just a way to trace how all the process was developing … Silly me, I will walk on my knees to beg your pardon …. Im sorry, again, will read it much more careful, and again T H A N K S

Dont’ be sorry…. its my pleasure and glad to know it worked, by default. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>