Last SQL Server Instance Start Date

I was interested in finding when a new SQL Server instance I started working on was last restarted and came across this blog post.

Although the information was very good I felt the queries could be tweaked slightly and broken out into the variations needed for the different SQL Server editions.

I find that it is helpful to know when the database server was started due to the various Dynamic Management Views and Functions (DMV) that I use. Many of these collate cumulative statistics beginning at a SQL Server start. Which means that they are cleared down when the instance is restarted, so it's helpful to know the uptime of the server in order to know how much data has been collected and whether it is representative of how the server is being used: i.e. one day's worth of statistics vs. one month's.

As the blog post mentioned above outlines, there are several ways of getting to this data and some of them work only in certain versions of SQL Server. Here they are in my modified form:
NB: I am ignoring SQL Server 2000

Method 1 - Check TempDB create date
SQL Server 2005+ 

SELECT [crdate] AS [LastStartupDate],

DATEDIFF(dd,[crdate],GETDATE()) AS [Days since server start]

FROM   [dbo].[sysdatabases]

WHERE  [name] = 'tempdb'

AND [crdate] < GETDATE() - 0 --filter results to instances restarted greater than X days

Method 2 - Check the system DMV
SQL Server 2008+

SELECT [sqlserver_start_time] AS [LastStartupDate],

DATEDIFF(dd, [sqlserver_start_time],GETDATE()) AS [Days since server start]

FROM   [sys].[dm_os_sys_info]

Method 3 - Interrogate the SQL Server error log
SQL Server 2005 - 2008

DECLARE @LogNum TINYINT

SET @LogNum = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT

SET @LogType = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64)

SET @ProcessType = 'Server'

DECLARE @TextSearch VARCHAR(20)

SET @TextSearch = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType , @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days

SQL Server 2008 - 2012
In SQL Server 2012 the extended stored procedure accepts only two parameters 

DECLARE @LogNum TINYINT = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64) = 'Server'

DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType --, @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE [ProcessInfo] = @ProcessType AND [TEXT] LIKE '%' + @TextSearch + '%'

AND LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days