SQL Server

Check

PowerShell

Get-Service -DisplayName *SQL*

Default Instance

Named Instance

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

Engine

$DfltInstance = $Wmi.Services['MSSQLSERVER']

$DfltInstance.Refresh();

$DfltInstance

$DfltInstance = $Wmi.Services['MSSQL$instancename']

$DfltInstance.Refresh();

$DfltInstance

SQL Server Agent

$DfltInstance = $Wmi.Services['SQLSERVERAGENT']

$DfltInstance.Refresh();

$DfltInstance

$DfltInstance = $Wmi.Services['SQLAGENT$instancename']

$DfltInstance.Refresh();

$DfltInstance

cmd

Default Instance

Named Instance

Engine

SQL Server Agent

sc query "MSSQLSERVER"

sc query "SQLSERVERAGENT"

sc query "MSSQL$INSTANCENAME"

sc query "SQLAgent$INSTANCENAME"

NOTE: WMIC is deprecated as of Windows 10, version 21H1

wmic service where (Name like '%sql%') get caption, name, startmode, state, PathName

Startup

Default Instance

Named Instance

Engine

- single-user mode

- min config mode

net start MSSQLSERVER

net start MSSQLSERVER /m

net start MSSQLSERVER /f

net start MSSQL$instancename

net start MSSQL$instancename /m

net start MSSQL$instancename /f

SQL Server Agent

net start SQLSERVERAGENT

net start SQLAgent$instancename

Engine

- single-user mode

- min config mode

net start "SQL Server (MSSQLSERVER)"

net start "SQL Server (MSSQLSERVER)" /m 

net start "SQL Server (MSSQLSERVER)" /f

net start "SQL Server (instancename)"

net start "SQL Server (instancename)" /m

net start "SQL Server (instancename)" /f

SQL Server Agent

net start "SQL Server Agent (MSSQLSERVER)"

net start "SQL Server Agent (instancename)"

Default Instance

Named Instance

PowerShell

Start-Service -NAME MSSQLSERVER

Start-Service -Name MSSQL$instancename

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

Engine

$DfltInstance = $Wmi.Services['MSSQLSERVER']

$DfltInstance.Start();

$DfltInstance = $Wmi.Services['MSSQL$instancename']

$DfltInstance.Start();

SQL Server Agent

$DfltInstance = $Wmi.Services['SQLSERVERAGENT']

$DfltInstance.Start();

$DfltInstance = $Wmi.Services['SQLAGENT$instancename']

$DfltInstance.Start();

cmd

Default Instance

Named Instance

Engine

SQL Server Agent

sc start "MSSQLSERVER"

sc start "SQLSERVERAGENT"

sc start "MSSQL$INSTANCENAME"

sc start "SQLAgent$INSTANCENAME"

Shutdown

Default Instance

Named Instance

Engine

SQL Server Agent

net stop MSSQLSERVER

net stop SQLSERVERAGENT

net stop MSSQL$instancename

net stop SQLAgent$instancename

Engine

SQL Server Agent

net stop "SQL Server (MSSQLSERVER)"

net stop "SQL Server Agent (MSSQLSERVER)"

net stop "SQL Server (instancename)"

net stop "SQL Server Agent (instancename)"

Default Instance

Named Instance

PowerShell

Stop-Service -NAME MSSQLSERVER

Stop-Service -Name MSSQL$instancename

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

sqlps

cd SQLSERVER:\SQL\computername

$Wmi = (get-item .).ManagedComputer

Engine

$DfltInstance = $Wmi.Services['MSSQLSERVER']

$DfltInstance.Start();

$DfltInstance = $Wmi.Services['MSSQL$instancename']

$DfltInstance.Start();

SQL Server Agent

$DfltInstance = $Wmi.Services['SQLSERVERAGENT']

$DfltInstance.Stop();

$DfltInstance = $Wmi.Services['SQLAGENT$instancename']

$DfltInstance.Stop();

cmd

Default Instance

Named Instance

Engine

SQL Server Agent

sc stop "MSSQLSERVER"

sc stop "SQLSERVERAGENT"

sc stop "MSSQL$INSTANCENAME"

sc stop "SQLAgent$INSTANCENAME"

Restart

PowerShell

Restart-Service -NAME MSSQLSERVER

Restart-Service -Name MSSQL$instancename

Version

SSMS

SELECT @@version, @@servicename

go

xp_msver

go

SELECT 

    SERVERPROPERTY('MachineName'), 

    SERVERPROPERTY('productversion') pv, 

    SERVERPROPERTY('productlevel'),

    SERVERPROPERTY('productupdatelevel'),

    SERVERPROPERTY('edition')

go

if @@trancount > 0 rollback

Powershell

Get-WmiObject -Class Win32_Product -Filter "Name LIKE 'SQL Server % Database Engine Services'" | Select-Object -Property Name,Vendor,Version,Caption | Get-Unique

If you have multiple instances...

$instances = @('SQL01','SQL02','SQL03','SQL04')

$instances | ForEach-Object {Get-Item "SQLSERVER:\SQL\$_\DEFAULT"} | Select-Object Name,VersionString

Using the registry...

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

foreach ($i in $inst)

{

   $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i

   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition

   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version

}

Query the Registry

REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /s /v /e /f Version

Edition

Query the Registry

REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /s /v /e /f Edition

SQL Server Configuration Manager

SQL Server Services - Right-click on SQL Server - Select Properties - Advanced tab...

Database Size

SELECT name AS DBName,

       filename,

       CASE groupid WHEN 1

                    THEN 'Data'

                    ELSE 'Log'

       END AS type,

       (size*8)/(1024.) AS SizeInMBs

  FROM master..sysaltfiles

alternatively...

exec sp_databases

Instance Size

To give the overall size of the database (all databases and transaction logs)...

SELECT SUM((SIZE*8)*1024.) AS SizeInBytes

  FROM master..sysaltfiles

Database Creation Date

SELECT name,

       create_date

  FROM sys.databases

Instance Creation Date

SELECT createdate AS 'SQL Server Instance Creation Date'

  FROM sys.syslogins 

 WHERE sid = 0x010100000000000512000000

    OR sid = 0x01020000000000052000000021020000

Port

SELECT DISTINCT local_net_address, local_tcp_port

  FROM sys.dm_exec_connections

 WHERE local_net_address IS NOT NULL

Instance Startup Time

SELECT sqlserver_start_time

  FROM sys.dm_os_sys_info

Bibliography

https://sqlserverbuilds.blogspot.com/ https://github.com/ktaranov/sqlserver-kit
Start/Stop/Statushttps://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-serviceshttps://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-modehttps://learn.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-optionshttps://learn.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-configure-server-startup-optionshttps://serverfault.com/questions/754766/show-status-of-a-windows-service-from-the-command-prompt
Versionhttps://support.microsoft.com/en-gb/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an#bookmark-howtodetermine https://devblogs.microsoft.com/scripting/powertip-use-powershell-to-find-versions-of-sql-server/https://stackoverflow.com/questions/7587077/how-do-i-check-for-the-sql-server-version-using-powershellhttps://shoestringdba.com/2019/09/02/using-powershell-to-check-sql-server-version-information/https://www.mssqltips.com/sqlservertip/5408/check-current-patch-levels-for-all-sql-servers-in-environment/https://dba.stackexchange.com/questions/67985/how-do-you-find-sql-server-version-edition-without-ssms-installed
Porthttps://stackoverflow.com/questions/19451490/how-to-identify-port-number-of-sql-serverhttps://dbamohsin.wordpress.com/2011/02/08/quickest-way-to-find-a-sql-server-instance-port-number/
Uptimehttps://www.mssqltips.com/sqlservertip/2501/find-last-restart-of-sql-server/
Namehttps://stackoverflow.com/questions/18070177/how-to-get-current-instance-name-from-t-sql
Sizehttps://social.msdn.microsoft.com/Forums/sqlserver/en-US/21db3620-f0bf-4b3d-a76b-52e5b08dc9b9/how-to-find-the-all-databases-sizes-in-sql-server-2000-?forum=transactsqlhttp://www.kodyaz.com/articles/list-database-size-using-sql-server-sp_msforeachdb.aspxhttps://stackoverflow.com/questions/176379/determine-sql-server-database-sizehttps://searchwindevelopment.techtarget.com/tip/How-to-find-the-size-of-every-database-in-SQL-Server-2000https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysaltfiles-transact-sql?view=sql-server-ver15
Otherhttp://www.4guysfromrolla.com/webtech/032906-1.shtmlhttp://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/https://sqldbpool.com/2013/08/27/how-to-find-out-the-sql-server-installation-date/https://www.sqlservercentral.com/blogs/querying-sql-and-windows-version-info-with-t-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 (sys.databases)https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-databases-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 (sp_databases)
https://www.brentozar.com/archive/2020/08/sql-constantcare-population-report-summer-2020/
Microsoft Customer Experience Improvement Program (CEIP)https://www.brentozar.com/archive/2020/10/what-is-the-sql-server-ceip-service/