Get File Size

USE [msdb]

GO

/****** Object: StoredProcedure [dbo].[dba_GetFileSize] Script Date: 05/08/2012 16:35:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[dba_GetFileSize]

@Filename VARCHAR(100)

AS

DECLARE @hr INT, --the HRESULT returned from

@objFileSystem INT, --the FileSystem object

@objFile INT, --the File object

@ErrorObject INT, --the error object

@ErrorMessage VARCHAR(255),--the potential error message

@Path VARCHAR(100),--

@ShortPath VARCHAR(100),

@Type VARCHAR(100),

@DateCreated datetime,

@DateLastAccessed datetime,

@DateLastModified datetime,

@Attributes INT,

@size INT

SET nocount ON

SELECT @hr=0,@errorMessage='opening the file system object '

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',

@objFileSystem OUT

IF @hr=0 SELECT @errorMessage='accessing the file '''

+@Filename+'''',

@ErrorObject=@objFileSystem

IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem,

'GetFile', @objFile out,@Filename

IF @hr=0

SELECT @errorMessage='getting the attributes of '''

+@Filename+'''',

@ErrorObject=@objFile

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'Path', @path OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'ShortPath', @ShortPath OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'Type', @Type OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'DateCreated', @DateCreated OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'DateLastAccessed', @DateLastAccessed OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'DateLastModified', @DateLastModified OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'Attributes', @Attributes OUT

IF @hr=0 EXEC @hr = sp_OAGetProperty

@objFile, 'size', @size OUT



IF @hr<>0

BEGIN

DECLARE

@Source VARCHAR(255),

@Description VARCHAR(255),

@Helpfile VARCHAR(255),

@HelpID INT



EXECUTE sp_OAGetErrorInfo @errorObject,

@source output,@Description output,

@Helpfile output,@HelpID output

SELECT @ErrorMessage='Error whilst '

+@Errormessage+', '

+@Description

RAISERROR (@ErrorMessage,16,1)

END

EXEC sp_OADestroy @objFileSystem

EXEC sp_OADestroy @objFile

SELECT --[Path]= @Path,

--[ShortPath]= @ShortPath,

--[Type]= @Type,

[DateCreated]= @DateCreated ,

--[DateLastAccessed]= @DateLastAccessed,

--[DateLastModified]= @DateLastModified,

-- [Attributes]= @Attributes,

[size]= @size

RETURN @hr

Backup Report

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROCEDURE [dbo].[dba_sp_Backup_Report]

AS

DECLARE @SQL varchar(max), @InstanceName varchar(max), @RoleDescription varchar(max), @SortOrder int

CREATE TABLE #Temp (ServerName varchar(100) NULL, DatabaseName varchar(500) NULL, LastRunDate date NULL, BackupType varchar(10) NULL,

Mirroring_Role int NULL, RoleDescription varchar(50) NULL, SortOrder int NULL)

DECLARE BackupCur CURSOR FOR

SELECT DISTINCT InstanceName, RoleDescription, SortOrder FROM dba_ServerName WHERE IsBackedUp = 1



OPEN BackupCur

FETCH NEXT FROM BackupCur INTO @InstanceName, @RoleDescription, @SortOrder

WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0

BEGIN

SET @sql = 'SELECT a.*,' + '''' + @RoleDescription + '''' + ', ' + '''' + cast(@SortOrder as varchar) + '''' + ' FROM OPENROWSET(' + '''' + 'SQLNCLI' + '''' + ', '

+ '''' + 'Server=' + @InstanceName + ';Trusted_Connection=yes;' + ''''

+' ,'

+ '''' + ' select @@servername as Server,db.name as Name, max(bs.backup_finish_date) as Date, Type, mirror.Mirroring_Role

from sys.databases db --master..sysdatabases db

left outer join msdb..backupset bs on db.name = bs.database_name

left outer join sys.database_mirroring mirror on db.database_id = mirror.database_id

where db.is_in_standby = 0 and db.is_read_only = 0 and db.state = 0 --and db.state <> 6

--and not exists (select * from sys.database_mirroring mirror where db.database_id = mirror.database_id and mirror.mirroring_role = 2)

group by db.name, Type, mirror.Mirroring_Role ' + ''''

+ ') AS a;'

PRINT @sql

INSERT INTO #Temp

EXEC(@sql)



FETCH NEXT FROM BackupCur INTO @InstanceName, @RoleDescription, @SortOrder

END

CLOSE BackupCur

DEALLOCATE BackupCur

DELETE FROM #Temp WHERE Mirroring_Role = 2

DELETE FROM #Temp WHERE RoleDescription = 'DR' and DatabaseName IN ('master', 'msdb', 'model', 'tempdb')

SELECT * FROM #Temp WHERE (BackupType = 'D' OR BackupType IS NULL) AND DatabaseName <> 'tempdb'

-- Send Email

DECLARE @HTML varchar(max), @HTML1 varchar(max), @HTML2 varchar(max), @subj varchar(500), @Descr1 varchar(500), @Descr2 varchar(500)

if exists

(

select * from #Temp WHERE (BackupType = 'D' OR BackupType IS NULL) AND DatabaseName <> 'tempdb' and (LastRunDate < CAST(getdate() as DATE) OR LastRunDate IS NULL)

)

begin

set @subj='Backup Report for ' + convert(varchar, getdate(), 1) + ':Bad'

set @Descr1 = (

SELECT CAST(count(*) as varchar) from #Temp WHERE (BackupType = 'D' OR BackupType IS NULL) AND DatabaseName <> 'tempdb' and (LastRunDate < CAST(getdate() as DATE) OR LastRunDate IS NULL)

) + ' databases not backed up'

set @Descr2 = (

SELECT CAST(count(*) as varchar) from #Temp WHERE BackupType = 'D' AND DatabaseName <> 'tempdb' and LastRunDate >= CAST(getdate() as DATE)

) + ' databases have been backed up'

end

else

begin

set @subj='Backup Report for ' + convert(varchar, getdate(), 1) + ':Good'

set @Descr2 = (

SELECT CAST(count(*) as varchar) from #Temp WHERE BackupType = 'D' AND DatabaseName <> 'tempdb' and LastRunDate >= CAST(getdate() as DATE)

) + ' databases have been backed up'

end

SET @HTML1 =

N'<html><head><style>

body{color: white ;font: 8pt Arial;}

h5{color: navy }

table{font: 8pt "Arial";color: margin: 1em auto;border-collapse: collapse;white; width: 50%}

th{color: #ffffff; background-color:#808080}

tr{color: navy; background-color:#ffffff}

#celSlide{letter-spacing: 10;color: white;}

</style></head>' +

--N'<H5><FONT COLOR=Red>Failed job report for ' + convert(varchar, getdate(), 1) + '</FONT></H4>' +

N'<H5><FONT COLOR=Red>' + @Descr1 + '</FONT></H4>' +

N'<table border="1">' +

N'<th>Server</th><th>Role</th><th>Database</th><th>LastRunDate</th>' +

CAST ( ( SELECT td = ISNULL(ServerName, ''), '',

td = ISNULL(RoleDescription, ''), '',

td = ISNULL(DatabaseName, ''), '',

td = ISNULL(LastRunDate, '')

FROM #Temp WHERE (BackupType = 'D' OR BackupType IS NULL) AND DatabaseName <> 'tempdb'

and (LastRunDate < CAST(getdate() as DATE) OR LastRunDate IS NULL)

ORDER BY SortOrder, ServerName, DatabaseName FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;



SET @HTML2 =

N'<html><head><style>

body{color: white ;font: 8pt Arial;}

h5{color: navy }

table{font: 8pt "Arial";color: margin: 1em auto;border-collapse: collapse;white; width: 50%}

th{color: #ffffff; background-color:#808080}

tr{color: navy; background-color:#ffffff}

#celSlide{letter-spacing: 10;color: white;}

</style></head>' +

--N'<H5><FONT COLOR=Red>Failed job report for ' + convert(varchar, getdate(), 1) + '</FONT></H4>' +

N'<H5><FONT COLOR=Red>' + @Descr2 + '</FONT></H4>' +

N'<table border="1">' +

N'<th>Server</th><th>Role</th><th>Database</th><th>LastRunDate</th>' +

CAST ( ( SELECT td = ISNULL(ServerName, ''), '',

td = ISNULL(RoleDescription, ''), '',

td = ISNULL(DatabaseName, ''), '',

td = ISNULL(LastRunDate, '')

FROM #Temp WHERE BackupType = 'D' AND DatabaseName <> 'tempdb'

and LastRunDate >= CAST(getdate() as DATE)

ORDER BY SortOrder, ServerName, DatabaseName FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

if exists (

select * from #Temp WHERE (BackupType = 'D' OR BackupType IS NULL) AND DatabaseName <> 'tempdb' and (LastRunDate < CAST(getdate() as DATE) OR LastRunDate IS NULL)

)

begin

SET @HTML = ISNULL(@HTML1, '') + ISNULL(@HTML2, '')

end

else

begin

SET @HTML = @HTML2

end



exec msdb.dbo.sp_send_dbmail @profile_name='EmailProfileName',

@recipients='EmailID1, EmailID2, EmailID3;',

@subject = @subj,

@body = @html,

@body_format = 'HTML'



DROP TABLE #Temp