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 Wednesday, January 16, 2013 4:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 201, Visits: 764
Hi Guys,

I am actually in need of a t-sql or a powershell script that will help me get the latest backup of all sql servers from a central server. We already have our environment setup for this i.e one central server and there are quite a handful of linked servers. I would like to have a script which will scan all of these linked servers get the info back, and send me a report of the latest backup details either in an "HTML" format or possibly using some sort of tabular formatting so that it looks good. I know that many of them might have implemented this in their environment, but this is the need of the hour for me. You can get back to me either providing a comment below or sending a private e-mail on this id faisalfarouqi@gmail.com.

I hope to see a quick response from you experts.

I am not a good programmer by any means as I have almost started off my career in "Sql Server", and would really appreciate your help.

Regards,
Faisal
Post #1407742
Posted Friday, January 18, 2013 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
PowerShell would be great for this. Using the SQL Server Provider you can iterate over the Linked Servers of your Central Server and issue a query to each. Then you can export the results to a CSV file, or store them in a database.

This could also be done in T-SQL using the same algorithm and some dynamic SQL.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1408930
Posted Friday, January 18, 2013 2:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,423, Visits: 2,445
If you use T-SQL, you'll probably want to use a cursor to loop through all of the linked servers. You can find all the linked servers in the Sys.Servers system view. You'll probably want to filter by the provider column to limit it to links to other SQL Server machines.

Then for each link, you'll want to get the backup information you are interested in. You can find backup information in the following tables:
msdb.dbo.backupset
msdb.dbo.backupmediafamily
Master.sys.databases

I believe they join together like this:

<linked server name>.msdb.dbo.backupset Backup_Set
Join
<linked server name>.msdb.dbo.backupmediafamily Media
ON
Backup_Set.Media_set_id = Media.media_set_id
AND Backup_Set.is_copy_only = 0
Left Join
<linked server name>.Master.sys.databases DB
ON
Backup_Set.database_name = DB.name

The whole backup history is in there, so you'll have to determine which backups you want and what backup information you are looking for and filter appropriately.

Once you have all the pieces, you use your cursor to loop through the linked servers, use dynamic SQL to run your query for each linked server, collect all the results in a temp table or table variable, and then select those results back out formatted as HTML...
http://gallery.technet.microsoft.com/scriptcenter/Selection-result-as-HTML-a1ccff98
...and send them off in an email with the HTML results variable as the body.

I hope this helps. Good luck!
Post #1409109
Posted Saturday, January 19, 2013 12:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 201, Visits: 764
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).

Pls. Pls. Pls. help me out here....I badly need a script

Regards,
Faisal

Post #1409221
Posted Saturday, January 19, 2013 12:34 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 20,862, Visits: 32,896
faisalfarouqi (1/19/2013)
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).

Pls. Pls. Pls. help me out here....I badly need a script

Regards,
Faisal



First, what would you learn if we gave it to you. Second, it's possible those of us reading this thread don't have something handing.

Here is what I suggest. Start by writing something that achieves your goal locally, and if you have problems, post back here what problem(S) you are encountering, the code you have developed so far, and I am sure some one would be more than willing to help you work through the problem. You will learn more that way then someone just giving you the code. Plus you will have a better understanding of the code as you are the one who will need to support the code if something goes south on you.

I know I wouldn't want to hear "I don't know how it works, I just got it off the internet." We are here to help, and that includes making sure you understand the code you will have to support.



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 #1409225
Posted Saturday, January 19, 2013 12:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 201, Visits: 764
Hi Lynn,

You are absoultely right here, that one gets a better understanding of things when they write the code themselves, and I really respect that. Currently, I am in a different situation out here, and since my knowledge of coding (I can't even rate that now, since I've just started off) is quite low at this point, and the time is ticking by as I have to finish off this task at least in a day or two, otherwise I'll be a swiss cheese.

I know it's sounds a little bad to not attempt to code a few things, and ask directly for a script, but I can't help it now as I am stuck, and really need one badly. I appreciate everybody's help and support out here.

Regards,
Faisal Farouqi
Post #1409227
Posted Saturday, January 19, 2013 1:02 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 20,862, Visits: 32,896
Well, that leaves me out as I have nothing at hand and I am not going to take the time to try and write something, especially since I don't have the environment to even test it.

Good luck.



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 #1409230
Posted Saturday, January 19, 2013 1:11 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, November 5, 2014 3:47 PM
Points: 3,087, Visits: 1,437
Lynn Pettis (1/19/2013)
faisalfarouqi (1/19/2013)
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).

Pls. Pls. Pls. help me out here....I badly need a script

Regards,
Faisal



First, what would you learn if we gave it to you. Second, it's possible those of us reading this thread don't have something handing.

Here is what I suggest. Start by writing something that achieves your goal locally, and if you have problems, post back here what problem(S) you are encountering, the code you have developed so far, and I am sure some one would be more than willing to help you work through the problem. You will learn more that way then someone just giving you the code. Plus you will have a better understanding of the code as you are the one who will need to support the code if something goes south on you.

I know I wouldn't want to hear "I don't know how it works, I just got it off the internet." We are here to help, and that includes making sure you understand the code you will have to support.
I agree with Lynn and I also understand your situation, but you should at least try to create something first and then ask for advice on your code and believe me you will be surprise with how fast you will be able to do by yourself. You already got some tips, try to write something based on that. When you have got it running posted here so that other people can also benefit from it. Remember why you are actually asking is for people to write something for you on their free time, when you have not even try to do it yourself.





My blog
Post #1409231
Posted Saturday, January 19, 2013 1:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,423, Visits: 2,445
Lynn and Ignacio are right. If you are responsible for these servers, it's important that you start developing the skills and knowledge you need to maintain them. Running someone else's code that you don't understand on your servers is very risky. This isn't too difficult a task, but it does involve a wide array of concepts. My post above outlines the overall process for doing this in T-SQL and hopefully gave you enough details to start doing some research and get started. If not, feel free to ask questions. You should really give it a shot yourself and post back with specific issues you encounter.
Post #1409233
Posted Saturday, January 19, 2013 1:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 201, Visits: 764
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
Post #1409235
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse