SQL Server Sysadmin Audit Report Powershell script

Question: I need a powershell script which reports on SQL Server logins with Sysadmin privileges. My aim is to automate the process - so if you could provide a script which provides the sql server name , login and that I can schedule. Answer: This powershell script will execute across a list of servers - as defined in the file associated with the $instancepath variable.Create a text file and place 1 or more sql server instances in the file e.g server1\instance1 server2\instance1 $isodate=Get-Date -format s $isodate=$isodate -replace(":","") $basepath=(Get-Location -PSProvider FileSystem).ProviderPath $instancepath=$basepath + "\config\instances_all.txt" $outputfile="\logs\sql_servers_sysadmin_report" + $isodate + ".html" $outputfilefull =... Read more →

Does SQL Server install Visual Studio components

Question: I was looking through Programs installed on a server with SQL Server installed and noticed Visual Studio components installed. As part of a SQL Server installation , which parts of the SQL Server install install the Visual Studio components? Answer: Since the 2016 version , SSMS is now based on the Visual Studio 2015 Shell. You could say SSMS is a Visual Studio Extension. The main reasoning , is the removal of dependency on installing .Net Framework 3.5 and some apparent performance improvements. Note:SSMS is now a standalone installation Useful reading on SSMS How to start SSMS as Administrator... Read more →

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 →