Currently Browsing: Powershell

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server
Some user complained that they cannot use my PowerShell script published on TechNet https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 It is a PowerShell script to Backup all user databases to Azure Storage. They experienced the following error: 8/14/2018 11:18 AM – Setting Folders PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.” new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is...
read more

Powershell / Azure Automation / Delete older Containers from Azure Storage Account

Powershell / Azure Automation / Delete older Containers from Azure Storage Account
This will be a simple example of how you can delete older containers in the Azure storage account. I will use Azure automation to scan storage containers and delete those matching the certain date pattern. First, you need to choose an existing one or create new automation account to run your Runbooks   You should put the script within your Runbooks under the process automation. You will have some useful tutorial Runbooks as well.   We will use connection name with information needed to authenticate with Azure so that we can manage Azure classic resources from Runbooks. You can create your...
read more

PowerShell / SQL Server / Check SQL Server current Update Status and send Email Report

PowerShell / SQL Server / Check SQL Server current Update Status and send Email Report
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-check-05ca591f Summary: Check SQL Server Version and the current patch level for all servers you specified. As well check latest patches/updates available for installed SQL Server version and send email with results. Description: SQL Server Instance Update Status PowerShell script which can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding servers names. Script is checking server registry...
read more

Powershell / Use PowerShell to Backup all user databases to Azure Storage

Powershell / Use PowerShell to Backup all user databases to Azure Storage
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 Summary: Backup all user database from specified SQL Server instance to the buffer location, and then copy from the buffer location to the azure storage, and create log file with results. Description: This PowerShell script can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding parameters – instance name, backup destination path, excluded databases list, azure storage...
read more

Powershell / Providers / Get-ChildItem : Invalid Path

Powershell / Providers / Get-ChildItem : Invalid Path
Using UNC paths when working with PowerShell providers. There are errors you can encounter using UNC paths if your current provider is different then filesystem. PowerShell will just use the current one. We can use the Get-PSProvider cmdlet to get full list of the PSProviders available on our system. My current one is filesystem.   So lets test this. I will use one simple PowerShell script to delete all sub-folders and files from network folder. $FolderNetworkPath="\\10.0.0.102\test\" Write-Output ((Get-Date -Format g) + " - Delete Files and Subfolders From Network Folder") Get-ChildItem -Path...
read more

Powershell / Credentials / Connect to SQL Server with PowerShell

Powershell / Credentials / Connect to SQL Server with PowerShell
How to login using SQL Server Management Object (SMO)   For windows authentication #################################################################### ####################### Windows Authentication ##################### #################################################################### [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "InstanceName" $s.ConnectionContext.LoginSecure=$true $s.Databases | select name, size, status ####################################################################  ...
read more

Powershell / Backup SQL Server DBs to Azure Storage with Powershell script

Powershell / Backup SQL Server DBs to Azure Storage with Powershell script
With this simple script, you can backup all databases to Azure daily, you can hardcode the parameters values or you can use task manager to pass them to the PS script. You need to have Azure PowerShell modules installed on your machine (how to). First off all, let’s try just to connect to your Azure Storage Account You can use azure credential-publishsettings file to do that, or putting the variables with access key, subscription and storage account name as in this example: #Variables param ( [string]$AzureKey= "AzureStorageAccessKey", [string]$AzureSubs= "YourSubscription", [string]$AzureStor=...
read more