Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Default file Locations


Database Default file Locations

Author
Message
Bhushan Kalla
Bhushan Kalla
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 71
Hi Everyone,
I would like to modify the "Database default locations" in Database settings under server properties using T-SQL script. The easy way to do this one through GUI on Management Studio, right click on the SQL Server instance and click on properties then select "Database Settings" option and change the "Database default locations" to desired locations for Data and Log files". But I want to do this through T-SQL script if I know which system table holds these file location information. Any help is greatly appreicated.

Thanks,
BK
starflyer
starflyer
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 388
You can script from the action you do in the GUI. I didn't see this anywhere else (like any of the property functions), but this will do the job in script format:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLdata\NewPath'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\SQLlogs\NewPath'
GO



Bhushan Kalla
Bhushan Kalla
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 71
Thank you very much for prompt reply.
Fabrizio Faleni
Fabrizio Faleni
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 456
Hello, I know that this thread is old, but...
I would like to script a change of SQL default files location on a SQL2005 Failover Cluster. I am afraid that modifying the Registry "manually" for this change might write the right parameters on the active cluster node, leaving the inactive node unchanged. Do you agree on this?
Did everyone try the above script on a 2 node Failover cluster?
Thanks in advance and Best regards,

Fabrizio

MCITP Database administrator 2008
MCTS SQL Server 2008 Implementation and maintenance
MCTS Sharepoint configuration
MCP Designing Deploying and Managing a Network Solution for the Small and Medium-sized Business
ITIL V3 Foundation

starflyer
starflyer
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 388
Old thread, but still got the email.

I would agree that it would not carry over to the inactive cluster. Although, I have not tried it on a cluster, but have worked with them in the past. I would assume that type of change would not show up on the inactive node.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search