While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.
This script will return all SQL instances running on a server or list of servers. cls Import-Module -Name SQLPS -DisableNameChecking $servers = “SERVER_NAME”, “SERVER_NAME_2”, “ETC”; foreach($server in $servers) { $path = “SQLServer:\SQL\” + $server; $instances = Get-ChildItem -Path $path; $instances; }
This was made possible by the Scripting Guy. I have boiled it down to the answer. If you want to use PowerShell to query for the log on account that normally is displayed in the services.msc service properties window: Here is the PowerShell script: $server_name = "name_of_server"; $services...
Query to get all currently running jobs with their run duration in seconds. Source: http://www.sanssql.com/2013/08/t-sql-query-to-find-currently-running.html SELECT [J].[name] AS [Running_Jobs] , [JA].[Start_execution_date] AS [Starting_time] , DATEDIFF(ss, [JA].[Start_execution_date], GETDATE()) AS [Has_been_running(in Sec)] FROM [msdb].[dbo].[sysjobactivity] [JA] JOIN [msdb].[dbo].[sysjobs] [J] ON [J].[job_id] = [JA].[job_id] WHERE [job_history_id] IS NULL AND [start_execution_date] IS NOT NULL ORDER...
This is a bit of a scary task. Someone tells you about 87 databases that all need to be restored from the most recent FULL backups. You can either spend the rest of your day pointing and clicking, or you can use some PowerShell power to crank out the script...
Maybe this is a one-off type of thing, but if you need to see every folder within a directory and get the list of who can access that folder, it can be done using PowerShell. The output is tab-separated and can easily paste into an Excel sheet. cls cd \\DIRECTORY_NAME\CHANGE_THIS...
If you've painted yourself into the single_user corner, here is how you can get out.
Get some clustered facts about your server.
The Deep Secrets of My Computing Environment!
In my computing environment, I have a set of tools that I rely on because they just make life easier. Here they are in order of (perceived) actual usage.
Getting all services running SQL Server using T-SQL: SELECT * FROM sys.dm_server_services