Database last restore status report

use master
go
drop procedure sp_db_restore_status_report
go

create procedure sp_db_restore_status_report
as
begin

/*
select *
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'
order by a.name

SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
    drop table #tmp
   
Create table #tmp (db nvarchar(1000), restore_Date datetime)

insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'

--select * From #tmp

if(OBJECT_ID('tempdb..#tmp_total')>1)
    drop table #tmp_total
   
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
)  as restored_older_24_hours
into #tmp_total

--select * From #tmp_total

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
    N'

Database Restore Report from '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N'' +
    N' ' +
    CAST ( ( SELECT td = total_dbs,       '',
                    td = restored_last_2_hours,       '',
                    td = restored_last_8_hours, '',
                    td = restored_last_24_hours, '',
                    td = restored_older_24_hours
              FROM #tmp_total
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Total Databases	Restored in last 2 hours	Restored in last 8 hours	Restored in last 24 hours	Restored over 24 hours

' ;
   
print @tableHTML
   
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @subject = @subjectstring;
   
end   

sp_delete_files

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_delete_files]    Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
    ,@ext  = 'trc'
    ,@minutesgoback  = -60
*/

ALTER proc [dbo].[sp_delete_files]
(
    @folder varchar(100)
    ,@ext varchar(10)
    ,@minutesgoback int = -6000
)
as
begin

    set nocount on;
    declare @run int = 1 , @reccount int = 0
    declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)

    --set @folderlogs = @folder +'\logs\'
    set @folderlogs = @folder
   

    ------------------------------------------------

    set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
    --print @sql

    if(OBJECT_ID('tempdb..#tmp_tbl')>0)
        drop table #tmp_tbl
       
    CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))

    INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql

    DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
    --select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
    --From #tmp_tbl
    --where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

    ---------------------------------------------------


    declare tmp_cur cursor for
    SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

    open tmp_cur
    Fetch next from tmp_cur into @filename

    while @@fetch_status = 0
    begin
    print @filename
    set @tablename = REPLACE(@filename,'.'+@ext,'')
    print 'deleteing '+@tablename


    set @filename = ltrim(RTRIM(@filename)) 

    set @sql = 'del '+ @folder+ @filename
    if @run = 0
        print @sql 
    if @run = 1
        EXECUTE master.dbo.xp_cmdshell @sql
     
     


     
    Fetch next from tmp_cur into @filename
    print '---'
    end

    close tmp_cur
    deallocate tmp_cur

    ------------------------------------------------------
 


end