April 23, 2009 at 2:55 pm
Comments posted to this topic are about the item SQL Server Database Backup Status
May 19, 2009 at 8:48 pm
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
May 22, 2009 at 11:33 am
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
May 22, 2009 at 12:53 pm
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.
May 22, 2009 at 2:05 pm
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.
May 22, 2009 at 3:09 pm
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.
May 26, 2009 at 8:32 am
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.
May 26, 2009 at 9:07 am
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.
May 26, 2009 at 9:42 am
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.
May 27, 2009 at 4:22 pm
Well, if you get to that point, you should be using System Center Operations Manager or some other tool to manage the servers.
June 4, 2009 at 10:51 am
What about adding a column thats adds "Number of Days Since Last Backup" in addition to the "Started" and "Finished" columns?
June 5, 2009 at 8:54 am
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'
| Database Backup Status Report |
| AS OF '+convert(varchar(50),getdate(),101) + ' '+convert(varchar(50),getdate(),108)+N' |
'+
N'
| DBName | Backup Type | Backup File | Backup Size(bytes) | Age(Days) | Started | Finished | Time(mins) |
|---|
' ;
June 5, 2009 at 9:04 am
: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'
| Database Backup Status Report |
| AS OF '+convert(varchar(50),getdate(),101) + ' '+convert(varchar(50),getdate(),108)+N' |
'+
N'
| DBName | Backup Type | Backup File | Backup Size(bytes) | Age(Days) | Started | Finished | Time(mins) |
|---|
' ;
January 26, 2010 at 1:22 pm
Does this also work for SQL 2008? If not can we get a version that does?
January 26, 2010 at 2:21 pm
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 19 total)
You must be logged in to reply to this topic. Login to reply