Database Default file Locations

Question

Database Default file Locations

Bhushan Kalla

Default port

Points: 1475
More actions
January 4, 2008 at 8:23 am

#119519

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

starflyer SSC Eights! Points: 979 More actions · Answer 1

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 Default port Points: 1475 More actions · Answer 2

Bhushan Kalla

Default port

Points: 1475

January 4, 2008 at 9:56 am

#764899

Thank you very much for prompt reply.

Fabrizio Faleni Ten Centuries Points: 1249 More actions · Answer 3

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 SSC Eights! Points: 979 More actions · Answer 4

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.