Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Default file Locations Expand / Collapse
Author
Message
Posted Friday, January 4, 2008 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:20 PM
Points: 161, Visits: 51
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
Post #438976
Posted Friday, January 4, 2008 9:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:52 AM
Points: 200, Visits: 332
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




Post #439018
Posted Friday, January 4, 2008 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:20 PM
Points: 161, Visits: 51
Thank you very much for prompt reply.
Post #439024
Posted Wednesday, April 21, 2010 8:57 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 4, 2013 5:34 AM
Points: 39, Visits: 455
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
Post #907799
Posted Wednesday, April 21, 2010 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:52 AM
Points: 200, Visits: 332
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.



Post #907887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse