Figure out recovery mode for each database

,

SELECT name AS [Database Name],CONVERT(varchar(10),create_date,111) as [Create Date],

recovery_model_desc AS [Recovery Model]

FROM sys.databases

order by [Create Date] desc,[Recovery Model] desc

GO

-- Run following to set a database's recovery mode to "Full":

Use Master;

go

ALTER DATABASE [put database name here] SET RECOVERY FULL

GO

SELECT name AS [Database Name],CONVERT(varchar(10),create_date,111) as [Create Date],
recovery_model_desc AS [Recovery Model]
FROM sys.databases

order by [Create Date] desc,[Recovery Model] desc
GO

note: list all database recovery mode and created date with newest one listing on top. You can see if a database that has recovery mode as simple caused your transaction log backup to fail.

Run following to set a database's recovery mode to "Full":

Use Master;
go

ALTER DATABASE [put database name here] SET RECOVERY FULL
GO

Rate

3 (2)

Share

Share

Rate

3 (2)