SQL Server Database Backup Status

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    Comments posted to this topic are about the item SQL Server Database Backup Status

  • sankaran.s

    SSC Rookie

    Points: 42

    Hi

    i want to msdb.dbo.sp_send_dbmail procedure description. that procedure is default one ?. where i set smtp ip ?

    thanks and regards,

    s.sankar

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    Hi,

    1. Open SQL Server Management Studio

    2. In the Tree menu (Left side of the screen) expand 'Management'

    3. Right click on 'Database Mail'

    4. Select 'Configure Database Mail'

    5. if this is the first time you are setting up choose the first option

    ie 'Setup Database mail performing the following Tasks...'

    if Database mail is already setup choose the second option namely the

    'Manage Database mail accounts and profiles'

    Follow the prompts and you should be good. When prompted for SMTP server

    enter your server IP. I myself use an exchange server.

    Hope this helps

    Thanks

    Pratap

  • Robert Davis

    One Orange Chip

    Points: 28027

    That's great if you only have one or two SQL Servers. Would you do the same if you have 30 servers or 300 servers or 3000 servers?

    I think the script would be more useful if you could pass in a threshold value and have it only send the email if the backup exceeded the specified threshold.

    Here is how I do it:

    I have a procedure on every server that collects its own backup information every day. Then I have a centralized ops server that captures the data from each server and combines them in one place. A job then sends me an alert telling me which databases on which servers have exceeded the backup threshold highlighted in red. For the servers with no databases that exceed the threshold, it shows the server name with its oldest database backup highlighted in green.

    So I get one unified report for all servers.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • pauln

    SSC Enthusiast

    Points: 156

    This would be a very useful procedure if I could get it to work. I'm getting a bunch of errors when I try to execute the create

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 28

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 39

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 50

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 78

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 90

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 99

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 101

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 123

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 136

    Incorrect syntax near '?'.

    I don't see anything near the lines mentioned that would be causing the syntax problems.

  • Robert Davis

    One Orange Chip

    Points: 28027

    It looks like there are some control characters in the code. A quick analysis showed that the character is char(63). So I did a Replace on char(63) and got good SQL that can be compiled.

    Just paste the code into a query window, do a search and replace on single quotes replacing them with 2 single quotes. Then wrap that in a "Select Replace('', char(63), space(1))" query and execute it. I changed the output to text and increased teh output size to the maximum size.

    The output will be good, usable code.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    Sorry about the code not compiling. I just copy and pasted the code from QueryAnalyzer. If you are still having problems, please shot me an email and I will reply with the code attached as an ASCII text file.

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    I just resubmitted the code after removing all Tabs (I use these while coding for legibility). For some reason these where showing up as control characters in the published code. which was not allowing the code to compile. Hope this helps.

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    Robert Davis (5/22/2009)


    That's great if you only have one or two SQL Servers. Would you do the same if you have 30 servers or 300 servers or 3000 servers?

    I think the script would be more useful if you could pass in a threshold value and have it only send the email if the backup exceeded the specified threshold.

    Here is how I do it:

    I have a procedure on every server that collects its own backup information every day. Then I have a centralized ops server that captures the data from each server and combines them in one place. A job then sends me an alert telling me which databases on which servers have exceeded the backup threshold highlighted in red. For the servers with no databases that exceed the threshold, it shows the server name with its oldest database backup highlighted in green.

    So I get one unified report for all servers.

    Thanks Robert, I see your point. Yes its true that I have only one Production server active at one time (Active/Passive Cluster). However in a setup with more than one server, hosting different databases (Assuming that they are linked servers) a simple change to the select statement would do the job. If you look closely you will notice that I am inserting into a table variable. Therefore multiple inserts could also be done prior to composing the email. A where clause on these selects would ofcourse be able to filter as per requirement.

    However I must say I pity the DBA who has to monitor even 30 servers with different databases on each server, let alone 3000. My hats off to you for being able to do so.

  • Robert Davis

    One Orange Chip

    Points: 28027

    Well, if you get to that point, you should be using System Center Operations Manager or some other tool to manage the servers.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • tim.shirey

    SSC Enthusiast

    Points: 145

    What about adding a column thats adds "Number of Days Since Last Backup" in addition to the "Started" and "Finished" columns?

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    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'.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''+

    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'

    DBName Backup Type Backup File Backup Size(bytes) Age(Days) Started Finished Time(mins)

    ' ;

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    :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'.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''+

    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'

    DBName Backup Type Backup File Backup Size(bytes) Age(Days) Started Finished Time(mins)

    ' ;

  • tim.shirey

    SSC Enthusiast

    Points: 145

    Does this also work for SQL 2008? If not can we get a version that does?

  • Pratap Prabhu

    Say Hey Kid

    Points: 711

    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.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply