Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Server Database Backup Status Expand / Collapse
Author
Message
Posted Thursday, June 4, 2009 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:50 AM
Points: 5, Visits: 130
What about adding a column thats adds "Number of Days Since Last Backup" in addition to the "Started" and "Finished" columns?
Post #729145
Posted Friday, June 5, 2009 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148
Normally in production the number of days since last backup should all be 0 as the backups are critical. However should you need the number of days just change the SET @tblHTML in the stored proc to include a datediff column. I have done it and pasted the code below this message. Notice that I have included a case statement and also cast it to a varchar. This is just to keep the listing clean. As if there is no backup the datediff will return 39967.
-------------------------------------
SET @tableHTML =
N''+
N''+
N'' +
N''+
N'
Database Backup Status Report
AS OF '+convert(varchar(50),getdate(),101) + ' '+convert(varchar(50),getdate(),108)+N'
'+
N'' +
N''+
N'' +
N''+
N''+
N'' +
N'' +
N'' +
N'' +
N'' +
CAST ( ( SELECT "td/@align"='left' ,td = bks.DBName, '',
"td/@align"='left' ,td = bks.BackupType, '',
"td/@align"='left' ,td = bks.BackupFile, '',
"td/@align"='right',td = bks.BackupSize, '',
"td/@align"='right',td = case when bks.BackupType<>'---NONE---' then convert(varchar(4),dateDiff(d,BackupStartDate_dt,getdate())) else '' end, '',
"td/@align"='left' ,td = bks.BackupStartDate, '',
"td/@align"='left' ,td = bks.BackupEndDate, '',
"td/@align"='right',td = bks.BackupDuration
FROM @Tbl as BKS
ORDER BY BKS.DBname,BKS.BackupStartDate_dt
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
DBNameBackup TypeBackup FileBackup Size(bytes)Age(Days)StartedFinishedTime(mins)
' ;
Post #729804
Posted Friday, June 5, 2009 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148

Sorry the pasting of code did not seem to work too well, I am trying to attach the code within the IFCode Shortcuts, hope it works
SET @tableHTML =
N'<style type="text/css">'+
N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '+
N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '+
N'body {font-family: Arial, verdana;} '+
N'table{font-size:8px; border-collapse:collapse;} '+
N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '+
N'th{background-color:#99CCFF;}'+
N'</style>'+
N'<table border="0">'+
N'<tr><td class="h1">Database Backup Status Report</td></tr>' +
N'<tr><td class="h2">AS OF '+convert(varchar(50),getdate(),101) + ' '+convert(varchar(50),getdate(),108)+N'</td></tr>'+
N'</table>'+
N'<table border="1">' +
N'<tr> <th>DBName</th>'+
N'<th>Backup Type</th>' +
N'<th>Backup File</th>'+
N'<th>Backup Size(bytes)</th>'+
N'<th>Age(Days)</th>' +
N'<th>Started</th>' +
N'<th>Finished</th>' +
N'<th>Time(mins)</th>' +
N'</tr>' +
CAST ( ( SELECT "td/@align"='left' ,td = bks.DBName, '',
"td/@align"='left' ,td = bks.BackupType, '',
"td/@align"='left' ,td = bks.BackupFile, '',
"td/@align"='right',td = bks.BackupSize, '',
"td/@align"='right',td = case when bks.BackupType<>'---NONE---' then convert(varchar(4),dateDiff(d,BackupStartDate_dt,getdate())) else '' end, '',
"td/@align"='left' ,td = bks.BackupStartDate, '',
"td/@align"='left' ,td = bks.BackupEndDate, '',
"td/@align"='right',td = bks.BackupDuration
FROM @Tbl as BKS
ORDER BY BKS.DBname,BKS.BackupStartDate_dt
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

Post #729810
Posted Tuesday, January 26, 2010 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:50 AM
Points: 5, Visits: 130
Does this also work for SQL 2008? If not can we get a version that does?
Post #853926
Posted Tuesday, January 26, 2010 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148
Hi I do not currently have access to a SQL 2008 server. I would love it if someone who has SQL 2008 checks the code to see if it works or breaks and or tweaks the code. If not I shall surely do so myself the moment I have an SQL 2008 server to work with.
Post #853972
Posted Tuesday, January 26, 2010 2:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:42 PM
Points: 1,618, Visits: 1,549
Worked perfectly on sQL 2008 for me.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #853982
Posted Tuesday, January 26, 2010 2:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 3, 2013 9:38 PM
Points: 139, Visits: 148
Thanks a lot.
Post #853989
Posted Tuesday, March 9, 2010 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:00 AM
Points: 4, Visits: 164
doesnt work for me guys. Ran this in 2008 and get the following error

Msg 1087, Level 15, State 2, Line 32
Must declare the table variable "@Tbl".
Post #879797
Posted Tuesday, March 9, 2010 4:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:42 PM
Points: 1,618, Visits: 1,549
You need to run the whole thing and not just the snippet posted above.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #879838
Posted Wednesday, January 18, 2012 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 12:17 PM
Points: 1, Visits: 71
I am trying to use this scripts on SQL Server 2008 R2 box but not working.

Do I need to change any part of the scripts.
Please advise.
Thank you,
Haider
Post #1238217
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse