Database Default file Locations

  • 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

  • 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

  • Thank you very much for prompt reply.

  • 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
  • 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.

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

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