power script to find backup status in sql server

Question

power script to find backup status in sql server

Narine M

SSCrazy

Points: 2672
More actions
August 10, 2016 at 3:05 am

#312414

Hi All,
I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML format using the powershell. Kindly help on this with, where to make the modifications.
Regards
NM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 1

naren.ece2012 (8/10/2016)
Hi All,
I need a power-shell script to obtain the following information Name of the database , LastBackupDate of the database and this information should be sent via an email converting the results to HTML format using the powershell. Kindly help on this with, where to make the modifications.
Regards
NM

My recommendation would be to NOT write PoSh scripts for this. It's an unnecessary complication. The script would have to hit on the MSDB tables to get the information anyway so why not write a simple stored procedure and send the email from there?

Better than that, why create a report at all? Whatever you're using to do your backups should send an email for any backup that failed when it fails.

Remember... if you can't write it, you might not be able to support it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I agree with Jeff - no need for PowerShell. Write a view or stored procedure, and create a job to e-mail the results.

I don't share Jeff's doubt over whether a report is needed, though. If the backup doesn't run at all then you're not going to get a failure alert, so a regular report is a good thing - all the more so if it only shows those databases that haven't been backed up recently.

John

Steve Jones - SSC Editor SSC Guru Points: 734469 More actions · Answer 3

I've done this in T-SQL, as noted. I get the information and then build an HTML table that is emailed to users. It's easy to do and works well.

PoSh will work, and if you want the challenge, you can do it, but T-SQL is much easier.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 4

John Mitchell-245523 (8/10/2016)
I agree with Jeff - no need for PowerShell. Write a view or stored procedure, and create a job to e-mail the results.
I don't share Jeff's doubt over whether a report is needed, though. If the backup doesn't run at all then you're not going to get a failure alert, so a regular report is a good thing - all the more so if it only shows those databases that haven't been backed up recently.
John

I actually do get a regular report but it's not specific to backups. It provides me with the rolling 24 hour status of all jobs on the server (I don't have many servers so I created it to send me 1 email for each server). Of course, the backup job are included in that. My alerts also provide failure information for each database backup that failed while still letting the good ones through.

I just don't need a separate report for the backups because of the "Morning Jobs Report".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Jeff

Does your job highlight just jobs that failed, or does it include those that didn't run, perhaps because they were accidentally disabled, or because SQL Server Agent wasn't running?

I think backups are important enough to have their own report. Not only do I check that each database has been backed up, but also that the backup file exists in the location it was made.

I'm not saying this to claim that my way of doing things is better, but just to point out that different things work for different people and in different circumstances.

John

Edit - corrected typo

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 6

And how do you treat jobs that were cancelled part-way through?

Thomas Rushton
blog: https://thelonedba.wordpress.com

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 7

John Mitchell-245523 (8/15/2016)
Jeff
Does your job highlight just jobs that failed, or does it include those that didn't run, perhaps because they were accidentally disabled, or because SQL Server Agent wasn't running?
I think backups are important enough to have their own report. Not only do I check that each database has been backed up, but also that the backup file exists in the location it was made.
I'm not saying this to claim that my way of doing things is better, but just to point out that different thinks work for different people and in different circumstances.
John

The report shows a whole lot even for a "summary". It shows whether the job is currently enabled or not. It shows whether it's scheduled or not or if it's a "One time" job that's run manually on occasion, all highlighted in different colors if out of the norm of enabled and scheduled. It shows the number of times the job ran, how many were successful, how many failed (color highlighted on failure, of course), and how many were cancelled. It shows the earliest run date/time in the 24 hour period, the latest run date/time 24 hour period be damned and, if that is more than a week old, highlights it in Yellow and Red if it hasn't run in a month. It shows the min and max run durations and it shows the last time the job was changed. If it changed anytime in the last week, that date is also colorized. Of course, the addition of a job is a change.

About the only thing not on the report is the median and mode of the run times and deleted jobs. And, like I said, I don't have a huge number of servers so each server generates it's own. It's an easy thing to push out to each server because I don't want all of the servers to stop reporting if the server that generates an enterprise wide report goes down..

That being said, a report for backups isn't a bad idea. I just don't need it for the way we're setup. If that changes, then you can bet I'll have a report on backups to go along with the alerts. You can also bet that it WON'T be a PoSh script that does it and that it'll get pushed to each server, like the jobs reports I have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Narine M SSCrazy Points: 2672 More actions · Answer 8

Thank you for the replies..

Can any one share the Posh scripts to my mail ID: naren.vgsqldba@gmail.com. Kindly do the needful.

Thanks

NM

Sue_H SSC Guru Points: 90860 More actions · Answer 9

naren.ece2012 (10/13/2016)
Thank you for the replies..
Can any one share the Posh scripts to my mail ID: naren.vgsqldba@gmail.com. Kindly do the needful.
Thanks
NM

You can find many examples pretty easily if you just do a search on: powershell sql backup report

Sue

Narine M SSCrazy Points: 2672 More actions · Answer 10

Sue_H - Thursday, October 13, 2016 11:13 AM
naren.ece2012 (10/13/2016)
Thank you for the replies..Can any one share the Posh scripts to my mail ID: naren.vgsqldba@gmail.com. Kindly do the needful.ThanksNM
You can find many examples pretty easily if you just do a search on: powershell sql backup reportSue

Thank You.