October 27, 2015 at 12:24 pm
Hi All,
We are in the process of creating inventory, for that I need a query to capture the below information
HostName
InstanceName
Version
Edition
SQL Service Account
Region
Zone
Server Managed By
Storage Managed By
Backup Managed By
Type of Backup
Mode of Backup
Application Name
Is Built By DBA Team
Is Clustered
Having Access
Connecting Mode
October 27, 2015 at 12:31 pm
New persopn (10/27/2015)
Hi All,We are in the process of creating inventory, for that I need a query to capture the below information
HostName
InstanceName
Version
Edition
SQL Service Account
RegionZone
Server Managed By
Storage Managed By
Backup Managed By
Type of Backup
Mode of Backup
Application Name
Is Built By DBA Team
Is Clustered
Having Access
Connecting Mode
some of those values are not actually fields or propertie s in SQL,so you cannot query them, or are rendered on a per-connection basis,but here's my default set that i pull that has most of what you asked for:
Select @@version,
SERVERPROPERTY('IsClustered') As IsClustered,
Serverproperty('BuildClrVersion') AS BuildClrVersion,
Serverproperty('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
Serverproperty('Edition') AS Edition,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END As SQLVersion,
Serverproperty('productversion') As ProductVersion,
Serverproperty('EditionID') AS EditionID,
Serverproperty('EngineEdition') AS EngineEdition,
Serverproperty('MachineName') AS MachineName,
Serverproperty('ProductLevel') AS ProductLevel,
Serverproperty('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
Serverproperty('ResourceVersion') AS ResourceVersion,
Serverproperty('ServerName') AS ServerName,
Serverproperty('InstanceName') AS InstanceName
SELECT servicename, service_account
FROM sys.dm_server_services
Lowell
October 27, 2015 at 12:36 pm
Glenn Berry maintains a lot of system information type DMV queries on his blog site. You might find something there to fill in some of the missing items.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2015 at 1:05 pm
Thanks for your information.
Is there any query to capture the information like Type of Backup, Mode of Backup information in SQL server
October 27, 2015 at 1:15 pm
New persopn (10/27/2015)
Thanks for your information.Is there any query to capture the information like Type of Backup, Mode of Backup information in SQL server
Backups occur on a per database level, and not at the server level, so the scope/assumption is incorrect.
select dbz.name,dbz.recovery_model_desc from sys.databases dbz shows you which db's are in FULL/SIMPLE/ recovery mode, but whether you have an actual backup of those db's, and what type the last ones were require querying the msdb database for last backups.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply