SQL Clone
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
ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1412
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40894 Visits: 14413
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
sestell1
sestell1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3572 Visits: 3511
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!
ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1412
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97153 Visits: 38988
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.

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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1412
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97153 Visits: 38988
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.

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)
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6650 Visits: 1439
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

sestell1
sestell1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3572 Visits: 3511
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.
ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1412
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
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