Search This Blog

SQL Server Information

Database created date and sql server restarted time

Running the below query you can see the databases created date and time

select name, crdate from sys.sysdatabases order by crdate

Also you can run the below query to find the sql server last time restarted.

select name, crdate from sys.sysdatabases WHERE name = 'tempdb'

How to find SQL Server information

Run the extended procedure to get sql server internal information
xp_msver

You can use parameter to get specific information
xp_msver 'ProductName', 'ProductVersion', 'Language', 'Platform', 'WindowsVersion', 'PhysicalMemory', 'ProcessorCount'


How do you find SQL Server Backup File Version?

When you have a different backup files form various SQL Servers. How do you find a version of  SQL Server backup ? ( SQL Server 2008, 2005, 2000)

Restore Headeronly from disk='E:\backup\MyDb.bak'

SoftwareVersionMajor
10  for SQL Server 2008
9    for SQL Server 2005
8    for SQL Server 2000

Find Server Information on SQL Server 2008



exec xp_msver "ProductName", "ProductVersion", "Language", "Platform",
"WindowsVersion", "PhysicalMemory", "ProcessorCount"

SQL ServerProperty Info

SELECT SERVERPROPERTY('ServerName')
ASIAPAC06

SELECT SERVERPROPERTY('Machinename')
ASIAPAC06


SELECT SERVERPROPERTY('edition')
Enterprise Evaluation Edition

SELECT SERVERPROPERTY('enginedition')
NULL

SELECT SERVERPROPERTY('Instancename')
NULL

SELECT SERVERPROPERTY('ProductVersion')
10.50.1600.1

SELECT SERVERPROPERTY('ProductLevel')
RTM

SELECT SERVERPROPERTY('ResourceVersion')
10.50.1600

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
2010-04-02 17:38:24.957