{"id":132,"date":"2015-08-26T14:46:13","date_gmt":"2015-08-26T14:46:13","guid":{"rendered":"http:\/\/robinplomp.com\/?p=132"},"modified":"2015-08-26T14:47:55","modified_gmt":"2015-08-26T14:47:55","slug":"remote-manage-a-sql-environment-with-powershell","status":"publish","type":"post","link":"https:\/\/robinplomp.com\/?p=132","title":{"rendered":"Remote manage a SQL environment with PowerShell"},"content":{"rendered":"

\"download\"<\/a>In this article I want to talk about managing a SQL 2012 Server with PowerShell from a remote computer. As I\u2019ve mentioned in an earlier blog post PowerShell is becoming more and more important in the day to day task of a system administrator. <\/p>\n

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. <\/p>\n

 <\/p>\n

$fldr = \"C:\\users\\robin\\Documents\\PowerShell\\data\"\n$credential = Get-Credential\n \n$credential.Password | ConvertFrom-SecureString | Set-Content \"$fldr\\adminpassword.bin\"\n \n$password = Get-Content \"$fldr\\adminpassword.bin\" | ConvertTo-SecureString\n \n$cred = New-Object System.Management.Automation.PSCredential(\"contoso\\adminrobin\", $password)\n \nNew-PSSession -Name SQL -ComputerName sql01.contoso.com -Credential $cred\n \nEnter-PSSession -Name SQL<\/pre>\n
<\/p>\n
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: <\/p>\n
winrm s winrm\/config\/client \u2018@{TrustedHosts=\u201dsql01.contoso.com\u201d}\u2019<\/a><\/strong> <\/p>\n
  <\/p>\n
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. <\/p>\n
Import-Module sqlps -DisableNameChecking\n \nSet-Location SQLSERVER:\\SQL\\localhost\\default\\databases\n \nGet-childitem<\/pre>\n
In the code sample above I retrieve a list of the available SQL databases on this server. <\/p>\n
\"image\"<\/a> <\/p>\n
In the output you see in the image above you can see that this command also displays the recovery model off the listed databases. <\/p>\n
I want to focus the database for Citrix Provisioning Services. As you can see in the above image the database \u2018ProvisioningServices\u2019 has the \u2018simple\u2019 recovery model. I want to change this to the full recovery model and perform a full backup of this database. <\/p>\n
Change the recovery model to full <\/p>\n
Import-Module sqlps -DisableNameChecking\n \n$instancename = \"sql01\"\n$server = New-Object -TypeName Microsoft.SQLserver.management.Smo.server -ArgumentList $instancename\n \n$databasename = \"ProvisioningServices\"\n \n$database = $server.Databases[$databasename]\n \n$database.DatabaseOptions.RecoveryModel\n \n$database.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.SMO.Recoverymodel]::Full\n \n$database.Alter()\n$database.Refresh()\n \n$database.DatabaseOptions.RecoveryModel<\/pre>\n
  Perform a full backup for the Provisioning Service database. <\/p>\n
Import-Module sqlps -DisableNameChecking\n \n$instancename = \"sql01\"\n$server = New-Object -TypeName Microsoft.SQLserver.management.Smo.server -ArgumentList $instancename\n \n$databasename = \"ProvisioningServices\"\n$timestamp = Get-Date -Format yyyyMMddHHmmss\n \n$backupfolder = \"C:\\backup\"\n$backupfile = \"$($databasename)_full_$($timestamp).bak\"\n$fullbackupfile = Join-Path $backupfolder $backupfile\n \nBackup-SqlDatabase -ServerInstance $instancename -Database $databasename -BackupFile $fullbackupfile -Checksum -Initialize -BackupSetName \"$databasename Full Backup\" -CompressionOption On<\/pre>\n","protected":false},"excerpt":{"rendered":"
In this article I want to talk about managing a SQL 2012 Server with PowerShell from a remote computer. As I\u2019ve 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 … Lees verder Remote manage a SQL environment with PowerShell<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,18],"tags":[11,4],"class_list":["post-132","post","type-post","status-publish","format-standard","hentry","category-powershell","category-sql","tag-powershell","tag-sql-2012"],"_links":{"self":[{"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/posts\/132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/robinplomp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=132"}],"version-history":[{"count":3,"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/posts\/132\/revisions"}],"predecessor-version":[{"id":135,"href":"https:\/\/robinplomp.com\/index.php?rest_route=\/wp\/v2\/posts\/132\/revisions\/135"}],"wp:attachment":[{"href":"https:\/\/robinplomp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/robinplomp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/robinplomp.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}