Powershell script to find SQL Server Instances on a Server

Question: How can I find the SQL Server Instances on a server using Powershell ? Either the local server or remote SQL Servers. The script should be based on reporting the name after the MSSQL$ . For example , if the SQL Server Instance is called MSSQL$MYSERVER1 , than only MYSERVER1 should be returned Answer: You can use this Powershell script as a basis to create a script . Place the code in a powershell script file , and then execute .\Find_SQL_Server_Instances_on_server.ps1 -server MYSERVER1 param ( [Parameter(Mandatory=$true)] [string]$server ) $SERVER=$server function getSQLInstanceOnServer ([string]$SERVER) { $services = Get-Service -Computer $SERVER $services... Read more →

Powershell script function for disk free

Question: I'm currently building a script to return all SQL Servers in the environment . As part of the recordset I need to return the aggregated free space for the drives on the server. So for example , if these were the drive free space details for a specific servers C:\ 52 GB free E:\ 7 GB free F:\ 9 GB free G:\ 5 GB free than the figure to return is 73 GB , I'd like this to be a Powershell function , so then I can reference it for any server, therefore the input parameter should be servername... Read more →

Get Server Total Physical Memory with Powershell

Question: The server team have added a bunch og memory to a server . How can I get the Total Physical Memory of a server using Powershell? The server is a remote server. Answer; A common method to extract the total physical memory of server is using : Get-WMIObject and TotalPhysicalMemory According to the documentation : "The Get-WmiObject cmdlet gets instances of Windows Management Instrumentation (WMI) classes or information about the available WMI classes. " There is an option to query a remote compueter by specifying a name on the -Computername switch. An example of the Powershell code. The result... Read more →

How to start SSMS as Administrator

Sometimes you find yourself in a situation where you are logged onto a server via RDP - and want to start SQL Server Management Studio (SSMS) as another local login. I'm not talking about the actual SQL Server login process - but actually starting SSMS as another login. Why would you want to do this? Let's say you've logged onto a server with a particular login - but that login may not have permissions to start SSMS. Or you may have BUILTIN\Administrators enabled on the SQL Server , therefore you may need to initiate SSMS in the context of a... Read more →

How to troubleshoot Avamar backup issues with avsql command line

It is possible when there is either a SQL Version upgrade or Avamar plug-in changes backup issues are encountered. avsql is the command line interface for Avamar and SQL Server. The command browses SQL Server plug-in data for SQL Server backup and restores . These are the different operation available via the avsql command line interface . avsql --operation={browse | backup | restore} [options] The browse operation reports on client data available for backup. This example returns client data for SQL Server instances on the current host avsql --operation=browse --verbose There are plenty of other browsing options right down to... Read more →