Tag: MSSQL

Some time ago I created automatized installation of SharePoint. One of this set of scripts was script for configuration of clean MSSQL installation. Script uses SQL Server Management Objects (SMO) as API for MSSQL manangement:

# Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $connection

# Create login for SP2016\Administrator
$login = New-Object Microsoft.SqlServer.Management.Smo.Login $server, "SP2016\Administrator"
$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
$login.Create()
$login.AddToRole("sysadmin")

# Create login for SP2016\SP_Admin
$login = New-Object Microsoft.SqlServer.Management.Smo.Login $server, "SP2016\SP_Admin"
$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
$login.Create()
$login.AddToRole("dbcreator")
$login.AddToRole("securityadmin")

# Set max. server memory
$server.Configuration.MaxServerMemory.ConfigValue = 2048
$server.Configuration.Alter()

PowerShell script at the start loads SMO assemblies and connects to the local MSSQL instance. Then creates logins from domain accounts and add it to server roles. At the end the script set maximum memory which can MSSQL allocate for ist operation.

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

In previous post I wrote about the backup of Microsoft SQL Server database using PowerShell. Opposed operation to backup is restore, which can be executed using following PowerShell script:

param
(
    [string] $backupFile = $(throw "Backup file is required"),
    [string] $databaseName = $(throw "Database name is required")
)

$server = "."

Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance $server -Query "ALTER DATABASE $databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $server -Query "RESTORE DATABASE $databaseName FROM DISK='$backupFile' WITH REPLACE"
Invoke-Sqlcmd -ServerInstance $server -Query "ALTER DATABASE $databaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE"

At the first step database is set to single user mode. Next the restore of database is executed. After restore the database is set back to multi user mode.

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

During development we need often create backup of Microsoft SQL Server database. Performing this task using SSMS UI dialog is slow and time consuming activity. This task can be automatized by following PowerShell script:

param
(
    [string] $databaseName = $(throw "Database name is required"),
    [string] $backupToDir = ".",
    [bool] $shrinkLogFile = $true
)

Import-Module SQLPS

if ($backupToDir -eq ".")
{
    $backupToDir = (Get-Item .).FullName + "\Backups\"

    if (-not (Test-Path $backupToDir))
    {
        New-Item -Path $backupToDir -ItemType Directory | Out-Null
    }
}

if ($shrinkLogFile)
{
    $shrinkCommand = "DBCC SHRINKFILE (" + $databaseName + "_log, 0)"
    Invoke-Sqlcmd -ServerInstance . -Database $databaseName -Query $shrinkCommand | Out-Null
}

$now = (Get-date).ToString("yyyyMMddHHmmss")
$backupCommand = "BACKUP DATABASE " + $databaseName + " TO DISK='" + $backupToDir + $databaseName + "_" + $now + ".bak' WITH COMPRESSION"

Invoke-Sqlcmd -ServerInstance . -Query $backupCommand

The script shrinks database log file and backup is created with compression to decrease the size of backup file.

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.