Managing SQL 2012 Server with PowerShell remotely

download In this article I want to talk about managing a SQL 2012 Server with PowerShell from a remote computer. As I’ve mentioned in an earlier blog post PowerShell is becoming more and more important in the day to day task of a system administrator.

As my main work machine is a MacBook I perform my PowerShell from within a virtual machine. This virtual machine runs Windows 8.1.
I am using PowerShell ISE as scripting application. It is a simple scripting tool but it does everything I need at this time to make my PowerShell work easy.

$fldr = "C:\users\robin\Documents\PowerShell\data"
$credential = Get-Credential

$credential.Password | ConvertFrom-SecureString | Set-Content "$fldr\adminpassword.bin"

$password = Get-Content "$fldr\adminpassword.bin" | ConvertTo-SecureString

$cred = New-Object System.Management.Automation.PSCredential("contoso\adminrobin", $password)

New-PSSession -Name SQL -ComputerName sql01.contoso.com -Credential $cred

Enter-PSSession -Name SQL

In the code sample above I connect to the SQL Server with a pssession. With a pssession I execute my PowerShell commands directly on the remote machine, the output from my commands is presented on my Windows 8.1. machine. Because my Windows 8.1 machine is not in the same domain as the SQL machine it has to be added as a trustedhost with this winrm one liner:

winrm s winrm/config/client ‘@{TrustedHosts=”sql01.contoso.com”}’

When I am connected I load the SQL modules, now I can perform SQL management from my Windows 8.1 machine without installing any extra software.

Import-Module sqlps -DisableNameChecking

Set-Location SQLSERVER:\SQL\localhost\default\databases

Get-childitem

In the code sample above I retrieve a list of the available SQL databases on this server.

image

In the output you see in the image above you can see that this command also displays the recovery model off the listed databases.

I want to focus the database for Citrix Provisioning Services. As you can see in the above image the database ‘ProvisioningServices’ has the ‘simple’ recovery model. I want to change this to the full recovery model and perform a full backup of this database.

Change the recovery model to full

Import-Module sqlps -DisableNameChecking

$instancename = "sql01"
$server = New-Object -TypeName Microsoft.SQLserver.management.Smo.server -ArgumentList $instancename

$databasename = "ProvisioningServices"

$database = $server.Databases[$databasename]

$database.DatabaseOptions.RecoveryModel

$database.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.SMO.Recoverymodel]::Full

$database.Alter()
$database.Refresh()

$database.DatabaseOptions.RecoveryModel

Perform a full backup for the Provisioning Service database.

Import-Module sqlps -DisableNameChecking

$instancename = "sql01"
$server = New-Object -TypeName Microsoft.SQLserver.management.Smo.server -ArgumentList $instancename

$databasename = "ProvisioningServices"
$timestamp = Get-Date -Format yyyyMMddHHmmss

$backupfolder = "C:\backup"
$backupfile = "$($databasename)_full_$($timestamp).bak"
$fullbackupfile = Join-Path $backupfolder $backupfile

Backup-SqlDatabase -ServerInstance $instancename -Database $databasename -BackupFile $fullbackupfile -Checksum -Initialize -BackupSetName "$databasename Full Backup" -CompressionOption On