<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Pratap Prabhu
»
SQL Server Database Backup Status
20 posts, Page 1 of 2
1
2
»»
SQL Server Database Backup Status
Rate Topic
Display Mode
Topic Options
Author
Message
Pratap Prabhu
Pratap Prabhu
Posted Thursday, April 23, 2009 2:55 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:11 AM
Points: 139,
Visits: 143
Comments posted to this topic are about the item
SQL Server Database Backup Status
Post #703627
sankaran.s
sankaran.s
Posted Tuesday, May 19, 2009 8:48 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, June 15, 2010 6:54 AM
Points: 6,
Visits: 13
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
Post #720273
Pratap Prabhu
Pratap Prabhu
Posted Friday, May 22, 2009 11:33 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:11 AM
Points: 139,
Visits: 143
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
Post #722258
Robert Davis
Robert Davis
Posted Friday, May 22, 2009 12:53 PM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 1,558,
Visits: 1,396
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
Twitter:
@SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #722337
pauln
pauln
Posted Friday, May 22, 2009 2:05 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:44 AM
Points: 3,
Visits: 64
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.
Post #722382
Robert Davis
Robert Davis
Posted Friday, May 22, 2009 3:09 PM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 1,558,
Visits: 1,396
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
Twitter:
@SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #722450
Pratap Prabhu
Pratap Prabhu
Posted Tuesday, May 26, 2009 8:32 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:11 AM
Points: 139,
Visits: 143
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.
Post #723293
Pratap Prabhu
Pratap Prabhu
Posted Tuesday, May 26, 2009 9:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:11 AM
Points: 139,
Visits: 143
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.
Post #723321
Pratap Prabhu
Pratap Prabhu
Posted Tuesday, May 26, 2009 9:42 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:11 AM
Points: 139,
Visits: 143
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.
Post #723350
Robert Davis
Robert Davis
Posted Wednesday, May 27, 2009 4:22 PM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 1,558,
Visits: 1,396
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
Twitter:
@SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #724658
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
