SQL Server :: When was a Database taken Offline

Who changed SQL Server database state to OFFLINE or When was my database last taken Offline?

Here is a T-SQL script which tells when and who took the database offline or online recently.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.

DECLARE  @DBNAME nvarchar(100)

  ,@FileName nvarchar(max)

  ,@spid int

  ,@LogDate Datetime

  ,@Status nvarchar(10)

  

SET @DBNAME = 'AdventureWorks2008R2' -- Change DB Name

SET @Status = 'OFFLINE' --[OFFLINE or ONLINE]

SELECT @FileName=[path] FROM sys.traces WHERE is_default=1

DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))

INSERT INTO @ErrorLogTable

EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'

SELECT TOP 1 @spid=cast(SUBSTRING(ProcessInfo,5,5) AS int)

   ,@LogDate=cast(Logdate AS nvarchar)

FROM @ErrorLogTable

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime

FROM sys.fn_trace_gettable( @FileName, DEFAULT )

WHERE spid=@spid and DatabaseName=@DBNAME and CAST(StartTime AS nvarchar)=@LogDate

If you didn't get the result by running above code it means, trace is reset after the database went offline. 

In this case go run following code and get the path.

select path FROM sys.traces WHERE is_default=1

Change file name and run following code. Last row will have the timestamp. 

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime

FROM sys.fn_trace_gettable( 'H:\sqlsysdb\MSSQL10\MSSQL\Log\log_228.trc', DEFAULT )

WHERE DatabaseName='TestDB'

Second option is to check SQL Server error log and you'll get something similar for your database. 

Hope it will be helpful.