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

Need a backup script to get backup history status for the latest backup from all linked sql servers Expand / Collapse
Author
Message
Posted Saturday, January 19, 2013 2:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 1,277, Visits: 2,205
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.
Post #1409238
Posted Sunday, January 20, 2013 2:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
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
Post #1409305
Posted Sunday, January 20, 2013 2:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 21,209, Visits: 14,901
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1409311
Posted Sunday, January 20, 2013 3:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 23,009, Visits: 31,503
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.



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)
Post #1409314
Posted Tuesday, January 29, 2013 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:51 AM
Points: 138, Visits: 661
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



  Post Attachments 
daily backup report script.txt (20 views, 10.92 KB)
backup report.png (15 views, 37.88 KB)
Post #1412850
Posted Tuesday, February 12, 2013 3:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
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
Post #1419216
Posted Tuesday, February 12, 2013 11:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:51 AM
Points: 138, Visits: 661
Thanks. Ignacio :)

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

Regards,
Faisal
Post #1419303
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse