SqlDatabaseDefaultLocation

SqlDatabaseDefaultLocation

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	The name of the SQL instance to be configured.
Type	Key	String	The type of database default location to be configured. { Data	Log
Path	Required	String	The path to the default directory to be configured.
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
RestartService	Write	Boolean	If set to $true then SQL Server and dependent services will be restarted if a change to the default location is made. The defaul value is $false.
ProcessOnlyOnActiveNode	Write	Boolean	Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server Instance.
IsActiveNode	Read	Boolean	Determines if the current node is actively hosting the SQL Server instance.

Description

The SqlDatabaseDefaultLocation DSC resource is used to configure default locations for user databases. The types of default locations that can be changed are Data, Log, and Backup. For more information about database default locations, please read the article Changing the Database Default Locations.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example shows how to manage database default locations for Data, Logs, and Backups for SQL Server.

In the event this is applied to a Failover Cluster Instance (FCI), the ProcessOnlyOnActiveNode property will tell the Test-TargetResource function to evaluate if any changes are needed if the node is actively hosting the SQL Server Instance.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseDefaultLocation 'Set_SqlDatabaseDefaultDirectory_Data'
        {
            ServerName              = 'sqltest.company.local'
            InstanceName            = 'DSC'
            ProcessOnlyOnActiveNode = $true
            Type                    = 'Data'
            Path                    = 'C:\Program Files\Microsoft SQL Server'

            PsDscRunAsCredential    = $SqlAdministratorCredential
        }

        SqlDatabaseDefaultLocation 'Set_SqlDatabaseDefaultDirectory_Log'
        {
            ServerName              = 'sqltest.company.local'
            InstanceName            = 'DSC'
            ProcessOnlyOnActiveNode = $true
            Type                    = 'Log'
            Path                    = 'C:\Program Files\Microsoft SQL Server'

            PsDscRunAsCredential    = $SqlAdministratorCredential
        }

        SqlDatabaseDefaultLocation 'Set_SqlDatabaseDefaultDirectory_Backup'
        {
            ServerName              = 'sqltest.company.local'
            InstanceName            = 'DSC'
            ProcessOnlyOnActiveNode = $true
            Type                    = 'Backup'
            Path                    = 'C:\Program Files\Microsoft SQL Server'

            PsDscRunAsCredential    = $SqlAdministratorCredential
        }
    }
}