Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need a backup script to get backup history status for the latest backup from all linked sql servers


Need a backup script to get backup history status for the latest backup from all linked sql servers

Author
Message
sestell1
sestell1
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2192 Visits: 3415
I imagine everyone has their own approach to this.

Your approach of using linked servers will definitely work.

I use a C# application I wrote that lets me run T-SQL code against a select list of servers and provides a combined result set.
I also have an Excel spreadsheet with a macro that essentially does the same, but puts results in individual worksheets. I don't manage anywhere near that number of servers though.

Someone else I know uses registered servers somehow to run T-SQL against multiple servers.

As opc.three mentioned, you could probably do this through a Powershell script as well.

I don't know, but I'd guess Redgate offers some sort of tool for this sort of thing.
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3114 Visits: 1439
faisalfarouqi (1/19/2013)
Thanks. a lot once again, and really do appreciate what you guys say. I'll try to figure out how best I can utilize the info provided by "sestell1". Pls. pardon my ignorance, but I would really like to know how do others get backup information from all of the instances configured in their for e.g: if there are more than 50+ servers, I hope nobody does that manually going and checking on each of the servers, they might be using some kind of a script right? or either a 3rd party tool to do the work for them.

However, I am surprised to know that no one actually has set this thing up int their environment.

I need to really code things off by myself, rather than placing a request for the same.

Regards,
Faisal
Every company has a different approach to it. If you want to use something that has been already proven and you want a step by step guide, you may want to try http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61621/.




My blog

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21067 Visits: 18258
sestell1 (1/19/2013)
I imagine everyone has their own approach to this.

Your approach of using linked servers will definitely work.

I use a C# application I wrote that lets me run T-SQL code against a select list of servers and provides a combined result set.
I also have an Excel spreadsheet with a macro that essentially does the same, but puts results in individual worksheets. I don't manage anywhere near that number of servers though.

Someone else I know uses registered servers somehow to run T-SQL against multiple servers.

As opc.three mentioned, you could probably do this through a Powershell script as well.

I don't know, but I'd guess Redgate offers some sort of tool for this sort of thing.


You can use CMS (central management server) to run scripts against multiple servers at once.

Besides the powershell option there is also ssis which is very good for this.

If you read my blog, you will also find a script to check the backup history.
http://jasonbrimhall.info/2012/11/30/backup-history/



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37929
faisalfarouqi (1/19/2013)
Thanks. a lot once again, and really do appreciate what you guys say. I'll try to figure out how best I can utilize the info provided by "sestell1". Pls. pardon my ignorance, but I would really like to know how do others get backup information from all of the instances configured in their for e.g: if there are more than 50+ servers, I hope nobody does that manually going and checking on each of the servers, they might be using some kind of a script right? or either a 3rd party tool to do the work for them.

However, I am surprised to know that no one actually has set this thing up int their environment.

I need to really code things off by myself, rather than placing a request for the same.

Regards,
Faisal


What we are trying to say is that we are willing to help you by providing you with guidance and feedback as you develop your process and write what ever code you decide to use (PowerShell, T-SQL, or something else).

None of us have said we haven't done this in our own environments, what we have said is that we aren't willing to just give you code to run in your environment. If we give you the code and something goes wrong, you are the one that needs to support the code, not any of us. We want you to fully understand what it is you are implementing in your environment.

As an aside, no, I haven't implemented anything like this in my environments. It was already setup in the environments (last two contract positions), or the environments were small enough that it wasn't necessary (only had about 5 or so production servers running SQL Server, so using SQL Server Agent was sufficient for our needs.

My current production environment happens to be 7700 miles away, and until I get there, I'm not sure what they do over there. I'm looking forward to learning.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ffarouqi
ffarouqi
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 1240
Thanks. to all you guys.

I've finally come up with a script that does the trick for me, and can be helpful for others as well. Now, here are the problems which I am facing...currently I just want to have the differential backups to be specified only if there is one on the server in the report, and it shouldn't print out diff backups in the report if there isn't any. Secondly, I want the report to be nicely formatted, so it looks good to the eyes, anything in html would do for me.

I've attached the script with this post, and I hope now people can actually provide me with some inputs on how to achieve it in a more neat & tidy manner. You can run this in your environment, and can modify the code to suit my needs mentioned above. Pls. revert this to me, as I am in need to get this formatted, and avoid those differentials & log backups if they aren't really there.

--Pls. note I've also attached how the report looks when it arrives in your mail box.

I hope this helps, and now you guys can make me achieve this in a better way.

Regards,
Faisal
Attachments
daily backup report script.txt (21 views, 10.00 KB)
backup report.png (15 views, 37.00 KB)
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3114 Visits: 1439
faisalfarouqi (1/29/2013)
Thanks. to all you guys.

I've finally come up with a script that does the trick for me, and can be helpful for others as well. Now, here are the problems which I am facing...currently I just want to have the differential backups to be specified only if there is one on the server in the report, and it shouldn't print out diff backups in the report if there isn't any. Secondly, I want the report to be nicely formatted, so it looks good to the eyes, anything in html would do for me.

I've attached the script with this post, and I hope now people can actually provide me with some inputs on how to achieve it in a more neat & tidy manner. You can run this in your environment, and can modify the code to suit my needs mentioned above. Pls. revert this to me, as I am in need to get this formatted, and avoid those differentials & log backups if they aren't really there.

--Pls. note I've also attached how the report looks when it arrives in your mail box.

I hope this helps, and now you guys can make me achieve this in a better way.

Regards,
Faisal

Hi, thank you for sharing the script. I must be honest to you, I would not choose for your approach, but there may be people who would. I will propbably use the SQL Overview solution for this. Check the link you may find it interesting http://www.sqlservercentral.com/articles/Administration/63042/.




My blog

ffarouqi
ffarouqi
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 1240
Thanks. Ignacio Smile

Appreciate your help, but is there any way you can modify my code, so that the daily reports are clean and tidy.

Regards,
Faisal
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