Database Resource

Synopsis

Manages SQL Server databases, including creation, configuration options, ANSI settings, performance options, and availability features.

Type

OpenDsc.SqlServer/Database

Capabilities

• Get
• Set
• Delete
• Export

Properties

Connection properties

serverInstance

SQL Server instance name.

Type: string
Required: Yes
Access: Read/Write
Default value: None

connectUsername

Username for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

connectPassword

Password for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

Database properties

name

Name of the database.

Type: string
Required: Yes
Access: Read/Write
Default value: None

collation

Database collation. Defaults to server collation.

Type: string
Required: No
Access: Read/Write
Default value: None

compatibilityLevel

Compatibility level (Version90 through Version160).

Type: string
Required: No
Access: Read/Write
Default value: None

recoveryModel

Recovery model: Simple, Full, or BulkLogged.

Type: string
Required: No
Access: Read/Write
Default value: None

owner

Login name of database owner.

Type: string
Required: No
Access: Read/Write
Default value: None

readOnly

Whether the database is read-only.

Type: bool
Required: No
Access: Read/Write
Default value: None

userAccess

User access: Multi, Single, or Restricted.

Type: string
Required: No
Access: Read/Write
Default value: None

pageVerify

Page verification: None, TornPageDetection, or Checksum.

Type: string
Required: No
Access: Read/Write
Default value: None

containmentType

Containment: None or Partial.

Type: string
Required: No
Access: Read/Write
Default value: None

File properties (write-only, used during creation)

primaryFilePath

Path to primary data file (.mdf).

Type: string
Required: No
Access: Write-Only
Default value: None

logFilePath

Path to log file (.ldf).

Type: string
Required: No
Access: Write-Only
Default value: None

primaryFileSize

Initial primary file size in MB.

Type: int
Required: No
Access: Write-Only
Default value: None

logFileSize

Initial log file size in MB.

Type: int
Required: No
Access: Write-Only
Default value: None

primaryFileGrowth

Primary file growth amount in MB.

Type: int
Required: No
Access: Write-Only
Default value: None

logFileGrowth

Log file growth amount in MB.

Type: int
Required: No
Access: Write-Only
Default value: None

ANSI settings

ansiNullDefault

Whether ANSI NULL default is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

ansiNullsEnabled

Whether ANSI NULLs are enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

ansiPaddingEnabled

Whether ANSI padding is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

ansiWarningsEnabled

Whether ANSI warnings are enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

arithmeticAbortEnabled

Whether arithmetic abort is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

concatenateNullYieldsNull

Whether concatenating null yields null.

Type: bool
Required: No
Access: Read/Write
Default value: None

numericRoundAbortEnabled

Whether numeric round-abort is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

quotedIdentifiersEnabled

Whether quoted identifiers are enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

Performance and behavior settings

autoClose

Auto-close when last user exits.

Type: bool
Required: No
Access: Read/Write
Default value: None

autoShrink

Automatically shrink database.

Type: bool
Required: No
Access: Read/Write
Default value: None

autoCreateStatisticsEnabled

Automatic statistics creation.

Type: bool
Required: No
Access: Read/Write
Default value: None

autoUpdateStatisticsEnabled

Automatic statistics update.

Type: bool
Required: No
Access: Read/Write
Default value: None

autoUpdateStatisticsAsync

Async statistics update.

Type: bool
Required: No
Access: Read/Write
Default value: None

closeCursorsOnCommitEnabled

Close cursors on transaction commit.

Type: bool
Required: No
Access: Read/Write
Default value: None

localCursorsDefault

Default to local cursor scope.

Type: bool
Required: No
Access: Read/Write
Default value: None

nestedTriggersEnabled

Allow nested triggers.

Type: bool
Required: No
Access: Read/Write
Default value: None

recursiveTriggersEnabled

Allow recursive triggers.

Type: bool
Required: No
Access: Read/Write
Default value: None

trustworthy

Database is trustworthy.

Type: bool
Required: No
Access: Read/Write
Default value: None

databaseOwnershipChaining

Cross-database ownership chaining.

Type: bool
Required: No
Access: Read/Write
Default value: None

dateCorrelationOptimization

Date correlation optimization.

Type: bool
Required: No
Access: Read/Write
Default value: None

brokerEnabled

Service Broker enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

encryptionEnabled

Transparent data encryption.

Type: bool
Required: No
Access: Read/Write
Default value: None

isParameterizationForced

Forced parameterization.

Type: bool
Required: No
Access: Read/Write
Default value: None

isReadCommittedSnapshotOn

READ_COMMITTED_SNAPSHOT isolation.

Type: bool
Required: No
Access: Read/Write
Default value: None

isFullTextEnabled

Full-text indexing.

Type: bool
Required: No
Access: Read/Write
Default value: None

targetRecoveryTime

Target recovery time in seconds.

Type: int
Required: No
Access: Read/Write
Default value: None

delayedDurabilityEnabled

Delayed durability.

Type: bool
Required: No
Access: Read/Write
Default value: None

acceleratedRecoveryEnabled

Accelerated database recovery.

Type: bool
Required: No
Access: Read/Write
Default value: None

Read-only properties

id

Database ID.

Type: int
Required: No
Access: Read-Only
Default value: None

createDate

Creation date.

Type: datetime
Required: No
Access: Read-Only
Default value: None

size

Current size in MB.

Type: double
Required: No
Access: Read-Only
Default value: None

spaceAvailable

Space available in KB.

Type: double
Required: No
Access: Read-Only
Default value: None

dataSpaceUsage

Data space usage in KB.

Type: double
Required: No
Access: Read-Only
Default value: None

indexSpaceUsage

Index space usage in KB.

Type: double
Required: No
Access: Read-Only
Default value: None

activeConnections

Number of active connections.

Type: int
Required: No
Access: Read-Only
Default value: None

lastBackupDate

Date of last full backup.

Type: datetime
Required: No
Access: Read-Only
Default value: None

lastDifferentialBackupDate

Date of last differential backup.

Type: datetime
Required: No
Access: Read-Only
Default value: None

lastLogBackupDate

Date of last log backup.

Type: datetime
Required: No
Access: Read-Only
Default value: None

status

Database status.

Type: string
Required: No
Access: Read-Only
Default value: None

isSystemObject

Whether it is a system database.

Type: bool
Required: No
Access: Read-Only
Default value: None

isAccessible

Whether the database is accessible.

Type: bool
Required: No
Access: Read-Only
Default value: None

isUpdateable

Whether the database is updateable.

Type: bool
Required: No
Access: Read-Only
Default value: None

isDatabaseSnapshot

Whether it is a database snapshot.

Type: bool
Required: No
Access: Read-Only
Default value: None

isMirroringEnabled

Whether mirroring is enabled.

Type: bool
Required: No
Access: Read-Only
Default value: None

availabilityGroupName

Availability group name.

Type: string
Required: No
Access: Read-Only
Default value: None

caseSensitive

Whether the database is case-sensitive.

Type: bool
Required: No
Access: Read-Only
Default value: None

primaryFilePathActual

Actual path to primary file.

Type: string
Required: No
Access: Read-Only
Default value: None

defaultFileGroup

Default file group name.

Type: string
Required: No
Access: Read-Only
Default value: None

DSC properties

_exist

Whether the database should exist. Defaults to true.

Type: bool
Required: No
Access: Read/Write
Default value: true

Examples

Example 1 — Get a database

PowerShellShell

$resourceInput = @'
serverInstance: .
name: master
'@

dsc resource get -r OpenDsc.SqlServer/Database --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
name: master
EOF
)

dsc resource get -r OpenDsc.SqlServer/Database --input "$resource_input"

Example 2 — Create a database

PowerShellShell

$resourceInput = @'
serverInstance: .
name: AppDb
recoveryModel: Simple
collation: SQL_Latin1_General_CP1_CI_AS
'@

dsc resource set -r OpenDsc.SqlServer/Database --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
name: AppDb
recoveryModel: Simple
collation: SQL_Latin1_General_CP1_CI_AS
EOF
)

dsc resource set -r OpenDsc.SqlServer/Database --input "$resource_input"

Example 3 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Application database
    type: OpenDsc.SqlServer/Database
    properties:
      serverInstance: "."
      name: AppDb
      recoveryModel: Full
      autoShrink: false
      autoCreateStatisticsEnabled: true
      autoUpdateStatisticsEnabled: true

Exit codes

Code	Description
0	Success
1	Error
2	Invalid JSON
3	Invalid argument
4	Unauthorized access
5	Invalid operation