SqlDatabase

SqlDatabase

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
Name	Key	String	The name of the SQL database.
InstanceName	Key	String	The name of the SQL instance to be configured.
Ensure	Write	String	An enumerated value that describes if the database is added (Present) or dropped (Absent). Valid values are 'Present' or 'Absent'. Default Value is 'Present'.	Present, Absent
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
Collation	Write	String	The name of the SQL collation to use for the new database. Default value is server collation.
CompatibilityLevel	Write	String	Specifies the version of the SQL Database Compatibility Level to use for the specified database.	Version80, Version90, Version100, Version110, Version120, Version130, Version140, Version150
RecoveryModel	Write	String	The recovery model for the specified database.	Simple, Full, BulkLogged
OwnerName	Write	String	Specifies the name of the login that should be the owner of the database.

Description

The SqlDatabase DSC resource is used to create or delete a database. For more information about SQL Server databases, please read the following articles Create a Database and Delete a Database.

This resource sets the recovery model for a database. The recovery model controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: full, simple, and bulk-logged. Read more about recovery model in the article View or Change the Recovery Model of a Database.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• Valid values per SQL Server version for the parameter CompatibilityLevel can be found in the article ALTER DATABASE (Transact-SQL) Compatibility Level.

Known issues

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

Examples

Example 1

This example shows how to create a database with the database name equal to 'Contoso'.

The second example shows how to create a database with a different collation.

The third example shows how to create a database with a different compatibility level.

The fourth example shows how to create a database with a different recovery model.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabase 'Create_Database'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'Contoso'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_collation'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'AdventureWorks'
            Collation            = 'SQL_Latin1_General_Pref_CP850_CI_AS'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_compatibility_level'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'Fabrikam'
            CompatibilityLevel   = 'Version130'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_recovery_model'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'FabrikamData'
            RecoveryModel        = 'Simple'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_specific_owner'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'FabrikamDataOwner'
            OwnerName            = 'sa'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to remove a database with the database name equal to 'AdventureWorks'.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabase 'Delete_Database'
        {
            Ensure               = 'Absent'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'AdventureWorks'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}