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
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 153
Comments posted to this topic are about the item SQL Server Database Backup Status
sankaran.s
sankaran.s
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
Pratap Prabhu
Pratap Prabhu
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 153
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
Robert Davis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3500 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
pauln
pauln
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 89
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
Robert Davis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

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

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

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

Group: General Forum Members
Points: 195 Visits: 153
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
Robert Davis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3500 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
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