0

By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by Aaron Bertrand.

Here is my actual command which gets recent LOG backups for each database in the server:

EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command = 
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg'' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = ''L''
WHERE sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

Now, the problem is I want to send all these query results into email in CSV file but I am not able to paste all these results. I tried creating a new table and storing all query results there, unfortunately I got an error. I need an alternative solution.

I would be glad to hear your ideas.

2

I started by extracting the SELECT query into a separate SSMS window. I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results.

Note: I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to get the query to work. This is what that looked like:

SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as 'Size Meg' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig'
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + ' minutes, '
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 'Yes' ELSE 'No' END AS isOlderThan24Hours
into dbo.TableForEmailingLogBackups
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = 'L'
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;

Then, I used the following code to truncate dbo.TableForEmailingLogBackups, insert the results of the stored procedure call EXEC master.dbo.sp_ineachdb and send the email. Note that you'll need to change the email part to send it to the appropriate email address

truncate table dbo.TableForEmailingLogBackups

insert into dbo.TableForEmailingLogBackups
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command = 
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg'' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = ''L''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SqlServerEmailProfile',
    @recipients = 'recipients@.test.com;',
    @subject = 'Transaction log backup report',
    @query = N'set nocount on;select * from dbo.TableForEmailingLogBackups',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ','

A minute later, I received the email with the attachment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.