SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Database Backup Status


SQL Server Database Backup Status

Author
Message
tim.shirey
tim.shirey
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
What about adding a column thats adds "Number of Days Since Last Backup" in addition to the "Started" and "Finished" columns?
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 153
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)
' ;
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 153
Ermm
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''+
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)
' ;


tim.shirey
tim.shirey
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
Does this also work for SQL 2008? If not can we get a version that does?
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 153
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.
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 153
Thanks a lot.
Jigar Lakhani
Jigar Lakhani
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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".
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
haider.biswas
haider.biswas
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 79
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search