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, April 23, 2009 2:55 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
Comments posted to this topic are about the item SQL Server Database Backup Status
Post #703627
Posted Tuesday, May 19, 2009 8:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, May 22, 2009 11:33 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
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
Posted Friday, May 22, 2009 12:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
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
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 #722337
Posted Friday, May 22, 2009 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:53 AM
Points: 3, Visits: 78
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
Posted Friday, May 22, 2009 3:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
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
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 #722450
Posted Tuesday, May 26, 2009 8:32 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 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
Posted Tuesday, May 26, 2009 9:07 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
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
Posted Tuesday, May 26, 2009 9:42 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
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
Posted Wednesday, May 27, 2009 4:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
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
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 #724658
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse