dbatools.io = command-line SQL Server Management Studio - Something new

This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.

New database

Request for a new database is a pretty standard and repeatable task. It can be either a simple database based on the model database or more complex with multiple data files and filegroups.

New-DbaDatabase

# simple database creation based on the model database
New-DbaDatabase -SqlInstance $server -Name NewDatabaseBasedOnModel

<#
ComputerName       : localhost
InstanceName       : MSSQLSERVER
SqlInstance        : 4dc570825344
Name               : NewDatabaseBasedOnModel
Status             : Normal
IsAccessible       : True
RecoveryModel      : Full
LogReuseWaitStatus : Nothing
SizeMB             : 16
Compatibility      : Version140
Collation          : SQL_Latin1_General_CP1_CI_AS
Owner              : sa
LastFullBackup     : 01/01/0001 00:00:00
LastDiffBackup     : 01/01/0001 00:00:00
LastLogBackup      : 01/01/0001 00:00:00
#>

# database with more customizations
# using PowerShell splat for better readability
# define parameters
$Params = @{
    SqlInstance = $server;
    Name = 'BrandNewAndShinyDb';
    Recoverymodel = 'Simple';
    Owner = 'sa';
    PrimaryFilesize = 64;
    PrimaryFileGrowth = 128;
    LogSize = 64;
    LogGrowth = 64;
    SecondaryFileCount = 2;
    SecondaryFilesize = 64;
    SecondaryFileGrowth = 128;
    DefaultFileGroup = 'Secondary';
}

# create database based on parameters
New-DbaDatabase  @Params 

<#
ComputerName       : localhost
InstanceName       : MSSQLSERVER
SqlInstance        : 4dc570825344
Name               : BrandNewAndShinyDb
Status             : Normal
IsAccessible       : True
RecoveryModel      : Simple
LogReuseWaitStatus : Nothing
SizeMB             : 320
Compatibility      : Version140
Collation          : SQL_Latin1_General_CP1_CI_AS
Owner              : sa
LastFullBackup     : 01/01/0001 00:00:00
LastDiffBackup     : 01/01/0001 00:00:00
LastLogBackup      : 01/01/0001 00:00:00
#>

New login/user/role

Another piece of the request could be granting access to the new database. So we would need to create a new login on the SQL instance and then user and a custom role (like db_executor):

New-DbaLogin

# set the password for the SQL login
$password = '<It is a str0ng! password>' | ConvertTo-SecureString -asPlainText -Force

# add a new login
New-DbaLogin -SqlInstance $server -Login PSRulez -SecurePassword $password

<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Name         : PSRulez
LoginType    : SqlLogin
CreateDate   : 09/07/2020 07:32:28
LastLogin    : 
HasAccess    : True
IsLocked     : False
IsDisabled   : False
#>

New-DbaDbRole

# create a new database role
$roleName = 'db_executor'
New-DbaDbRole -SqlInstance $server -Database BrandNewAndShinyDb -Role $roleName

<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Name         : db_executor
Parent       : BrandNewAndShinyDb
Owner        : dbo
#>

# configure the role with GRANT EXECUTE (this is not part of the dbatools... yet)
$databasePermissionSet = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet
$databasePermissionSet.Execute = $true
$database = $instance.databases['BrandNewAndShinyDb']
$database.Grant($databasePermissionSet,$roleName)

New-DbaDbUser

# add a new user to the database
New-DbaDbUser -SqlInstance $server -Database BrandNewAndShinyDb -Login PSRulez  -Username PSRulez

<#
ComputerName       : localhost
InstanceName       : MSSQLSERVER
SqlInstance        : 4dc570825344
Database           : BrandNewAndShinyDb
CreateDate         : 09/07/2020 07:42:13
DateLastModified   : 09/07/2020 07:42:13
Name               : PSRulez
Login              : PSRulez
LoginType          : SqlLogin
AuthenticationType : Instance
State              : Existing
HasDbAccess        : True
DefaultSchema      : db
#>

Add-DbaDbRoleMember

# add the new user to the newly created database role
# use -Confirm:$false to skip the prompt
Add-DbaDbRoleMember -SqlInstance $server -Database BrandNewAndShinyDb -Role db_executor -User PSRulez -Confirm:$false

New agent job

Configuring a SQL agent job can be easy too. With a few lines, you can have a brand new job with its steps and schedule. Let’s see how:

dbatools: New-DbaAgentJob

# create a new empty job
New-DbaAgentJob -SqlInstance $server -Job ANewJob -Description 'Just a description'

New-DbaAgentJobStep

# add some steps to the job
New-DbaAgentJobStep -SqlInstance $server -Job ANewJob -StepName FirstStep -Command 'SELECT @@VERSION'
New-DbaAgentJobStep -SqlInstance $server -Job ANewJob -StepName SecondStep -Command 'SELECT @@servername'

New-DbaAgentSchedule

# create a new schedule for the job
# Job done!
New-DbaAgentSchedule -SqlInstance $server -Job ANewJob -Schedule DailySchedule -FrequencyType Daily -FrequencyInterval 1 -StartTime '080000' -Force

New mail account/profile

Finally, the last thing I wanted to show you is how easy we can configure mail service with dbatools.

New-DbaDbMailAccount

New-DbaDbMailAccount -SqlInstance $server -Name MailAccountForMikey -DisplayName 'Mikey Bronowski' -EmailAddress '[email protected]'

<#
ComputerName   : localhost
InstanceName   : MSSQLSERVER
SqlInstance    : 4dc570825344
Id             : 1
Name           : MailAccountForMikey
DisplayName    : Mikey Bronowski
Description    : 
EmailAddress   : [email protected]
ReplyToAddress : 
IsBusyAccount  : False
MailServers    : {}
#>

New-DbaDbMailProfile

New-DbaDbMailProfile -SqlInstance $server -Name MailProfile -MailAccountName MailAccountForMikey

<#
ComputerName  : localhost
InstanceName  : MSSQLSERVER
SqlInstance   : 4dc570825344
Id            : 1
Name          : MailProfile
Description   : 
IsBusyProfile : False
#>

That would be all new stuff I have for you this week.

Thank you,

Mikey