How to: Find Last Restart of SQL Server

Method-1: Checking SQL Server Error Log
First time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

Method-2: Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Note: Method-2 was removed by Microsoft due to internal formatting issue. Internally the SQL start time is stored as tick count from machine boot (displayed in the sqlserver_start_time_ms_ticks column). During the conversion from tick count to a SQL Datetime the precision of the conversion is rounded off at 1 second rather than 1 ms. According to Micrsoft, this issue will be addressed in a future release of SQL Server. (Source: http://connect.microsoft.com/SQLServer/feedback/details/367663/sqlserver-start-time-not-persistent)

Method-3: Start time of the Default Trace
The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

Method-4: Creating date of tempdb
The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.
SELECT create_date FROM sys.databases
WHERE name = ‘tempdb’