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.

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.

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