DB Status Check

DB Status Check:

set nocount ON
select getdate()
declare @name VARCHAR(50),@read varchar(1500),@standby varchar(1500),@dbname1 nvarchar(1500),@state nvarchar(1500),@autoclose nvarchar(1500)
--SET @name=db_name()

DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases --where name =isNULL(@name,name)
WHERE (is_read_only>0 OR is_in_standby>0 OR state>0 or is_auto_close_on > 0) and name=isNULL(@name,name) order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
select @dbname1=name, @read=is_read_only,@standby=is_in_standby,@state=state,@autoclose=is_auto_close_on
  from sys.databases
  where name=@name
   --select @dbname1 +' '+@read+', '+ @close
   print '--- Start of Databse '+@dbname1
 if @standby>0
set @standby='--It is in stand by  '
ELSE
SET @standby=''
if @read>0
set @read='--It is read only, Run below query to bring back the database to normal:

ALTER DATABASE ['+@dbname1+'] SET  READ_WRITE
 
  '

ELSE
SET @read=''
IF @state>0
SET @state='--Database Is not online'
ELSE
set @state=''
IF @autoclose>0
SET @autoclose='--Database Is Auto Close, Run below query to set Auto Close Off:

ALTER DATABASE ['+@dbname1+'] SET AUTO_CLOSE OFF

'

ELSE
set @autoclose=''
print @read+ @standby + @state + @autoclose

FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor
DEALLOCATE db_cursor

Databases which are not backed up for more than 24hours

Finding the databases which are not backed up for more than 24hours:

 set nocount on
create table #backupcheck (columnName varchar(500),backuptype varchar(50))
insert into #backupcheck SELECT
    CASE
        WHEN ((datediff(day,MAX(msdb.dbo.backupset.backup_finish_date),getdate()) )>1 )
                    THEN msdb.dbo.backupset.database_name + ' backup is not completed last day and last backup date is '+CONVERT(VARCHAR(100), MAX(msdb.dbo.backupset.backup_finish_date))+''
          END AS columnName,
        CASE  when  msdb.dbo.backupset.type='I'   then 'incremental backup'
        else 'full backup'
        END as backuptype
            FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type in ('D','I')
GROUP BY
   msdb.dbo.backupset.database_name ,msdb..backupset.type
ORDER BY 
   msdb.dbo.backupset.database_name

      select columnName+' and backup type is '+backuptype from #backupcheck where columnName is NOT NULL
      drop table #backupcheck